Android Tutorial Connect Android to MySQL Database Tutorial

A new more powerful framework is now available: jRDC2.

This tutorial explains the basic concepts required for creating a connection between Android device and a remote server. In this case it is a MySQL database.
A tutorial for connecting to SQL Server is available here.

Android cannot connect directly to the database server (search for JdbcSQL). Therefore we need to create a simple web service that will pass the requests to the database and will return the response.

For this example I've created a new database that lists the countries population. The data was derived from a UN database.

The database includes a single table named "countries" with three columns:

PHP Script

The web service is made of a single PHP script running on the same server.
You have several options for the web service implementation. You can create several prepared statements which will be filled with parameters passed in the request.
Or you can take the complete query from the request and pass it to the database server.
There are high security risks with issuing queries received from an unknown user. If your service is public then you will probably want to choose the first option.
You should also make sure to correctly escape the parameters. See this php method: PHP: mysql_real_escape_string - Manual

In our example I've chosen the second option which takes the query and passes it to the database directly.
I've restricted the database user to SELECT queries (in MySQL configuration).

The PHP code:

<?php

$databasehost = "localhost";
$databasename = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";

$con = mysqli_connect($databasehost,$databaseusername,$databasepassword, $databasename) or die(mysqli_error($con));
mysqli_set_charset ($con , "utf8");
$query = file_get_contents("php://input");
$sth = mysqli_query($con, $query);

if (mysqli_errno($con)) {
   header("HTTP/1.1 500 Internal Server Error");
   echo $query.'\n';
   echo mysqli_error($con);
}
else
{
   $rows = array();
   while($r = mysqli_fetch_assoc($sth)) {
     $rows[] = $r;
   }
   $res = json_encode($rows);
    echo $res;
    mysqli_free_result($sth);
}
mysqli_close($con);
?>

The script takes the query from the POST data and submits it to the database.
The result is then written in JSON format.

B4A code

Our application displays a list of countries. When the user presses on a country, its population is retrieved from the database and displayed.

The code sends the query and then when the result arrives the JSON is parsed and displayed.
Note that in this case the JSON object is made of an array of one or more maps.

Edit: Code updated to use OkHttpUtils2.

 

[Erel]

I recommend you to store the images outside of the database and then you will be able to download the images with another call.

 

[Peter Simpson]

How can I expand and send connection info to PHP file

Hello,
I'm new to Basic4Android(a new 24 hour user), I think that it is great.

I've developed in vb.net for a number if years now and I'm really enjoying Basic4Android. I'm looking to write a personal app for myself to monitor multiple databases.

Anyway, I changed and got the routines working with my own MySQL databases, bringing back my query results.

I was wondering could anybody give me an example on how to change both the PHP file and the Basic code so that the connection information is not stored in the PHP file but sent through the basic code.

I have a number of different databases and I don't want to create PHP files for each one.

Also, what is the best way to store data on my phone and tablet?

Thanks in advance for your responses

 

[timo]

Welcome Peter.

I was wondering could anybody give me an example on how to change both the PHP file and the Basic code so that the connection information is not stored in the PHP file but sent through the basic code.

You can store the access informations in b4a code, but most providers don't allow you doing this (it's like accessing 100% from remote):

<?php
$hst  = $_GET['hst'];
$db = $_GET['db'];
$usr = $_GET['usr'];
$pwd = $_GET['pwd'];

$conn_DB=mysql_connect($hst,$usr,$pwd) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
//.....etc.

-------------------------
'B4A:
Dim adr As String : adr= "http://www.yourSite.com/file.php?hst=youHost&db=youDb&usr=yourUser&pwd=yourPass"
'etc

So, it is better to put the access infos in the php file directly ($pwd="yourPass"; etc.). Generally, other parameters then hosting,user,password and dbname can be sent from remote with no problem.

I have a number of different databases and I don't want to create PHP files for each one.

I don't understand what you mean here. [understood: adr/adr1/adr2,etc]

 

[Peter Simpson]

Wow, thank you for this information.
I knew about the query string from developing aspx ecommerce websites for clients, but not about the PHP side of things as I'm not a PHP developer.

Anyway, thank you again for the response

 

[Peter Simpson]

Worked first time

Thank you

 

[kaputo]

Same error as Post 4 (JSON array)

Hi, I have got the same JSON error as post 4. Looking in the b4a log file I see that res holds the html of my default web page, and not values from the database! Please advise, thanks

 

[Erel]

Check the url. Start with testing the url in a browser. You should see a JSON response, not an html page.

 

[kaputo]

Check the url. Start with testing the url in a browser. You should see a JSON response, not an html page.

Hi, I see the web site

 

[Erel]

So you are not using the correct url. You should use the url of the php script (see the first post).

 

[kaputo]

So you are not using the correct url. You should use the url of the php script (see the first post).

Hi, the url I am using is TMS - Super Slogan (test.php is the script in the first post) and as you can see this takes me to the default web page (although the url is not redirected). It seems that the host is doing something strange

 

[kaputo]

Hi, the url I am using is TMS - Super Slogan (test.php is the script in the first post) and as you can see this takes me to the default web page (although the url is not redirected). It seems that the host is doing something strange

Hi, I now get a reasonable value for res, but still have the JSON array error:

** Activity (main) Create, isFirst = true **


** Activity (main) Resume **


Response from server: {"f_name":"Charles","f_surname":"Bradshaw"},{"f_name":"Victor","f_surname":"Delgado"}]



<!-- Free Web Hosting with PHP, MySQL and cPanel, No Ads Analytics Code -->

