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
How can i add an (small) picture to a mysql database and here in the code ?
 

GMan

Well-Known Member
Licensed User
Longtime User
Hoi Erel,
solved - dont need a pic ;-)

next question:
if i read in the list from the mysql db (that works fine) i like to insert a new Label to the ListView1 whenever a new "category"-table content was read (also already in the table, and the content was show in an extra label on the screen.
But i want to add that in the List of the Listview (once, when a new new category was found)

Here is the part from the query:
B4X:
    Case SORTEN_KATEGORIE
                Dim l As List
                l = parser.NextArray
                If l.Size = 0 Then
                    lblKategorie.Text = "N/A"
                Else
                    Dim m As Map
                    m = l.Get(0)
                    lblKategorie.Text = (m.Get("kategorie"))
                End If
 

GMan

Well-Known Member
Licensed User
Longtime User
Not sure that I understand. However consider using CustomListView class instead. It is easier to work with.
Can i simply changed that with all the given code or have i to adopt the CLV "new" ?
 

GMan

Well-Known Member
Licensed User
Longtime User
Not sure that I understand. However consider using CustomListView class instead. It is easier to work with.
I meant it this way:

i have a stock with screws, articels in the db.
the articels db table has a field CATEGORY and is linked i.e. to M8.
In this category all M8 screws are listed, as M8x15, M8x20 etc.

In the showed list all articles are one under the other, with no "optical" cut between all the screws.

So i like, if all i.e. M8 Screws are displayed, a additional label that simply shows the new Category, i.e. M8 = all M8 screws are listed, if M10 appears as a new category it should be showd once as M10 ... etc.

Hope, this decleares what i mentioned to declare :rolleyes:;)
 

GMan

Well-Known Member
Licensed User
Longtime User
Another question:
can i check if the database is updated?
If yes , can i then load it down and use it offline (to avoid making traffic for nothing) ?
 

GMan

Well-Known Member
Licensed User
Longtime User
I got the code working, but it didnt do exact that what i want.

Here is the part:
B4X:
Case SCREWS_KATEGORIE
                Dim l As List
                l = parser.NextArray
                If l.Size = 0 Then
                    lblKategorie.Text = "N/A"
                Else
                  
                    Dim m As Map
                    m = l.Get(0)
                    lblKategorie.Text = (m.Get("kategorie"))
'                  
                    ' KATEGORIE SEPERATOR
                    Dim tlz As TwoLines
                    tlz.First = "Kategorie: " 'm.Get("kategorie")
                    tlz.Second = lblKategorie.Text
                  
                    Dim Labeleins As Label
                    Labeleins = ListView1.TwoLinesLayout.Label
                    Labeleins.TextSize = 14
                    Labeleins.TextColor = Colors.green
                    Labeleins.Text = tlz.First
                  
                    Dim Labelzwei As Label
                    Labelzwei = ListView1.TwoLinesLayout.SecondLabel
                    Labelzwei.TextSize = 14
                    Labelzwei.TextColor = Colors.ARGB(255,246,7,22)
                    Labelzwei.Text = tlz.Second
                  
                    ListView1.AddTwoLines2(Labeleins.text, Labelzwei.Text, tlz)
                End If
With this code, the list was updated with the additional entry, but only after tapping an entry - and then the new line is megred to the end instead on the actual position (without tapping ;-))
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
can i check if the database is updated?
If Job.Success is true then the query or command were executed successfully.

You cannot download a MySQL database as you need a MySQL server in order to access it.

I don't exactly understand the list view problem. However you should probably switch to CustomListView class. It will give you more control.
 

GMan

Well-Known Member
Licensed User
Longtime User
Thx for the Hint with Job.Success

The listView"problem": i simply want to have a an extra Label (inserted in the ListView1) if a new Category was found in the db table when reading it in
 

GMan

Well-Known Member
Licensed User
Longtime User
OK, i'll TRY - maybe you can give me a link to a how-to ? :D
 

GMan

Well-Known Member
Licensed User
Longtime User
Thx, solved it already
 

shaxboz

Member
Licensed User
Longtime User
Hi!
I cannot connect to mysql server
B4X:
Response from server: Access denied for user 'shaxboz'@'50.6.77.95' (using password: YES)

But, my server ip is not 50.6.77.95
 

GMan

Well-Known Member
Licensed User
Longtime User
what script are you using ?
 

shaxboz

Member
Licensed User
Longtime User
B4X:
<?

$databasehost = "76.163.252.220";
$databasename = "mybase";
$databaseusername ="shaxboz";
$databasepassword = "sss222";

$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);
}
?>
 

GMan

Well-Known Member
Licensed User
Longtime User
Hi!
I cannot connect to mysql server
B4X:
Response from server: Access denied for user 'shaxboz'@'50.6.77.95' (using password: YES)

But, my server ip is not 50.6.77.95

Isn't that the IP from your local device ?

Also: did you try localhost in the script (with the database host) ?
 
Status
Not open for further replies.
Top