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

Part 2 see here: http://www.b4x.com/android/forum/th...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

  • Example_B4A.zip
    8.6 KB · Views: 2,881
  • php_example.zip
    552 bytes · Views: 2,478

DonManfred

Expert
Licensed User
Longtime User

Anser

Well-Known Member
Licensed User
Longtime User
Need a help.

To test B4A and MySQL via PHP, today, I purchased a one month web hosting plan (Linux/MySQL/Php)

I want to test B4A and MySQL via PHP.
I have my MySQL Database ready on a remote server on the internet. I can connect to the the MySQL database via HeidiSQL and is working fine.
I have a domain name registered. I have not loaded any webpage on this site. When I checked the webroot, I found the following files are available.

  1. 404.shtml
  2. home.html
  3. layout-style.css
  4. A folder named cgi-bin

Now how do I start using the PHP ?
I have found the php code attached in this thread.

If I copy the php_example.php file available in this thread to my website home (webroot) will it work ?
Do I need to have a index.html on my webroot ?
I don't have any plans to have a website (as of now). My only purpose of this web server is to use the MySQL database via Php

Any help regarding this will be appreciated. The answers to this post may be helpful for other newbies like me.

Regards
Anser
 

Anser

Well-Known Member
Licensed User
Longtime User
I understand that to use php you don't need to have html file and that you can directly call the php file saved in your www root or any other folder

For eg. www.mydomain.com/php/myscripts.php?TestScript

Unfortunately, I am getting the following error

Access denied for user 'testusers'@'xxx.xxx.xxx.xxx' (using password: YES)

Anybody any idea why is it giving such an error ?
I have given full permission to the user testusers

Regards
Anser
 

Anser

Well-Known Member
Licensed User
Longtime User
Hi all,

Got it working.
The error message which I posted in my previous message is related with the Web Hosting and permission. It has nothing to do with the HttpUtils2 and PHP.

Regards
Anser
 

achtrade

Active Member
Licensed User
Longtime User
Great tutorial. could you please post the content of the db.php file ??

thanks.
 

Anser

Well-Known Member
Licensed User
Longtime User
Great tutorial. could you please post the content of the db.php file ??

thanks.
I assume that the following could be the code for db.php
Anyway it is working for me. But I am not aware whether there are other better ways to code the db.php

I am a newbie here.
B4X:
<?php

$host = "YourDomainName_Or_IPaddress";
$db = "YourDatabaseName";
$user = "YourDatabaseUserName";
$pw = "YourDatabaseUserPassword";

?>

Regards
Anser
 

achtrade

Active Member
Licensed User
Longtime User
I assumed that is how the file looks like, but I wanted to be sure how to do it.

thanks.
 

Anser

Well-Known Member
Licensed User
Longtime User
Hi,

How would I retrieve a Varchar and Image data together in JSON format via php

For eg. In my Table, I have ItemName (Varchar) and ItemPicture (BLOB) and the follwing php code retrieves the Image as null
B4X:
 Case "PriceList":
        $catg = mysql_real_escape_string($_GET["Category"]);
        $q = mysql_query("SELECT Item_code, Item_Picture FROM Items_master WHERE Category = '$catg'");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        mysql_free_result($q);
        print json_encode($rows);       
    break;

In my B4A app the above given php code retrieves JSON records with the image data as null.

I understand that base64_encode() should be used in php code, but I don't know how to apply the base64_encode() to the image data alone and then retrieve the Item_code and Item_Picture together.

Any help will be appreciated.

Regards
Anser
 

Anser

Well-Known Member
Licensed User
Longtime User
don't you mind tell us how ?

Here is the code :).
B4X:
 Case "PriceList":
        $catg = mysql_real_escape_string($_GET["Category"]);
        $q = mysql_query("SELECT Item_code, Item_Picture FROM Items_master WHERE Category = '$catg'");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        /* Converting the Image column to Base64Encoded in JSON */
       for ($i=0; $i < sizeof($rows); $i++)
        {
            $rows[$i]['Item_Picture']=base64_encode($rows[$i]['Item_Picture']);
        }
        mysql_free_result($q);
        print json_encode($rows);      
    break;

Regards
Anser
 

achtrade

Active Member
Licensed User
Longtime User
Here is the code :).
B4X:
 Case "PriceList":
        $catg = mysql_real_escape_string($_GET["Category"]);
        $q = mysql_query("SELECT Item_code, Item_Picture FROM Items_master WHERE Category = '$catg'");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        /* Converting the Image column to Base64Encoded in JSON */
       for ($i=0; $i < sizeof($rows); $i++)
        {
            $rows[$i]['Item_Picture']=base64_encode($rows[$i]['Item_Picture']);
        }
        mysql_free_result($q);
        print json_encode($rows);     
    break;

