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:

trinitysj

Member
Licensed User
Longtime User
I just checked.. and it is PHP 5.1.6

so that isn't the problem and the db has the correct table and data in it..
 

kikloo

Member
Licensed User
Longtime User
Hi.

I am new to B4A and I tried this code, but when I run I get this error:

B4X:
Compiling code.                         Error
Error parsing program.
Error description: Unknown type: httpclient
Are you missing a library reference?
Occurred on line: 5
Dim hc As HttpClient

How to fix ?

Thanks.
 

kikloo

Member
Licensed User
Longtime User
Hi,

Can you please in your every tutorial tell what elements it uses and what their names and properties have to be so that newbies like me can do it properly.

Thanks.
 

trinitysj

Member
Licensed User
Longtime User
that error is due to the http library not being included.. you have to click the lib tab at the bottom right of the screen and check the http refreence.. it is only available in the purchased version of basic4android. it is NOT available in the trial.

hope that this helps...
 

kikloo

Member
Licensed User
Longtime User
that error is due to the http library not being included.. you have to click the lib tab at the bottom right of the screen and check the http refreence.. it is only available in the purchased version of basic4android. it is NOT available in the trial.

hope that this helps...

Hi,

I got it.

Thanks.
 

trinitysj

Member
Licensed User
Longtime User
That solved it...

so for anyone that needs the php portion of the Connect Android to MySql Database Tutorial, This is the one that works for me on PHP 5.1.6

PHP:
<?

$databasehost = "localhost";
$databasename = "dbname";
$databaseusername ="username";
$databasepassword = "password";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
$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;
    }
    //This line below is changed from json_encode to __json_encode
    print __json_encode($rows);
}

function __json_encode( $data ) {            
    if( is_array($data) || is_object($data) ) { 
        $islist = is_array($data) && ( empty($data) || array_keys($data) === range(0,count($data)-1) ); 
        
        if( $islist ) { 
            $json = '[' . implode(',', array_map('__json_encode', $data) ) . ']'; 
        } else { 
            $items = Array(); 
            foreach( $data as $key => $value ) { 
                $items[] = __json_encode("$key") . ':' . __json_encode($value); 
            } 
            $json = '{' . implode(',', $items) . '}'; 
        } 
    } elseif( is_string($data) ) { 
        # Escape non-printable or Non-ASCII characters. 
        # I also put the \\ character first, as suggested in comments on the 'addclashes' page. 
        $string = '"' . addcslashes($data, "\\\"\n\r\t/" . chr(8) . chr(12)) . '"'; 
        $json    = ''; 
        $len    = strlen($string); 
        # Convert UTF-8 to Hexadecimal Codepoints. 
        for( $i = 0; $i < $len; $i++ ) { 
            
            $char = $string[$i]; 
            $c1 = ord($char); 
            
            # Single byte; 
            if( $c1 <128 ) { 
                $json .= ($c1 > 31) ? $char : sprintf("\\u%04x", $c1); 
                continue; 
            } 
            
            # Double byte 
            $c2 = ord($string[++$i]); 
            if ( ($c1 & 32) === 0 ) { 
                $json .= sprintf("\\u%04x", ($c1 - 192) * 64 + $c2 - 128); 
                continue; 
            } 
            
            # Triple 
            $c3 = ord($string[++$i]); 
            if( ($c1 & 16) === 0 ) { 
                $json .= sprintf("\\u%04x", (($c1 - 224) <<12) + (($c2 - 128) << 6) + ($c3 - 128)); 
                continue; 
            } 
                
            # Quadruple 
            $c4 = ord($string[++$i]); 
            if( ($c1 & 8 ) === 0 ) { 
                $u = (($c1 & 15) << 2) + (($c2>>4) & 3) - 1; 
            
                $w1 = (54<<10) + ($u<<6) + (($c2 & 15) << 2) + (($c3>>4) & 3); 
                $w2 = (55<<10) + (($c3 & 15)<<6) + ($c4-128); 
                $json .= sprintf("\\u%04x\\u%04x", $w1, $w2); 
            } 
        } 
    } else { 
        # int, floats, bools, null 
        $json = strtolower(var_export( $data, true )); 
    } 
    return $json; 
} 
?>
 

trinitysj

Member
Licensed User
Longtime User
This might want to be included into the tutorial so that anyone on php 5.1.x or less has the ability of the tutorial working for them.

just my suggestion.
 

squaremation

Active Member
Licensed User
Longtime User
anyone have a sample of this db? trying to duplicate one using sample and i'm db stupid.





:sign0085:
 
Last edited:

kikloo

Member
Licensed User
Longtime User
anyone have a sample of this db? trying to duplicate one using sample and i'm db stupid.:sign0085:

Hi,

Though the script is already online here: www.b4x.com/android/countries.php and you can use it to do the testing etc.

But here's the sample DB code for MySQL:

B4X:
CREATE TABLE IF NOT EXISTS `countries` (
  `Name` varchar(30) NOT NULL,
  `ID` varchar(4) NOT NULL,
  `Population` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `countries`
--

INSERT INTO `countries` (`Name`, `ID`, `Population`) VALUES
('India', 'IN', 1234),
('Australia', 'AU', 123);

Hope this helps.

Thanks
 

squaremation

Active Member
Licensed User
Longtime User
Found this had nothing to do with my Code. For some reason my server (PhpFog) "stopped liking my password". Password change on server, change in php, works fine.:sign0094:

I was working on something using this example. All of a sudden the list from the db wont load.

I have this in the logs:
B4X:
** Activity (main) Create, isFirst = true **


** Activity (main) Resume **


Response from server: 


       Lost connection to MySQL server at 'reading initial communication packet', system error: 111
** Service (serivcemod) Start **


main_hc_responsesuccess (B4A line: 127)


countries = parser.NextArray 'returns a list with maps

java.lang.RuntimeException: JSON Array expected.
   at anywheresoftware.b4a.objects.collections.JSONParser.NextArray(JSONParser.java:59)
   at iebonic.gdictionary.sma.com.main._hc_responsesuccess(main.java:805)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:521)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:136)
   at anywheresoftware.b4a.BA$2.run(BA.java:244)
   at android.os.Handler.handleCallback(Handler.java:587)
   at android.os.Handler.dispatchMessage(Handler.java:92)
   at android.os.Looper.loop(Looper.java:123)
   at android.app.ActivityThread.main(ActivityThread.java:4627)
   at java.lang.reflect.Method.invokeNative(Native Method)
   at java.lang.reflect.Method.invoke(Method.java:521)
   at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:858)
   at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)


   at dalvik.system.NativeStart.main(Native Method)
java.lang.RuntimeException: JSON Array expected.

Didn't make any changes to the php at all.
 
Last edited:

Ropy

New Member
Hi...

I created this project and I got Missing Reference error...

Where can I get the reference?
 

askez

Member
Licensed User
Longtime User
i'm bringing from the DB dirty text cell with a lot of HTML code in it.
how can i clean it?
 
Status
Not open for further replies.
Top