Android Question [Solved] Retrieving data from php+mysql

microbox

microbox

Active Member
Licensed User
Longtime User
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?

Code: Copy 
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
Code: Copy 
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
Code: Copy 
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
Code: Copy 
<?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 :)
 
Last edited:
Sort by date Sort by votes
Maybe you get an SQL-Error which causes the JSON to be empty.

Add

Code: Copy 
or die(mysql_error())

behind

Code: Copy 
 mysql_query("SELECT name, age FROM persons")


Code: Copy 
 mysql_query("SELECT name, age FROM persons") or die(mysql_error());
 
Upvote 0
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:
Code: Copy 
Dim j As JSONParser
j.Initialize("[]")
Log(j.NextArray)
Click to expand...

I copied your code...
Code: Copy 
Select Job.JobName
                       
            Case "GetP"
                Dim j As JSONParser
                j.Initialize("[]")
                Log(j.NextArray)

This is my output..
phpMysqlErr1.png

seems server is not giving out data.
 
Upvote 0
KMatle said:
Maybe you get an SQL-Error which causes the JSON to be empty.

Add

Code: Copy 
or die(mysql_error())

behind

Code: Copy 
 mysql_query("SELECT name, age FROM persons")


Code: Copy 
 mysql_query("SELECT name, age FROM persons") or die(mysql_error());
Click to expand...

Hi KMatle, I did what you suggested..
Code: Copy 
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..
 
Upvote 0
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
Code: Copy 
 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
 
Upvote 0
rscheel said:
Try doing so, with this you can send an insert and a select both, separated ; .

Code: Copy 
<?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);

?>

Code: Copy 
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

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

Code: Copy 
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
Click to expand...
Hello rscheel, I will try it today...thanks for the time.
 
Upvote 0
Please check if there are really any rows in the table?

Use the original "GetPersons":

Code: Copy 
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.

Code: Copy 
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;
 

Attachments

Last edited:
Upvote 0
KMatle said:
Please check if there are really any rows in the table?

Use the original "GetPersons":

Code: Copy 
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.

Code: Copy 
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;
Click to expand...

Hello KMatle, now works fine :) it was related to the character set was it?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

Cliff McKibbin
  • Article Article
Android Example Syncing local databases using PHP to access a database on a hosted server
Replies
1
Views
8K
thetrueman
T
Mashiane
Mashy Teaches WebApp/Website Development with BANanoVuetifyAD3 - The New Series
7 8 9
Replies
164
Views
125K
bridge1
B
eng.khalidvb
  • Question Question
Android Question Retrieve image from MYSQL database
Replies
2
Views
3K
Erel
Erel
KMatle
  • Article Article
Android Tutorial Complete tutorial: B4A & Your own (local) php & MySql Server (Xampp) with b4a
2
Replies
36
Views
67K
OliverA
OliverA
Erel
  • Locked
  • Article Article
Android Tutorial Connect Android to MySQL Database Tutorial
30 31 32
Replies
633
Views
1M
Oke
O
Back
Top