<script type="text/javascript" src="http://analytics.hosting24.com/count.php"></script>

<noscript><a href="http://www.hosting24.com/"><img src="http://analytics.hosting24.com/count.php" alt="web hosting" /></a></noscript>

<!-- End Of Analytics Code -->

main_hc_responsesuccess (java line: 341)



java.lang.RuntimeException: JSON Array expected.
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:59)
at com.my3.main._hc_responsesuccess(main.java:341)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:136)
at anywheresoftware.b4a.BA$2.run(BA.java:244)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:130)
at android.app.ActivityThread.main(ActivityThread.java:3703)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:841)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:599)
at dalvik.system.NativeStart.main(Native Method)
java.lang.RuntimeException: JSON Array expected.

Any idea what the problem now is?

Thanks

 

[kaputo]

Hi, I now get a reasonable value for res, but still have the JSON array error:

** Activity (main) Create, isFirst = true **


** Activity (main) Resume **


Response from server: {"f_name":"Charles","f_surname":"Bradshaw"},{"f_name":"Victor","f_surname":"Delgado"}]



<!-- Free Web Hosting with PHP, MySQL and cPanel, No Ads Analytics Code -->

<script type="text/javascript" src="http://analytics.hosting24.com/count.php"></script>

<noscript><a href="http://www.hosting24.com/"><img src="http://analytics.hosting24.com/count.php" alt="web hosting" /></a></noscript>

<!-- End Of Analytics Code -->

main_hc_responsesuccess (java line: 341)



java.lang.RuntimeException: JSON Array expected.
at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:59)
at com.my3.main._hc_responsesuccess(main.java:341)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:136)
at anywheresoftware.b4a.BA$2.run(BA.java:244)
at android.os.Handler.handleCallback(Handler.java:587)
at android.os.Handler.dispatchMessage(Handler.java:92)
at android.os.Looper.loop(Looper.java:130)
at android.app.ActivityThread.main(ActivityThread.java:3703)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:507)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:841)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:599)
at dalvik.system.NativeStart.main(Native Method)
java.lang.RuntimeException: JSON Array expected.

Any idea what the problem now is?

Thanks

Ok, opened my eyes and saw that square bracket missing coz I removed first character in the code - all now OK
Many thanks

 

[Peter Simpson]

Hello,
I was wondering if it is possible to run an sql query then run another query straight afterwards. Silly question I know but I keep getting the following error message.

java.lang.IllegalStateException: Content has been consumed

Query one runs to login
SQLTaskId=1
ExecuteRemoteQuery("SELECT * FROM `tbusers` WHER ??????=??????", SQLTaskId)

Query two that runs after login
SQLTaskId=2
ExecuteRemoteQuery("SELECT * FROM `tbinvoices` WHERE `Creator`='Internet HMB'", SQLTaskId)

Dim Res As String 'is in Globals

This is my response code. SQLTaskId=1 works great, SQLTaskId=2 throws the error after SQLTaskId=1 is completed.
Sub hc_ResponseSuccess(Response As HttpResponse, TaskId As Int)
If SQLTaskId=1 Then
Res = Response.GetString("UTF8")
Check_Login
End If

If SQLTaskId=2 Then
Res = Response.GetString("UTF8")
Load_Invoices
End If

Response.Release
End Sub

Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
Dim Req As HttpRequest

HC.Initialize("hc")
Req.InitializePost2("http://www.myurl.co.uk/db_connect.php?hst=" & Hst & "&db=" & Db & "&usr=" & Usr & "&pwd=" & Pwd & "", Query.GetBytes("UTF8"))
HC.Execute(Req, TaskId)
End Sub

Basically no matter what I do I can't stop the emulator or my tablet from throwing the error java.lang.IllegalStateException: Content has been consumed. If I press continue, the grid will then populate with the test invoices that I have stored on my online MySQL server.

 

[Erel]

HttpClient should only be initialized once.

 

[Peter Simpson]

I'm still having the same problem...

Thank you for the reply Erel.
I tried the following but that did not work either.

I originally removed HC.Initialize("hc") from

Sub ExecuteRemoteQuery(Query As String, TaskId As Int)

and had

Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
HC.Initialize("hc")
End If


...Other code
End Sub

Pressing continue will bring up MySQL results but now I'm seriously stuck :-(

Thanks in advance for your assistance

 

[Erel]

I tried the following but that did not work either.

Did you get any error message?

you should check the logs.

 

[Peter Simpson]

Attached are screen shots

Did you get any error message?

you should check the logs.

Hello again, sorry to be troubling everybody on a Sunday :-(

I query the database twice. Once to login, then a second time to query the invoice table to get a list of invoices.

I've checked the logs a few times but to no avail. Below is the only line that appears to be causing the problem in the logs...

java.lang.IllegalStateException: Content has been consumed

I've attached screen shots

Once I click yes for continue, the grid behind in blue will populate with MySQL information stored on my online server, but only after the error message has appeared.

Thank you again...

 

[Erel]

This error means that you are trying to read the returned result more than once. It is impossible to say why it happens without seeing your complete code.

 

[Peter Simpson]

More than once

This error means that you are trying to read the returned result more than once. It is impossible to say why it happens without seeing your complete code.

OK, I'll look into this in more detail.

Thank you and happy Easter.

 

[Peter Simpson]

Fixed - Content has been consumed error

This error means that you are trying to read the returned result more than once. It is impossible to say why it happens without seeing your complete code.

Thank you for that useful information about reading results twice. I've now fixed the issue and got it working.

Tar...