Regards
Anser
Thanks, and how b4a catch this item_picture value ?
 

Anser

Well-Known Member
Licensed User
Longtime User
Thanks, and how b4a catch this item_picture value ?
B4X:
Dim ImageData As String
Dim MyBitmap As Bitmap
Dim ImageBytes() As Byte
Dim su As StringUtils
Dim MyInputStream As InputStream

ImageData = oRecord.Get("Item_Picture") 
ImageBytes = su.DecodeBase64(ImageData)
     
MyInputStream.InitializeFromBytesArray(ImageBytes,0,ImageBytes.Length)
MyBitmap.Initialize2(MyInputStream)
MyInputStream.Close

Hope this will be helpful and saves time for newbies like me.

Regards
Anser
 

Anser

Well-Known Member
Licensed User
Longtime User
Hi aeric,

Please keep in mind that according to me this is not an elegant solution due to the following reasons.

  1. Php retrieves MySQL BLOB data first via SQL Query.
  2. Php loops thru the data to convert the BLOB data TO Baase64 encoded data.
  3. Inside B4A, the JSON data received as response from the server is processed again to convert it to Imagedata
All the above may have an impact on the performance of our app.

The process number 2 in the above list can be avoided if we use MySQL ver 5.6.1 OR above

From MySQL ver 5.6.1 onwards we can retrieve BLOB data as Base64 Encoded string directly via SQL without the need of Php converting it thru a loop. Unfortunately many Hosting service providers still use MySQL version 5.5 only. Even though MySQL version 5.7 is available, they consider 5.5 as the most stable version.

Hope this is helpful to others. :)

Regards
Anser
 

aidymp

Well-Known Member
Licensed User
Longtime User
Hi, This all works as expected for me, BUT!

The App I want to use the code with is using the 3 modules "DownloadService.bas" "HttpJob.bas" "HttpUtils2Service.bas" As the whole point of my app is to download large files, and i use these a lot. when i use the code with these 3 modules instead of the "HttpUtils2" lib, i get the following error

** Activity (main) Resume **
Installing file.
** Activity (main) Pause, UserClosed = false **
PackageAdded: package:com.FireMatic.kodi
** Activity (main) Create, isFirst = true **
mac = 58:12:43:a7:b7:54
MAC = 58:12:43:A7:B7:54
** Activity (main) Resume **
** Service (httputils2service) Create **
** Service (httputils2service) Start **
httputils2service_hc_responsesuccess (java line: 148)
java.lang.ClassCastException: java.lang.Object cannot be cast to com.FireMatic.kodi.downloadservice$_jobtag
at com.FireMatic.kodi.httputils2service._hc_responsesuccess(httputils2service.java:148)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:187)
at anywheresoftware.b4a.BA$3.run(BA.java:332)
at android.os.Handler.handleCallback(Handler.java:605)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4430)
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:792)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:559)
at dalvik.system.NativeStart.main(Native Method)
java.lang.RuntimeException: java.lang.ClassCastException: java.lang.Object cannot be cast to com.FireMatic.kodi.downloadservice$_jobtag
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:213)
at anywheresoftware.b4a.BA$3.run(BA.java:332)
at android.os.Handler.handleCallback(Handler.java:605)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4430)
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:792)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:559)
at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.ClassCastException: java.lang.Object cannot be cast to com.FireMatic.kodi.downloadservice$_jobtag
at com.FireMatic.kodi.httputils2service._hc_responsesuccess(httputils2service.java:148)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:187)
... 10 more
** Activity (main) Resume **

Obviously I have tried it removing te modules and using the lib, but that is not what I want!

How can I get the download2 function to work correctly with these modules?

Thanks

Aidy
 

alienhunter

Active Member
Licensed User
Longtime User
B4X:
Dim ImageData As String
Dim MyBitmap As Bitmap
Dim ImageBytes() As Byte
Dim su As StringUtils
Dim MyInputStream As InputStream

ImageData = oRecord.Get("Item_Picture")
ImageBytes = su.DecodeBase64(ImageData)
  
MyInputStream.InitializeFromBytesArray(ImageBytes,0,ImageBytes.Length)
MyBitmap.Initialize2(MyInputStream)
MyInputStream.Close

Hope this will be helpful and saves time for newbies like me.

Regards
Anser


Hi what is oRecord.get ? a list ? Map ?
thanks AH
 
Top