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:

GMan

Well-Known Member
Licensed User
Longtime User
i meant this (and this works for me)

B4X:
$databasehost = "localhost";
 

shaxboz

Member
Licensed User
Longtime User
If I use localhost, it shows me "Response from server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)"
 

GMan

Well-Known Member
Licensed User
Longtime User
Are there any restricitons on your server (are not real familiar with that, but i also had a server that made some things impossible) - can you check with PHP-Info ?
 

tufanv

Expert
Licensed User
Longtime User
as i read in previous posts users managed to execute insert command with this lib and tutorail is that correct ?
 

tufanv

Expert
Licensed User
Longtime User
RDC is too complicated for me Erel =) i am strarting with this. Now i have my server set with php and mysql and also phpmyadmin. created my database. with name db . And created a php file that cntains the code with changing the server information to mine.
now will check if i can get the countries ty for your answer
 

tufanv

Expert
Licensed User
Longtime User
I could receive the countries list with no problem. But the retrieve time is about 2-3 seconds and if we try to use this way it would take quite long time for getting many datas from server. for example with local sql i can get 10 different strings in 1 second maybe but with this it would take about 20 30 seconds. Is there any way to fasten this proceses
 

GMan

Well-Known Member
Licensed User
Longtime User
Sometimes i have the same problems, but the reason is always the provider, which has a low data throuhgput in case of much traffic (shared server)
 

tufanv

Expert
Licensed User
Longtime User
have 2 questions.
I can receive the countries list with no problem and their populations. but when i try to execute an insert command I use for button1_click:
ExecuteRemoteQuery("INSERT INTO countries VALUES('" & "4" & "','" & "555555" & "','" & "nigeria" & "')" , COUNTRY_POPULATION)

1) what do i have to write after the coma, I am sure it must not be country population ( i am just trying to add a new country with : id , population,country)
2) it gives me internal server error for this

ty
 

tufanv

Expert
Licensed User
Longtime User
When i click the button1 Erel, it just say internal server error in the app. Nothing more
 

tufanv

Expert
Licensed User
Longtime User
Erel, I was trying to insert integer value into text field that was the problem. I could add the row. But altough the row inserted into db i got an error in my app says : JSON array expected.

what is the reason fr that ?
 

tufanv

Expert
Licensed User
Longtime User
Sorry i couldnt totally get it. Which part must i change for that
The current code expects a JSON response with the query results. In this case there is no result so you need to modify the code skip this step.
 
Status
Not open for further replies.
Top