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

brodmuehler

Member
Licensed User
Longtime User
Hallo
First it all i want to congratulate you for b4a. this is a great piece of work and I am really impressed.

since I am fairly new to programming apps, I have some questions ....

where do I have to place the php script. I have a webhosting area with 1und1 where I run a webpage qitha mysql database in the background. what I want to do, is to exchange data between my app a d its sqlite database and the mysql database at my provider. could you perhaps point me in a direction to find a start. assuming I understood your example correctly it pulls data from your mysql database, can I also transfer data to the mysql db?

any help is highly appreciated
thanks a lot
 

Peter Simpson

Expert
Licensed User
Longtime User
Excellent tutorial Erel.
I've only just updated my apps from using HttpClient to using HttpUtils2 instead.

Using a web service instead of the the MySQL libraries means that I can utilise the newer SDK's, as the libraries limit the Android SDK to SDK 8 due to knows MySQL driver issues.

I personally prefer to send the server connection details through the job.PostString, the database connection details are not stored locally and easily accessible. My server was hack a couple of years ago, my websites were all changed(forwarded to another website). No database details stored in php files means that if it ever happened again, at least my customers database login details are safe as they are stored on the actual devices themselves.

Cheers again Erel...
 

AlexPanic

New Member
Hi Everyone,

I am having an issue. I would like to populate two spinners from MySQL table. In MySQL is list of people with rows such as: id, Name, Number, City, Class. ( 1. Alex, 587956, New York, Maths...)
I would like to populate one spinner with City and the other one with Class, but those two spinners should be let's say connected. If I choose City New York, I would like to get option to select Class from the second spinner, but only Classes that are available in New York, if you understand me :)

Connection with MySQL is working fine over PHP. I need some good example for android and PHP file.

Tnx a lot.

Rgds,

Alex
 

AlexPanic

New Member
Two possible options:
- Store the data in a local SQLite db. You can then fetch the classes that belong to the selected city.
- Use a Map with the cities as the keys and the values are Lists with the classes.
I am using MySQL... cause table is too big. Regarding Map, do you know any good examples, step by step tutorials or similar? Cause I have no idea how to do it..
 

Dogbonesix

Active Member
Licensed User
Longtime User
The message says the sample to download has been updated to use HttpUtils2 but when I extract the file the only Library in use is the original Http and no reference to HttpUtils2. What am I doing wrong?
 

swamisantosh

Member
Licensed User
Longtime User
Hello Erie
Hello Erel

I'n new B4a Programming i tried php code countries.php to access mysql database, can you help to write Countries.php access SQL Server Database
//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");
i tried code, but i'm getting error message please help me.
 

somed3v3loper

Well-Known Member
Licensed User
Longtime User
The issue with this implementation is that it lets a malicious user to modify your program and send any query they want to your server.
The way to prevent it is by coding the queries in the PHP script and let the client to only send the required parameters.

I know very little about php . Can you please help me to code queries in PHP file?
Or any tutorial for doing it?
 

swamisantosh

Member
Licensed User
Longtime User
A new more powerful framework is now available: Remote Database Connector.

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

$databasehost = "localhost";
$databasename = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
$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);
}
?>
The script takes the query from the POST data and submits it to the database.
The result is then written in JSON format.

Basic4android code

Our application displays a list of countries. When the user presses on a country, its population is retrieved from the database and displayed.

SS-2013-01-29_16.42.32.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 HttpUtils2 instead of HttpClient.


IF I HAVE TO CONNECT TO SQL SERVER INSTEAD MYSQL WHAT ARE CHANGES I HAVE TO DO IN COUNTRIES.PHP CODE
 

swamisantosh

Member
Licensed User
Longtime User
A new more powerful framework is now available: Remote Database Connector.

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

$databasehost = "localhost";
$databasename = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
mysql_query("SET CHARACTER SET utf8");
$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);
}
?>
The script takes the query from the POST data and submits it to the database.
The result is then written in JSON format.

Basic4android code

Our application displays a list of countries. When the user presses on a country, its population is retrieved from the database and displayed.

SS-2013-01-29_16.42.32.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 HttpUtils2 instead of HttpClient.
Connect Android to MySQL Database Tutorial
http://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/

the above code work fine. PHP-MySQL

I was trying to write above program with PHP,SQLSERVER
I'm getting a error message $sth = mssql_query($query);
Please help in this regarding
code below
--------------------------
<?php
// Connect to MSSQL and select the database
$link1 = mssql_connect('SMS-PC\SQLEXPRESS', 'MYLOGIN', 'CENTR');
mssql_select_db('SDS', $link1);

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

$sth = mssql_query($query);

if (mssql_errno()) {
header("HTTP/1.1 500 Internal Server Error");
echo $query.'\n';
echo mssql_error();
}
else
{
$rows = array();
while($r = mssql_fetch_array($sth)) {
$rows[] = $r;
}
print json_encode($rows);
}

?>
 
Status
Not open for further replies.
Top