Android Question [Solved] Retrieving data from php+mysql

microbox · Jan 5, 2016

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?

B4X:

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

B4X:

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

B4X:

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

B4X:

<?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 · Jan 5, 2016

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:

B4X:

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

KMatle · Jan 5, 2016

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

Add

B4X:

or die(mysql_error())

behind

B4X:

 mysql_query("SELECT name, age FROM persons")

B4X:

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

microbox · Jan 5, 2016

Erel said:
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:

B4X:

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

I copied your code...

B4X:

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 · Jan 5, 2016

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

Add

B4X:

or die(mysql_error())

behind

B4X:

mysql_query("SELECT name, age FROM persons")

B4X:

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

Hi KMatle, I did what you suggested..

B4X:

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 · Jan 5, 2016

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

B4X:

 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 · Jan 5, 2016

rscheel said:

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

B4X:

<?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);

?>

B4X:

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

B4X:

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

B4X:

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 · Jan 5, 2016

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

Use the original "GetPersons":

B4X:

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.

B4X:

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 · Jan 6, 2016

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

Use the original "GetPersons":

B4X:

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.

B4X:

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 · Jan 6, 2016

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.

Android Question [Solved] Retrieving data from php+mysql

microbox

Active Member

Erel

B4X founder

KMatle

Expert

microbox

Active Member

microbox

Active Member

microbox

Active Member

microbox

Active Member

KMatle

Expert

Attachments

microbox

Active Member

KMatle

Expert

Similar Threads