Android Tutorial For beginners: How to communicate with a server using httputils2 (Part 3: php, MySql & JSON)

KMatle

Expert
Licensed User
Part 2 see here: http://www.basic4ppc.com/android/fo...a-server-using-httputils2-part-2-mysql.42456/

Well. Today we want to:

- make an app which calls different functions in our php script
- the app will be able to insert new persons, retrieve & count them into/from a database
- we will learn a bit about formatting the data (for lazy people like me - but it's more efficient)


Setting up the app

To keep it easy I have attached the project and the php file this time. I have tested all functions.

Keeping in mind what functions we want the app to have

- insert (a new person)
- retrieve all persons to show them
- count them (just to show how we work with single parms and a list of)

In tutorial 2 I have created a table with two culumns: name and age (all lowercase). The app will use it, too.

I made 3 buttons (insert, retrieve and count), 2 Edittext views (to input name and age) and a listview to display the data.

Insert:

B4X:
Dim InsertNewPerson As HttpJob
    InsertNewPerson.Initialize("InsertNewP", Me)
    InsertNewPerson.download2("http://www.yourdomain.com/myscript.php", Array As String ("action", "InsertNewPerson", "name", NameET.Text, "age", AgeET.Text))
We call our php script and send "name" and "age". As you see, we send the field name AND it's value. So in the script we can use the get-method to retrieve the fields we need (at the moment we need it).

To let the script know what we want to do (remember: we have 3 functions) I've added an "action field" as the first parameter. Here it is "InsertNewPerson". Guess what it does? Even the jobname ist called "InsertNewPerson".



Send and receive data to/from the php script

We will send and receive the data as an JSON formatted string. JSON is not a member of a boygroup. It helps us to handle the data very simple.

In my example there is a function to retrieve all persons from the table. The result we be a list of rows (n rows with name and age of a person):

B4X:
name      age
Klaus    30
Michael    40
test    23
Peter    59
Thomas    54

So here we have the 5 rows with 2 culumns which format is very similar to the way we send data to the script (field name, field value, field name, field value, .....)

JSON stands for "JavaScript Object Notation" and is a "ready to use function" to format data in a standard way. You will find the documentation here: http://www.json.org/

At first sight you will recognize the form we send and receive data. JSON help us to format the data.

And this is the formatted string the php sends to the app (JSON format):

B4X:
[{"name":"Klaus","age":"30"},{"name":"Michael","age":"40"},{"name":"test","age":"23"},{"name":"Peter","age":"59"},{"name":"Thomas","age":"54"}]
The meaning is simple:

Each content inside the brackets = {} is an object which is quite abstract. As we know, we handle persons with our app. So let's call it person.

So we simple get a list with persons. That's it.

In our app we use
B4X:
Dim parser As JSONParser
parser.Initialize(res)
to initialise the JSON parser.

As I said, we know to get a list with persons. So why not have a list to handle it?

B4X:
Dim ListOfPersons As List
Now we let the parser work:

B4X:
ListOfPersons = parser.NextArray
The JSON parser takes a look at the data and extracts the list for us. And remember: The list is a map with field name, field value, field name, field value, ..... and every map is a person (name and age).

So we need a map:

B4X:
Dim Person As Map
Inside the map we can address each value by (= culum of our table):

B4X:
PersonName = Person.Get("name")
PersonAge = Person.Get("age")
Wow. Now we have the "get" statement we used in our php script to get the data. Sending and getting maps with data.

The equivalent in the php script:

Getting the data from the app

B4X:
$name = $_GET["name"];
$age = $_GET["age"];
Sending back data to the app

B4X:
$q = mysql_query("SELECT name, age FROM persons");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);
"mysql_fetch_assoc" will get the values AND the culumn names. The rows are stored in an array and at the end we convert it into a JSON string.

B4X:
[{"name":"Klaus","age":"30"},{"name":"Michael","age":"40"},{"name":"test","age":"23"},{"name":"Peter","age":"59"},{"name":"Thomas","age":"54"}]
But what if we only want to send back one single value?

