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:

Erel

B4X founder
Staff member
Licensed User
Longtime User
i can do that with basic4android?.
Yes.

This PHP script however doesn't support blobs. You will need to modify it. The simplest solution will be to encode the data as base64 string and then decode it in the PHP script and add it to the database (if you can store the data encoded in base64 then you do not need to change the script at all).
 

giannimaione

Well-Known Member
Licensed User
Longtime User
connect mysql remote with B4PPC ?

Hi Erel,

i forgot, :BangHead:
there is a way to connect with B4PPC to remote DB mysql ?
thanks
 

oscar cabrales

Member
Licensed User
Longtime User
thanks!!!

Yes.

This PHP script however doesn't support blobs. You will need to modify it. The simplest solution will be to encode the data as base64 string and then decode it in the PHP script and add it to the database (if you can store the data encoded in base64 then you do not need to change the script at all).


thanks EREl my program now load and download images to and from mysql
greetings from colombia
:sign0098:
 

shashkiranr

Active Member
Licensed User
Longtime User
Hi ,

I am using xampp and installed mysql. I copied the php code from the first post and corrected the first line. My php script is

<?php

$databasehost = "localhost";
$databasename = "test_b4a";
$databaseusername ="test";
$databasepassword = "test";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
$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);
}
?>

I gave the link in the app as
req.InitializePost2("http://localhost/countries.php", Query.GetBytes("UTF8"))

I got this error Error: org.apache.http.conn.HttpHostConnectException: Connection to http://localhost refused, StatusCode: -1 . I saw few previous posts where ppl encountered this but no one has specified how they solved it :(

Anyone can tell me how to connect to local host Database.

One more question The database test_b4a has the table testcountries in which all the countries info are stored. is the $databasename = "test_b4a" correct or $databasename = "testcountries" is correct??
 

shashkiranr

Active Member
Licensed User
Longtime User
Erel i have installed Xampp in my laptop , I used 10.0.0.2 and changed $databasehost = "10.0.0.2"; and i got this error

Error: java.net.SocketTimeoutException: Socket is not connected, StatusCode: -1

I also used 127.0.0.1 which is local host address but still got the same error as the one posted in my previous post... :(

im using wifi bridge and testing on my phone
 

mc73

Well-Known Member
Licensed User
Longtime User
The functionality is the same. The code is now based on HttpUtils2 which better handles Http communication and is easier to work with.

The old code called Request.GetString. This method doesn't work in Android 4+ when the targetSdkVersion is set to 14 or above.


I see. Great!
 

Noel

Member
Licensed User
Longtime User
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).

Hello Erel,

How should the code look like when using the 1st (way more secure) option? (me hate mySQL injection) :-/
 

ivanomonti

Expert
Licensed User
Longtime User
mmm error:

Warning: mysql_connect() [function.mysql-connect]: Lost connection to MySQL server at 'reading initial communication packet', system error: 111 in /mc.php on line 8
Lost connection to MySQL server at 'reading initial communication packet', system error: 111

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

how can I fix, Thank
 

ivanomonti

Expert
Licensed User
Longtime User
mmm error:

Warning: mysql_connect() [function.mysql-connect]: Lost connection to MySQL server at 'reading initial communication packet', system error: 111 in /mc.php on line 8
Lost connection to MySQL server at 'reading initial communication packet', system error: 111

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

how can I fix, Thank

resolved, do not want to host name but ip, Thank
 
Last edited:

Sinan Tuzcu

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

Hallo Erel,

where exactly the line should be?
B4X:
SELECT CONVERT(Column1 USING utf8) FROM ...

can you please show me how I install your example, here in the query?


I need to convert the column Nachrichten
B4X:
ExecuteRemoteQuery("SELECT Vorname, Name, Nachricht, Link FROM mesajlar  WHERE Vorname="Hans", "Name")

greet
sinan
 
Status
Not open for further replies.
Top