Android Tutorial Connect Android to MySQL Database Tutorial

Status
Not open for further replies.
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:
mysql_1.png

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:
<?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.

upload_2016-8-3_13-4-25.png



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.
 

Attachments

  • MySQL.zip
    8 KB · Views: 17,464
Last edited:

JTmartins

Active Member
Licensed User
Longtime User
Encoding problems

Hi everyone,

I've noticed that some people are having encoding problems.

I also had them, and apparently I just solved them. (at least it seems to be working, although only tested with 3 rows of data)

Using Erel provided example for the PHP script, every time I had a character such as é or á or ú, í, etc in the database, I was getting a NULL instead of the data on the field.

I spent a full night trying to understand what was wrong, and I knew it was on the server side, as I was getting a NULL even on the browser.

So I added a line to the PHP script like this

B4X:
<?php


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

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());

mysql_query("SET CHARACTER SET utf8"); ' This is the line I added

$query = file_get_contents("php://input");
//$query = $_GET["query"]; ' forget this one, was used just for tests..No need to be here
$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);
}
?>

After this on the browser I was not getting NULL anymore, but data was appearing as Jos\u00e9 instead of José. (I think I could have fixed it by using some HTML tags for encodig the page, which I actually didn't tried.)

However in the android device, it was arriving correctly, and that was what I wanted.

I hope this can help anyone, specialy if using languages like Spanish, Portuguese, etc.

Thanks

José
 
Last edited:

en3rgie

New Member
Licensed User
Longtime User
Hi!

How can i use two activity in this tutorial? That have 1 activity for country list, and other for country population?
 

desof

Well-Known Member
Licensed User
Longtime User
First make sure that the data was inserted correctly. Open the database with a tool like PhpMyAdmin and check that the data exists.

You can use the Convert MySQL function to convert strings to UTF8:
MySQL :: MySQL 5.0 Reference Manual :: 11.10 Cast Functions and Operators
B4X:
SELECT CONVERT(Column1 USING utf8) FROM ...

ESTOY DESESPERADO 4 DIAS Y NO PUEDSO SOLUCIONAR ESTO A PESAR DEL INTENTO DE VARIOS EN AYUDARME,!!

TAMPOCO ME FUNCIONA ESTO Y ME SIGUE DEVOLVIENDO NULL !!
 

frevacom

Member
Licensed User
Longtime User
Error:Unauthorized

Hi,

Can anybody help? While using the files from the tutorial, everything is working fine. But when I want to connect to my own MySQL DB I'm getting this error message: Error:Unauthorized. To be sure I made the same DB as in the tutorial. I've double checked host, username,... How can I find out the problem?
 

frevacom

Member
Licensed User
Longtime User
This is a server configuration issue. Probably something with the user database permissions.


Thanks for your tip! I checked and can't find any restrictions on the server side. And when I modify the php file, I have no problem to have a JSON string with all the data I need. Still strugling...
 

frevacom

Member
Licensed User
Longtime User
Thanks for your tip! I checked and can't find any restrictions on the server side. And when I modify the php file, I have no problem to have a JSON string with all the data I need. Still strugling...

I found my problem. The php-file was saved in the wrong server folder. Thanks again for your tip!
 

Sytek

Active Member
Licensed User
Longtime User
ESTOY DESESPERADO 4 DIAS Y NO PUEDSO SOLUCIONAR ESTO A PESAR DEL INTENTO DE VARIOS EN AYUDARME,!!

TAMPOCO ME FUNCIONA ESTO Y ME SIGUE DEVOLVIENDO NULL !!

Forget that/Olvida eso

Use JTMartins php script on server side/Usa script proporcionado por JTMartins en el servidor.

B4X:
mysql_query("SET CHARACTER SET utf8"); ' This is the line I added
 
Last edited:

karmba_a

Member
Licensed User
Longtime User
Hi,

Arabic language appear on the form ??????????????????????

Used all charsets and the same problem

Arabic charset = ASMO-708
Arabic charset = DOS-720
Arabic charset = iso-8859-6
Arabic charset = x-mac-arabic
Arabic charset = windows-1256

:sign0085:
 

karmba_a

Member
Licensed User
Longtime User

we should set the PHP database client to provide and extract stored data in the correct format. This should be done right after calling myqsl_connect() function:

mysql_query('SET names=utf8');
mysql_query('SET character_set_client=utf8');
mysql_query('SET character_set_connection=utf8');
mysql_query('SET character_set_results=utf8');
mysql_query('SET collation_connection=utf8_general_ci');

Thanx
 

jatko

Member
Licensed User
Longtime User
Hi,
Is there someone, who can help me to change the php script to use user and password as the parameters? Should I change something in httputils2service?
 

jmon

Well-Known Member
Licensed User
Longtime User
Could someone help clarifying what is needed to setup an online mysql db? Is it just a file like Sqlite? Is it possible to host an online Sqlite db with multi user access?

Sent from my GT-P7500 using Tapatalk HD
 
Status
Not open for further replies.
Top