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

tufanv

Expert
Licensed User
Longtime User
Erel, I know you will say use RDC :) but is there any way to strenghten the security for the mysql connector ?
 

tufanv

Expert
Licensed User
Longtime User
When i do this, is it safe to use this Erel ?
The main problem with this solution is that the script will execute any query it receives. You can rewrite the script and put the queries in the PHP script and only send the parameters for the queries.
 

tufanv

Expert
Licensed User
Longtime User
Any query can not be executed if we pre enter the queries in php and just send parameters as i understand.?
 
Last edited:

tufanv

Expert
Licensed User
Longtime User
Erel can you clearify it. Is it safe with parammeters. If it is not still safe will try to switch to rdc but it will be much of work. So if it is safe with parameters i will change the php and coninue
 

tufanv

Expert
Licensed User
Longtime User
Isnt it the same for rdc ? I mean of course a hacker may do anything but in rdc also parameters are sent so the same danger stays for rdc also ? I mean if they are same no need to change if not i must consider rdc and change the whole system
 

tufanv

Expert
Licensed User
Longtime User
Ok Thank you Erel ! Sorry for bothering you too much about this topic =) I will stick to old system for now .
 

suciwulans

Active Member
Licensed User
Longtime User
i want try this tutorial. i have question. the connection between android and computer server using wifi? can i use locally webserver without using internet connection for this tutorial? thanks.
 

BarryW

Active Member
Licensed User
Longtime User
Can these method use to connect in Xampp database directly using wifi connection?
 

wmardian

Member
Licensed User
Longtime User
Dear Erel and forum member,
I have several questions;
1. can we use this method to insert data to mysql? I doubt about that..
2. what is the best method to upload image file to database/server folder?

Your help is very appreciated
Thanks in advance

best regards
 

wmardian

Member
Licensed User
Longtime User
Dear Erel,
Thanks, it is very appreciated.
1. thanks
2. but RDC can not be used in shared domain environment. Dedicated server is rather expensive..

Thanks in advance
Best Regards
 

wmardian

Member
Licensed User
Longtime User
Dear Erel,
Could you please giving a clue how to use it to insert data?
What is the content of the sub "Jobdone" should be?
And should php code have json if use it to insert data?

Thank in advance
Best Regards
 

tufanv

Expert
Licensed User
Longtime User
For the example Erel Published, you can use
B4X:
ExecuteRemoteQuery("INSERT INTO countries VALUES('" & "4" & "','" & "maldives" & "','" & "323233" & "')" , COUNTRY_POPULATION)
This is just an example. Under jobdone, for the jobname "COUNTRY_POPULATION" which you have used during the insert, you dont have to add a code. But for example if you want to show a message if adding is succesful you can add msgbox("Adding Complete","Warning") . The part jobdone means what to do when insert code completed.


Dear Erel,
Could you please giving a clue how to use it to insert data?
What is the content of the sub "Jobdone" should be?
And should php code have json if use it to insert data?

Thank in advance
Best Regards
 
Status
Not open for further replies.
Top