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.

 

[Swissmade]

Don't get this working

I have a MYSQL Database where I like to get some info from.
When I copy the php source to the webroot and connect with the app.
Then I get in the LogCat the source of the php file.:BangHead:
I have a connection to the database when I debug the php Script.
What i'm doing wrong here:sign0085:
Server is Xampp.

 

[Erel]

Your server should run the PHP script. For some reason it now treats it as a text file.

 

[Swissmade]

Your server should run the PHP script. For some reason it now treats it as a text file.

Erel,

Thanks again for your fast respond.
Found the error the Script started with <? and not with <?php

:sign0098:

 

[Reemj]

:sign0013:

maybe this question is not suitable

but I create DB and PHP file

this error is appear

Compiling code. Error
Error parsing program.
Error description: Unknown type: httpclient
Are you missing a library reference?
Occurred on line: 2
Dim hc As HttpClient

what is mistake??
I need to answer
Thanks

 

[Erel]

You should add a reference to the Http library. It is done in the Libraries tab in the right pane. Note that it can only be done with the full version.

 

[Hans Scholten]

Is there someone in this group who can change this part of php code in asp code

$query = file_get_contents("php://input");
$sth = mysql_query($query);

if (mysql_errno()) {
header("HTTP/1.1 500 Internal Server Error");
echo $query.'\n';
echo mysql_error();
}
else
{
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
$rows[] = $r;
}
print json_encode($rows);
}

 

[Reemj]

if I change the line 40 to

req.InitializePost2("C:\xampp\htdocs\countries.php", query.GetBytes("UTF8"))

this error appears

An error has occured in sub: main excuteremotequery
(B4A line:40)
req.InitializePost2("C:\xampp\htdocs\countries.php" , query.GetBytes("UTF8"))
java.lang
IllegalArgumentException:
Illegal character in schemeSpecificPart at index 2:
C:\xampp\htdocs\countries.php

Thanks a lot

 

[Reemj]

I edit php file to this

$databasehost = "localhost";
$databasename = "android";
$databaseusername ="root";
$databasepassword = "1234";

the type of ID is VARCHAR (5)
population is INT (5)
name VARCHAR (20)

 

[Erel]

The PHP script should be located in a server that can execute the script. It cannot be accessed as a regular file.

 

[Reemj]

thank you

thank you ,

I changed the link to this http://localhost/countries.php

and change type of populaion from VARCHAR to text

:sign0089:

another problem appears,

in java.lang.exception: sub
hc_responceerror signture dose not match expected signuter.
continued?




tahnks for the help

 

[Erel]

You should update your Http library. There is a link at the bottom of the first post.

 

[Reemj]

tank you fr answer me,

after adding bridge the mobaile can not connect to MYSQ.

MYSQL on laptop ..

I think because IP

the IP: http://xx.x.x.x/countries.php

thanks a lot

 

[Reemj]

hello
when i bridge connect between a laptop and a mobile it brings only interface.
how can i bring both the interface and the data on databae?

thanks

 

[Erel]

I'm sorry but I don't understand the issue here. Are you accessing a remote MySQL server?

 

[Reemj]

yes, with B4A bridg

I can't access to database.

but if the application work on emulator it is work.

 

[Erel]

As I wrote as a response to your question in a different thread, B4A Bridge only launches the program. If you have any problem while running the code it is not related to B4A Bridge.

 

[rajaramcomputers]

MySQL and Basic4Android

I tried the example provided by hosting the service in my site rajaramsystems.com.

Let us assume if someone not hosted their site anywhere and want to test with a (eg. Xampp Server) local server like http://localhost/countires.php with the emulator - any suggestion to try it with basic4Android.:sign0085:

 

[Erel]

You can test it with our server. The php script should be working.

 

[anaylor01]

I need some help with this. Your example runs just fine. I tried to alter it to match a DB I created on my webserver but I get a 404 error. Here is what I did. I copied the attached query.php script to the same folder as my index.html. I then altered your example to match my DB.

Sub Process_Globals
    Dim hc As HttpClient
    Dim COUNTRIES_LIST, COUNTRY_POPULATION As Int
    COUNTRIES_LIST = 1
    COUNTRY_POPULATION = 2
End Sub

Sub Globals
    Type TwoLines (First As String, Second As String)
    Dim lblPopulation As Label
    Dim ListView1 As ListView
    Dim lblCountry As Label
End Sub

Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
        hc.Initialize("hc")
    End If
    Activity.LoadLayout("1")
    FetchCountriesList
End Sub
Sub FetchCountriesList
  '  ProgressDialogShow("Fetching list of countries")
    'Gets all the available countries
    ExecuteRemoteQuery("SELECT col1, col2 FROM table1 ORDER BY col1", COUNTRIES_LIST)
End Sub
Sub ListView1_ItemClick (Position As Int, Value As Object)
    If IsBackgroundTaskRunning(hc, COUNTRY_POPULATION) Then
        ToastMessageShow("Wait for previous call to finish.", False)
        Return
    End If
    Dim tl As TwoLines
    tl = Value
    lblCountry.Text = tl.Second
    lblPopulation.Text = "Calling server..."
    ExecuteRemoteQuery("SELECT col1 FROM table1", COUNTRY_POPULATION)
End Sub
Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
    Dim req As HttpRequest
    req.InitializePost2("http://imgsinc.onlinewebshop.net/query.php", query.GetBytes("UTF8"))
    hc.Execute(req, TaskId)
End Sub
Sub hc_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)
    Log("Error: " & Reason & ", StatusCode: " & StatusCode)
    If Response <> Null Then
        Log(Response.GetString("UTF8"))
        Response.Release
    End If
    ProgressDialogHide
End Sub
Sub hc_ResponseSuccess (Response As HttpResponse, TaskId As Int)
    Dim res As String
    res = Response.GetString("UTF8")
    Log("Response from server: " & res)
    Dim parser As JSONParser
    parser.Initialize(res)
    Select TaskId
        Case COUNTRIES_LIST
            'add the countries to the ListView
            Dim countries As List
            countries = parser.NextArray 'returns a list with maps
            For i = 0 To countries.Size - 1
                Dim m As Map
                m = countries.Get(i)
                'We are using a custom type named TwoLines (declared in Sub Globals).
                'It allows us to later get the two values when the user presses on an item.
                Dim tl As TwoLines
                tl.First = m.Get("col1")
                tl.Second = m.Get("col2")
                ListView1.AddTwoLines2(tl.First, tl.Second, tl)
            Next
            ProgressDialogHide
        Case COUNTRY_POPULATION
            Dim l As List
            l = parser.NextArray
            If l.Size = 0 Then
                lblPopulation.Text = "N/A"
            Else
                Dim m As Map
                m = l.Get(0)
                lblPopulation.Text = m.Get("col1")
            End If
    End Select
    response.Release
End Sub

 

[Erel]

Http 404 error means that the page cannot be found. Your php file is probably not accessible or the address is wrong.