In another function I just count the persons stored. The php script will then just send a number. JSON will do that for us, too.

B4X:
$q = mysql_query("SELECT * FROM Persons");
$count = mysql_num_rows($q);
print json_encode($count);
In our app we need to tell the parser to expect a single value:

B4X:
parser.NextValue
Here we have an array with just one value and JSON gets it for us.

Now take a look at the b4a project and the php script. The include statement includes another php script which contains the database names and the login parameters. This is used to protect your login parameters and passwords from being known.
 

Attachments

JdV

Active Member
Licensed User
Hi

This tutorial is brilliant but can make the series slightly more navigable?

I mean put links to parts 2 and 3 in part 1. Put links to parts 1 and 3 in part 2 etc.

Regards

Joe
 

Imam

Member
Licensed User
When httputils2 do this mysql job, is this possible to make progress bar? so people didnot rapid click the button because may be somebody there think the app is hang or didnot respon for the first/second click. Thanks for very good helped tutorial.
 

KMatle

Expert
Licensed User
I would disable the button when you start the job and activate it again when getting back to job.done. You could set the button.text to "Retreiving" or "Sending", too. Usually the calls are very quick, so I don't use ToastMessageShow because most of the times the job is finished before the message is gone which is annoying to many people like me.
 

adjie

Member
Licensed User
Hi Klaus, I'm trying to run the example. The CountPersons running well. But when i try to getPerson list, came this error :

B4X:
Error occurred on line: 110 (main)
java.lang.RuntimeException: JSON Array expected.
    at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:62)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:636)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:302)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:238)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:121)
    at anywheresoftware.b4a.BA$3.run(BA.java:320)
    at android.os.Handler.handleCallback(Handler.java:615)
    at android.os.Handler.dispatchMessage(Handler.java:92)
    at android.os.Looper.loop(Looper.java:137)
    at android.app.ActivityThread.main(ActivityThread.java:4895)
    at java.lang.reflect.Method.invokeNative(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:511)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:994)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:761)
    at dalvik.system.NativeStart.main(Native Method)
** Activity (main) Resume **
and after that the program immediately close and the debug is stop. Any help on this ? many thanks before.
 

adjie

Member
Licensed User
Ok I've solved it ! :D
I'm focusing on my error "java.lang.RuntimeException: JSON Array expected." which I think the error is json from web server is not really a json, or having something that I can't detect.
something wierd with the data from server (php). I'm not familiar with it.
from php server I got data like this :
[{"name":"test1","age":"10"},{"name":"test2","age":"20"}]
and I found the problem when I put the output from web to file and read it like this :
Dim res As String = File.ReadString(File.DirAssets, "test.json")
with the same json, the length from web is 59, and from file is 57. thats the problem. So, become easy if come to this step ;)

Now my question is, is anybody has same problem like mine ? is it web problem or what ?
 

adjie

Member
Licensed User
here is my php script :

PHP:
<?php

include ("db.php");
$action = $_GET["action"];
$con = mysql_connect($host,$user,$pw) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");

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))
        {
            $rows[] = $r;
        }
        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;
   
}

?>
here my job.done :
B4X:
Sub JobDone(Job As HttpJob)
    ProgressDialogHide
    If Job.Success Then
        InsertNewPersonButton.Enabled = True
        Dim res As String
        res = Job.GetString
        Log("Back from Job:" & Job.JobName )
        Log("Response from server: " & res)
        Dim parser As JSONParser
       
        'Try to compare with the same output output from file copied from web result
       
        'File.WriteString(File.DirRootExternal,"data.json",res)
        'Dim rp As String = File.ReadString(File.DirRootExternal, "data.json")

'        Dim res2 As String = File.ReadString(File.DirAssets, "data.json")
'        Dim parser2 As  JSONParser
'        parser2.Initialize(res2)
'        Dim root As List = parser2.NextArray
       
'        For Each colroot As Map In root
'         Dim age As String = colroot.Get("age")
'         Dim name As String = colroot.Get("name")
'        Next
       
