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:

hogiebaer

Active Member
Licensed User
Longtime User
Hi Erel,

i have a big problem. I use the original script for our server (php 5.3.3). I have created a database with your table and filled some records.
Than i changed the link from your B4A Demo to our server. The result is an error 500 (internal server error). My second try was to change your script.
The line
$query = file_get_contents("php://input");
i changed directly to the query
$query = "SELECT name, id FROM countries ORDER BY id"
and this works and sends the correct data back.

It seems, the $query is empty:(
I have make NO change in the demo, only the Link of the script !!

Please help me:eek::eek:

Thanks
Holger
 

JohnD

Active Member
Licensed User
Longtime User
The code below shows how to pass the entire query for processing in the .php script. I want to pass just parameters. How would I pass multiple parameters to the .php script? RDC is not an option for me. My host will not allow me to open a port. Your help is appreciated. -JD.
B4X:
job.PostString(gsCountryInsertURL, Query)
 

JohnD

Active Member
Licensed User
Longtime User

Marlon Tayag

Member
Licensed User
Longtime User
Erel can you please help me. I' trying to use your sample code on SQL connection I made my own database on the local pc same as yours. But when I run the program I get this error JSON Array Expected. I already log the output of my json query and there were a bunch of other characters before the correct data. How can I remove this unwanted characters?
 

Attachments

  • error.png
    error.png
    11.2 KB · Views: 306

JohnD

Active Member
Licensed User
Longtime User
I get the following Error when I try to connect to my website and execute a php script:

"java.net.UnknownHostException :Unable to resolve host "www.mydomain.com": No address associated with hostname."

This is true there is not an ip address associated with my host name. My domain is hosted on a shared server. However, the functionality in the MySQL sample program worked fine until this morning. I can still execute all scripts from my browser. Your help would be appreciated. Thanks, JD.
 

jynx

New Member
Sir Erel, I have no idea how it works.. but i have a problem on my project. i have created a website and save into a localhost using WAMP . and i want to get the name, image and description on the database. how will i start for that? can you give me a tips? i know how to code PHP but on the B4A side no idea . can you give me a HINT? thanks . ill use B4A for my Thesis on school. im on 50% on process.
 

guidoarfini

Member
Licensed User
Longtime User
hello guys... i'm test the msq examle in old time and it work... now, i using new version xampp and sytem return...error..

change the php file into httdoc? can you help me? thenk you..
 

Attachments

  • nonFunge.txt
    2.1 KB · Views: 328

guidoarfini

Member
Licensed User
Longtime User
Sorry... where and what change?
can you help me?
thank you..
i 'm using xampp... chang my.ini mysql?
 

Attachments

  • myini.txt
    5.6 KB · Views: 323

guidoarfini

Member
Licensed User
Longtime User
erel... i check discussion .. but... nothing

if i using XAMPP 1.7.7 and php 5.3.8 my application run
if i using XAMPP 1.8.2 and php 5.4.25 my application error...

please help me...
 

gigi0ne

Member
Licensed User
Longtime User
prova ad andare da dos e lancia php pincopallino.php (dove pincopallino.php è il tuo file) e vedi se ti ritorna i dati, se ti ritorna il sorgente hai qualcosa di non corretto nella conf di php
sei sotto w o linux?
 
Status
Not open for further replies.
Top