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

giacomo-italy

Member
Licensed User
Longtime User
I can not handle the storage and loading of images in a blob field in remote mysql db. Can anyone help me?
 

giacomo-italy

Member
Licensed User
Longtime User
The php posted in the first post doesn't work with blobs. You have two options:
1. Modify the php script to support blobs.
2. Switch to RDC which does support blobs.


ok Erel, thanks.
I did several tests with php to manage the blob and I did not succeed.
With the library 'mysql' I also did several tests with the blob with negative results.
I will pass to the RDC as you suggested.
For the rest, everything works fine.
 

enrico

Active Member
Licensed User
Longtime User
I have to send 3 queries to a remote MySQL server.
With the first I can correctly get and parse the response.
With the second I receive this error :

Response from server:
<html>
<head>
<title>Error 403 - Forbidden</title>
<meta http-equiv="Refresh" content="0;url=http://xxxxxxxxx/errore_404?" />
</head>
<body>
</body>
</html>

Is it a server problem that doesn't allow consecutive connections ?
Is there a workaround ?
 

enrico

Active Member
Licensed User
Longtime User
No, I've masked it. Anyway it was mysql.hostinger.it
Strange that first query gets a correct response and the second not.
Now I'm trying with a different server and I don't have this problem.
 

enrico

Active Member
Licensed User
Longtime User
Can you show me an example about how to avoid exposing the server connection data in the php file?
 

enrico

Active Member
Licensed User
Longtime User
There isn't a ExecuteRemoteQuery2 command to escape characters (like apostrophes for example) when I connect to a MySQL DB.
How can I do it ?
 

catyinwong

Active Member
Licensed User
Longtime User
In the codes it writes that all the text will be submitted or got in "UTF8", but I tested the codes and add a new row to a table in MYSQL and then get the information from it, turning out that the words have become strange strings.... is there anything to do to refine the decoding and encoding???
 

Anser

Well-Known Member
Licensed User
Longtime User
Can anybody tell me what is the purpose of the line
file_get_contents("php://input");
in the php code posted in the first post of this thread.
B4X:
$query = file_get_contents("php://input");
$sth = mysql_query($query);

Reading the PHP manual/instructions, I understand that file_get_contents() will read a file and returns it contents as a string.

If that is the case, then what is the relevance of "php://input" here.

According to my understanding, if the purpose was to read the contents of a file the function call should be something like
file_get_contents("./input.txt");

Or is it some kind of Php setting ?.

It may be a very silly question from my side, but I am very much new to this Php as well as B4A.

Regards
Anser
 

achtrade

Active Member
Licensed User
Longtime User
Yes. It works :sign0104:.

Just for reference. I modified the PHP script in this way:

B4X:
if (!isset($_GET["insert"]))
{
    $rows = array();
    while($r = mysql_fetch_assoc($sth))
    {
        $rows[] = $r;
    }
    print json_encode($rows);
}

And then, just for the INSERT queries, I call the script adding ?insert variable.

Hello danvica,

don't you mind to post the whole php file ?, I'm starting a project and I need something like that
 

antofer

Member
Licensed User
Longtime User
hello.

using google translator
I've been trying to connect to MySql days in a database installed on my server wamp.

I downloaded your example and works properly but I do not show me the list of countries

shows me error control in image

because it connects me with my server?

thank you very much
 

Attachments

  • pantallazo1.jpg
    pantallazo1.jpg
    267.1 KB · Views: 305
  • pantallazo2.jpg
    pantallazo2.jpg
    208.5 KB · Views: 298

antofer

Member
Licensed User
Longtime User
Please , could you give an example of php file modified to base64 ?
thank you very much
 

shaxboz

Member
Licensed User
Longtime User
Thanks Erel, but I couldn't write "cyrillic" texts...
Can anybody help me?
When I write and read cyrillic text, It was looking like "????????"
What should I do?
 
Status
Not open for further replies.
Top