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

anaylor01

Well-Known Member
Licensed User
Longtime User
The php and the whole site which is only a very small index.html page that says hey I am new to web programming is all 0777. When I manually type in http://imgsinc.onlinewebshop.net/query.php in the address bar it gives me
"The page you are looking for is either not existing or cannot be displayed! "
I would assume since my index.html file is working and the query.php file is in the same folder with the same permissions that it should work. I attached the php file as a screenshot in my last post. Any suggestions on where to look?
 

Helihead

Member
Licensed User
Longtime User
sqlite Update data

Hi,

I'm trying to do an Update on a blob field in one of my tables to replace the default bmp with a different bmp in the field 'photo' in an existing record. I have looked the web over and can't find an Update example for B4A.

The code below shows what I am trying but I get various exceptions with different variants of the Update. Usually I get an invalid token or an exception saying problem with Value.

My Selects and Inserts work fine. Anybody see what I am doing wrong with Update?

The db is initialized as sqlCon already by the time it gets to this code. fpath and fname are passed into this and I have confirmed that the sql statement looks correct with the right name, path and search parameters but it is failing on the syntax. I also tried various query and nonquery options but no luck.

Thanks Jeff


Dim InputStream1 As InputStream
InputStream1 = File.OpenInput(fpath, fname)
Dim OutputStream1 As OutputStream
OutputStream1.InitializeToBytesArray(1000)
File.Copy2(InputStream1, OutputStream1)
Dim Buffer() As Byte
Buffer = OutputStream1.ToBytesArray
sqlCon.ExecNonQuery2("UPDATE table SET VALUES('photo',?) WHERE rname = '" & name & "' AND manufacturer = '" & manu & "'",Array As Object(Buffer))
 

Harris

Expert
Licensed User
Longtime User
sqlCon.ExecNonQuery2("UPDATE table SET VALUES(photo,?) WHERE rname = '" & name & "' AND manufacturer = '" & manu & "'",Array As Object(Buffer))




C = SQL1.ExecQuery("SELECT * FROM "& TB &" WHERE ID = (Select MAX("& Cn &") FROM "& TB &")")

This select statement uses the params TB and Cn. I use a pair of double quotes to start and end each section of the statement. Yours appears to have single quotes in it. Is this required in PHP?

"SELECT * FROM "
& TB &
" WHERE ID = (Select MAX("
& Cn &
") FROM "
& TB &
")")
 

Helihead

Member
Licensed User
Longtime User
This select statement uses the params TB and Cn. I use a pair of double quotes to start and end each section of the statement. Yours appears to have single quotes in it. Is this required in PHP?


Mine is not PHP just B4A to sqlite. I posted here because that's where I grabbed the original code to insert the blob that I am now trying to update. Just noticed that there was a PHP discussion going on. Sorry.
 

kickaha

Well-Known Member
Licensed User
Longtime User
Hi,
sqlCon.ExecNonQuery2("UPDATE table SET VALUES('photo',?) WHERE rname = '" & name & "' AND manufacturer = '" & manu & "'",Array As Object(Buffer))

Is that valid syntax for an update?

I thought the syntax would be

sqlCon.ExecNonQuery2("UPDATE table SET photo = ? WHERE rname = '" & name & "' AND manufacturer = '" & manu & "'",Array As Object(Buffer))
 

Harris

Expert
Licensed User
Longtime User
sqlCon.ExecNonQuery2("UPDATE table SET photo = ? WHERE rname = '" & name & "' AND manufacturer = '" & manu & "'",Array As Object(Buffer))

photo = ? seems to be the correct syntax


The select statement I supplied works fine for me. My update statements are formated in the same fashion using params.

Hope you work it out.
 

osamaguc

New Member
please i'm suffering :)!! i really need to know how to set up this external database ! esp the architecture of the whole picture ! where do i have the database ? and where should i write that php ?
 

jukav

New Member
Licensed User
Longtime User
Bugs.

Hello.

Congratulations on your development environment you just bought.

Go ahead my apologies for using the google translator to post in English.

The point is that I'm trying to follow his example to connect to a mysql db, but I'm stuck on the error

<b>Parse error</b>: syntax error, unexpected ']', expecting T_STRING or T_VARIABLE or T_NUM_STRING in <b>/home/s10ea9bf/public_html/conect.php</b> on line <b>22</b><br />

error.png


conectphp.png


I would appreciate your help.

Greetings.
 

jukav

New Member
Licensed User
Longtime User
It is the image, ie the countries.php from their example.

In both Dreamweaver and Eclipse says error, first in this line (single quotes), then in line 13, also by quotation marks, and finally at 21, suggesting that changes a '],' but it still fails .

I've also tried to put <?php opening the file, but it always breaks, according to your ide, the general array ...
 

jukav

New Member
Licensed User
Longtime User
Sorry, but I'm trying your example on my remote server, and I just read that in the emulator if you are only going local ... That's right.
 

jukav

New Member
Licensed User
Longtime User
A thousand pardons ... had not closed the quotes after the pass. Works perfectly.

Salu2.
 

MaxApps

Active Member
Licensed User
Longtime User
http://myweb.dk/phpfile.php+TAG+VALUE how?

Is it possible to call a php file and include a "Tag" and a "Value"



Here is the PHP:


$tag =trim($_POST["tag"]);
$value =trim($_POST["value"]);

$sql = "INSERT INTO `mdbuser1213913`.$tag (tekst) VALUES ('$value')";

$result = mysql_query($sql);


Kind regards

Jmich
 
Last edited:

danvica

Member
Licensed User
Longtime User
Thanks Erel for the helpfull tutorials.

I a very newbie to the Android and webserver world but thanks to you in a couple of days I already setup a small web-db application that allows to share data on several tablet...

Just a question. I used your PHP script to execute both "SELECT" and "INSERT" mysql statements.

For "SELECT" no problem, mysql_fetch_assoc can manage the returned resource, but mysql_query return TRUE/FALSE if the query contains "INSERT".

This way mysql_fetch_assoc returns a warning (not an error) saying "mysql_fetch_assoc() expects parameter 1 to be resource". And it is right !

Is there anyone who know how to differentiate a resource from a boolean ? I tried on several PHP forum but it seems they create the queries inside the script, so they already know if a query is a "SELECT" or an "INSERT" and they know what to expect from it.

Thanks.

P.S. As a newbie a suggest the use of EasyPHP. It combine PHP + Apache + phpmyadmin + mysql. Great tool.
 
Status
Not open for further replies.
Top