'        Log(res.Length)
'        Log(res2.Length)
        'Log(rp=res2)
       
        '----this is the work around------
        res = res.SubString(2)
        '---------------------------------
        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"
                Log(PersonsListview.Tag)
'                If PersonsListview.Tag <> "" Then
'                    PersonsListview.RemoveAt(0)
'                End If
                PersonsListview.Clear
                PersonsListview.AddSingleLine ("Persons in table: " & parser.NextValue)
                PersonsListview.Tag = "1"
            Case "InsertNewP"
                'Do nothing
                CountPersonsButton_Click
        End Select
       
       
       
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
End Sub
 

KMatle

Expert
Licensed User
In Job.Done I get the same format as a JSON-String (No error)

B4X:
Response from server: [{"name":"Klaus","age":"30"},{"name":"Michael","age":"40"},{"name":"test","age":"23"},{"name":"Peter","age":"59"},{"name":"Thomas","age":"54"},{"name":"James","age":"47"},{"name":"James","age":"47"},{"name":"James","age":"47"},{"name":"James","age":"47"},{"name":"James","age":"47"},{"name":"James","age":"47"},{"name":"James","age":"47"},{"name":"James","age":"47"},{"name":"James","age":"47"},{"name":"James","age":"47"}]

Then I stored the JSON-String in a file and loaded it back (no error):

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)
       
        File.WriteString(File.DirInternal,"data.json",res)
        res = File.ReadString(File.DirInternal, "data.json")
       
        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)
               
            Case "InsertNewP"
                'Do nothing
               
        End Select
       
       
       
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
End Sub
I've seen you tried to load from File.DirAssets (that will not work).

If you want, you can send me your real (not working) project via pm. I will take a look. Just copy the complete code.
 

adjie

Member
Licensed User
Ok. Thank you KMatle ! the File.DirInternal works perfectly. I'll try to work around that.
 

adjie

Member
Licensed User
Hi @KMatle, I found the problem. The problem is not on the b4a code, but in php code. Cause event I use File.DirInternal, the problem still came.
Then I try to look in to php code. I have suspicion on "SET CHARACTER SET utf8". And I remarked it. Now all things gone fine. Maybe this can be a notice for others. Thanks again.
 

DonManfred

Expert
Licensed User
And I remarked it. Now all things gone fine.
And ALL Characters are shown ok? äöü߀ too?

Normally i would say you need to let the SET characterset utf8 active and encode all strings with utf8 befor adding to the jsonresult.

This this as example
PHP:
    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);
Note that you need to utf8decode the vars coming from b4a...

B4X:
    case "InsertNewPerson":
        #$name = $_GET["name"];
        if (isset($_REQUEST['name'])){name=utf8_decode($_REQUEST['name']);} else {name="";}
        #$age = $_GET["age"];
        if (isset($_REQUEST['age'])){$age=intval($_REQUEST['age']);} else {$age=0;}
        $q = mysql_query("INSERT INTO Persons (name, age) VALUES ('$name', $age)");
        print json_encode("Inserted");
 
Last edited:

adjie

Member
Licensed User
And ALL Characters are shown ok? äöü߀ too?
In my case I'm not using äöü߀, so I'm not test it yet. But in my case encoding with utf8 will add 2 characters infront of the text. I can't figure it out what it is. Its not a space. When I copy to notepad I got nothing. But when I copy to sublimetext, i can detect with cursor that I got additional 2 characters infront of it.

If yours is find, then I thing it's a problem with my php. I'm using xampp version 1.8.1.
 

adjie

Member
Licensed User
check my post again, please. i saved some changes an examples into it a minute before
Wow..the utf8_encode does the magic :D
I try it and also try with the wierd characters and the output is OK.
And then I try again the previous script
PHP:
mysql_query("SET CHARACTER SET utf8");
and It also becoming OK ! Is it because utf8_encode ? :D is it triggering some thing in the php or apache ? ah forget it, I think I should search this on php forum.
Thank you DonManfred !
 
Top