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,125
Last edited:

Walter Scafati

Active Member
Licensed User
Longtime User
Yes, I use this server for a php application for my company. Now I want to connect to data stored in the same mysql database with an android app.
 

Ricardo Gonzalez Gaete

Member
Licensed User
Longtime User
Hi'

I'm developing an application but when I try to connect to the remote server, the error in the application Java.net.socketimeoutexception
Call code via browser and appears this.

Warning: mysqli_connect(): (HY000/2003): Can't connect to MySQL server on '200.28.130.15' (110) in /home/kreasoft/public_html/php/maestro.php on line 8

Warning: mysqli_error() expects parameter 1 to be mysqli, boolean given in /home/kreasoft/public_html/php/maestro.php on line 8


Estoy ocupando el siguiente código de PHP

<?php
$databasehost = "??????";
$databasename = "????";
$databaseusername ="???";
$databasepassword = "?????";
$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);
?>

I am doing wrong please help

 

Myr0n

Active Member
Licensed User
Longtime User
The phpcode works like a charm, but if you aren't entered the good information in the beginning of the phpcode doesn't gonna work.
If you think that the error is coming from your b4a code you should start a new thread and post your b4a code to help you out.
 
Last edited:

LWGShane

Well-Known Member
Licensed User
Longtime User
The following query works in PHPMyAdmin, but not in B4J:

B4X:
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE  'mydb'
ORDER BY  `TABLES`.`TABLE_NAME`

Any ideas?
 
Status
Not open for further replies.
Top