Android Question select row b4a and php file

[MohammadNew]

I have an example app that insert data with php file and b4a

insert , update and delete are ok.

but select a row and show data in edittext I do not know how to do it .. I searched before this thread

could you tell me about that and give me advice .

my php file for insert

case "InsertNewPerson":
                $name = $_POST["name"];
                $age = $_POST["age"];
                $img= $_POST["img"];
        $q = mysql_query("INSERT INTO persons (name, age, img) VALUES ('$name', '$age', '$img')");
        print json_encode("Inserted");
break;

and code b4a for insert this

Dim InsertNewPerson As HttpJob
    InsertNewPerson.Initialize("InsertNewP", Me) 
    InsertNewPerson.postString("http://" & ServerIP & "/persons.php","action=InsertNewPerson&name="&NameET.Text&"&age="&AgeET.Text&"&img="&img64)

until now no problem


my question : how to do select row and display data and image on edittext and imageview

this php file for select

case "SelectPersons":
                $name = $_POST["name"];
                $age = $_POST["age"];
                $img= $_POST["img"];     
        $q = mysql_query("select * from persons where name='$name'");
        print json_encode("Selected");
break;

what I write the code b4a to select row ?


this is code b4a

Sub JobDone(Job As HttpJob)
    ProgressDialogHide
    If Job.Success Then
       Dim res As String
        res = Job.GetString
        Log("Back from Job:" & Job.JobName )
        Log("Response from server: " & res)
              
        Dim parser As JSONParser
        parser.Initialize(res)
      
        Select Job.JobName
                      
            Case "GetP"
               Dim ListOfPersons As List
                Dim PersonName As String
                Dim PersonAge As Int
              
                ListOfPersons = parser.NextArray 'returns a list with maps
              
                PersonsListview.Clear
              
                If ListOfPersons.Size=0 Then
                   PersonsListview.AddSingleLine ("No persons found...")
                Else
                    For i = 0 To ListOfPersons.Size - 1
                        Dim Person As Map
                        Person = ListOfPersons.Get(i)
                                          
                        PersonName = Person.Get("name")
                        PersonAge = Person.Get("age")
                      
                        PersonsListview.AddSingleLine (PersonName & ", " & PersonAge)
                      
                    Next
                End If
      
            Case "CountP"
                PersonsListview.AddSingleLine ("Persons in table: " & parser.NextValue)
              
          
        End Select
      
      
      
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
End Sub

Thanks alot

 

[Erel]

You can use this PHP script: https://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/#content

 

[MohammadNew]

Thanks Erel,
I followed the link and I wrote code for insert like this

ExecuteRemoteQuery("INSERT INTO countries (id, name, population) VALUES ('" & EditText1.Text & "' , '" & EditText2.Text & "' , '" & EditText3.Text & "'" , "")

I got error

Logger connected to:  samsung SM-A700FD
--------- beginning of main
--------- beginning of system
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Service (httputils2service) Create **
** Service (httputils2service) Start **
Response from server: []
ResponseError. Reason: Internal Server Error, Response: INSERT INTO countries (id, name, population) VALUES ('1' , 'Egypt' , '20'\nYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
INSERT INTO countries (id, name, population) VALUES ('1' , 'Egypt' , '20'\nYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

the php file same yours with out change

 

[udg]

Hi,
you miss the closing parenthesis in your INSERT clause.

& EditText3.Text & "');"

udg

 

[MohammadNew]

UDG Thanks.

How to insert image in this case ?

 

[udg]

I'm not sure I understood it correctly, but assuming your DB has a BLOB field named "flag" where you want to insert an image, you could adapt the following code:

Dim s As String = ""
If File.Exists(File.DirRootExternal, fname) Then
  Dim bm As Bitmap
  bm.Initialize(File.DirRootExternal, fname)
  Dim bmpdata() As Byte = ImageToBytes(bm)
  Dim su As StringUtils
  s= su.EncodeBase64(bmpdata)
end if
ExecuteRemoteQuery("INSERT INTO countries (id, name, population,flag) VALUES ('" & EditText1.Text & "' , '" & EditText2.Text & "' , '" & EditText3.Text & "','" & s &"');" , "")


Public Sub ImageToBytes(Image As Bitmap) As Byte()
   Dim out As OutputStream
   out.InitializeToBytesArray(0)
   Image.WriteToStream(out, 100, "PNG")
   out.Close
   Return out.ToBytesArray
End Sub

udg

 

[MohammadNew]

Mr. UDG Thanks alot

what about retrieve the image ?

 

[MohammadNew]

I got it again Thanks Mr. UDG

                    Dim buffer() As Byte
                    Dim b As Bitmap
                    Dim su As StringUtils                   
                    buffer = su.DecodeBase64(m.Get("img"))
                    Dim In As InputStream
                    In.InitializeFromBytesArray(buffer, 0, buffer.Length)
                    b.Initialize2(In)
                    ImageView1.Bitmap = b

 

[udg]

I'm glad you solved your problem. As you read in a different thread, is generally better to store files in the file system and a file reference in the DB; this is expecially true for larger files. One important note of caution is from the MySql manual:
"The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the max_allowed_packet variable, but you must do so for both the server and your client program."

Just for a quick lookup:

TINYBLOB
A binary large object column with a maximum length of 255 (2^8 - 1) characters.
BLOB
A binary large object column with a maximum length of 65535 (2^16 - 1) characters.
MEDIUMBLOB
A binary large object column with a maximum length of 16777215 (2^24 - 1) characters.
LONGBLOB
A binary large object column with a maximum length of 4294967295 (2^32 - 1) characters.


udg

​

 

[MohammadNew]

Thanks UDG again.