Android Question [Solved] Retrieving data from php+mysql

[microbox]

I'm testing a demo program from https://www.b4x.com/android/forum/t...-local-php-mysql-server-xampp-with-b4a.48635/ upon running the given program, getting counts of records and inserting records has no problem..but I get issues when getting the data from php+mysql.
Am I missing something in the process?

Installing file.
** Activity (main) Pause, UserClosed = false **
PackageAdded: package:XamppphpMysql.example
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
** Service (httputils2service) Create **
** Service (httputils2service) Start **
Back from Job:CountP
Response from server: 3
Back from Job:InsertNewP
Response from server: "Inserted"
Back from Job:CountP
Response from server: 4
Back from Job:GetP
Response from server: []
Error occurred on line: 112 (Main)
java.lang.RuntimeException: JSON Array expected.
    at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:62)
    at XamppphpMysql.example.main._jobdone(main.java:539)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:491)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:668)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:334)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:244)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:491)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:132)
    at anywheresoftware.b4a.BA$3.run(BA.java:334)
    at android.os.Handler.handleCallback(Handler.java:587)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:132)
    at android.app.ActivityThread.main(ActivityThread.java:4123)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:491)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:849)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:607)
    at dalvik.system.NativeStart.main(Native Method)
** Activity (main) Pause, UserClosed = true **
** Activity (main) Resume **

Codes to get data from table persons

Sub GetPersonsButton_Click
    Dim GetPersons As HttpJob
    GetPersons.Initialize("GetP", Me)
    GetPersons.download2("http://" & ServerIP & "/persons/persons.php", Array As String ("action", "GetPersons"))
End Sub

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 String
              
                ListOfPersons = parser.NextArray 'returns a list with maps
                Log(ListOfPersons)
                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)
              
            Case "InsertNewP"
                'Do nothing
              
        End Select
      
      
      
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
End Sub

and this the php script

<?php

$host = "127.0.0.1";
$user = "root";
$pw = "mypass";
$db = "persons";

$con = mysql_connect($host,$user,$pw) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
mysql_query("SET NAMES 'utf8'");

$action = $_GET["action"];
switch ($action)

{
    case "CountPersons":
        $q = mysql_query("SELECT * FROM persons");
        $count = mysql_num_rows($q);
        print json_encode($count);
    break;
   
    Case "GetPersons":
        $q = mysql_query("SELECT name, age FROM persons");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $user = array();
            foreach($r AS $name => $value){
                $user[$name] = utf8_encode($value);
            }
            $row[] = $user;
        }
        print json_encode($rows);
    break;
   
    case "InsertNewPerson":
        $name = $_GET["name"];
        $age = $_GET["age"];
        $q = mysql_query("INSERT INTO persons (name, age) VALUES ('$name', $age)");
        print json_encode("Inserted");
    break;
   
}





?>

More power to this forum

 

[Erel]

Based on the logs it returned an empty array. However it shouldn't throw an error in that case.

I've tried to reproduce it with the following code and it works properly:

Dim j As JSONParser
j.Initialize("[]")
Log(j.NextArray)

 

[KMatle]

Maybe you get an SQL-Error which causes the JSON to be empty.

Add

or die(mysql_error())

behind

 mysql_query("SELECT name, age FROM persons")

 mysql_query("SELECT name, age FROM persons") or die(mysql_error());

 

[microbox]

Based on the logs it returned an empty array. However it shouldn't throw an error in that case.

I've tried to reproduce it with the following code and it works properly:

Dim j As JSONParser
j.Initialize("[]")
Log(j.NextArray)

I copied your code...

Select Job.JobName
                       
            Case "GetP"
                Dim j As JSONParser
                j.Initialize("[]")
                Log(j.NextArray)

This is my output..

seems server is not giving out data.

 

[microbox]

Maybe you get an SQL-Error which causes the JSON to be empty.

Add

or die(mysql_error())

behind

 mysql_query("SELECT name, age FROM persons")

 mysql_query("SELECT name, age FROM persons") or die(mysql_error());

Hi KMatle, I did what you suggested..

Case "GetPersons":
        $q = mysql_query("SELECT name, age FROM persons") or die(mysql_error());
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $user = array();
            foreach($r AS $name => $value){
                $user[$name] = utf8_encode($value);
            }
            $row[] = $user;
        }
        print json_encode($rows);
    break;

But I still have the same results...

I appreciate your kind help..

 

[microbox]

I change my device from Honeycomb to kikat android version. In Kikat OS device does not gives java error but it still return empty arraylist.("No persons found...")
I use the original code by KMatle

 Case "GetP"
                Dim ListOfPersons As List
                Dim PersonName As String
                Dim PersonAge As String
             
                ListOfPersons = parser.NextArray 'returns a list with maps
                Log(ListOfPersons)
                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

 

[microbox]

Try doing so, with this you can send an insert and a select both, separated ; .

<?php

$host = "URL_SERVER";
$user = "USER";
$pw = "PASS";
$db = "BD_NAME";

    $con = mysql_pconnect($host,$user,$pw) or die(mysql_error());
    mysql_select_db($db) or die(mysql_error());
    mysql_query("SET CHARACTER SET utf8");
    mysql_query("SET NAMES 'utf8'");

    $SQL = $_GET["SQL"];
    $SQL_SEPARADO = explode(";", $SQL);

    for($i = 0 ;$i<count($SQL_SEPARADO);$i++){
    if ($SQL_SEPARADO[$i]!=''){
        $pre = mysql_query($SQL_SEPARADO[$i]);
        $rows = array();
    while($r = mysql_fetch_assoc($pre)){
        $rows[] = $r;
    }
    }
    }
       print json_encode($rows);

?>

Sub GetData(SQL_Cadena As String, sJob As String)
    Dim GetSQL As HttpJob
    GetSQL.Initialize(sJob, Me)
    GetSQL.download2("http://" & ServerIP & "/NamePHP.php", Array As String ("SQL", SQL_Cadena))  
End Sub

GetData("INSERT INTO registro (reg_1, reg_2, reg_3, ...) VALUES (reg_1, reg_2, reg_3, ...); SELECT * FROM registro;", "GetRegistro")

Sub JobDone(Job As HttpJob)
    If Job.Success Then
             Dim res As String
            res = Job.GetString
            Log("Back from Job:" & Job.JobName )
            Log("Job Text: " & res)
            Dim parser As JSONParser
            parser.Initialize(res)
        Select Job.JobName

                Case "GetRegistro"
                      You source..........

                End Select
               Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
End Sub

Hello rscheel, I will try it today...thanks for the time.

 

[KMatle]

Please check if there are really any rows in the table?

Use the original "GetPersons":

Case "GetPersons":
        $q = mysql_query("SELECT name, age FROM persons");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);

I've added a SQL-dump of the table from the example. You can import it.

CREATE TABLE IF NOT EXISTS `persons` (
  `name` varchar(30) CHARACTER SET utf8 NOT NULL,
  `age` varchar(30) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

[microbox]

Please check if there are really any rows in the table?

Use the original "GetPersons":

Case "GetPersons":
        $q = mysql_query("SELECT name, age FROM persons");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);

I've added a SQL-dump of the table from the example. You can import it.

CREATE TABLE IF NOT EXISTS `persons` (
  `name` varchar(30) CHARACTER SET utf8 NOT NULL,
  `age` varchar(30) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Hello KMatle, now works fine it was related to the character set was it?

 

[KMatle]

I saw some charset conversation in one of your first posts. If a conversation fails the string may be empty so the JSON is empty, too.

Hint: Always use at least UTF8 for your databases/tables. It uses 4 bytes for each char (so VARCHAR(100) is good for 25 chars) but it is standard in Android. So no conversation is needed for special chars like here in Germany: äöüß or for the Frenchies éèâ, etc...

This is a simple example without security standards.

Add a "or die ..." behind every SQL statement (to get an error message when something goes wrong) and escape/check all data you get in the php script (see MySqlInjection and some other safety information - use Google - there are tons of safety tips). Include the db-parms from another file (when Apache fails the script might be shown as clear text and your login data can be seen).

Use "prepared statements" to add much more safety and ... have fun!

PS: Change the thread's title to "[solved] ....". Next to the title is a small button to do that.