Connecting to MySQL database

Mon, 05/10/2010 - 01:58 | by gabor

SDK Version:

The most spread method to connect to a remote MySQL database from an android device, is to put some kind of service into the middle. Since MySQL is usually used together with PHP, the easiest and most obvious way to write a PHP script to manage the database and run this script using HTTP protocol from the android system.

We can code the data in JSON format, between Android and PHP with the easy to use built in JSON functions in both languages.

I present some sample code, which selects data from a database depending on a given condition and creates a log message on the android side with the received data.

Lets suppose that we have a MySQL database named PeopleData, and a table int created, with the following SQL:

CREATE TABLE `people` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL ,
`sex` BOOL NOT NULL DEFAULT '1',
`birthyear` INT NOT NULL 
) 

We want to get all the data of the people, who were born after a specified year.
The PHP code will be very simple:
- connect to the database
- run an SQL query, with a WHERE block depending on data from POST/GET values
- output it in JSON format

For example we will have this functionality in the getAllPeopleBornAfter.php file:

<?php
mysql_connect("host","username","password");
mysql_select_db("PeopleData");
 
$q=mysql_query("SELECT * FROM people WHERE birthyear>'".$_REQUEST['year']."'");
while($e=mysql_fetch_assoc($q))
        $output[]=$e;
 
print(json_encode($output));
 
mysql_close();
?>

The Android part is only a bit more complicated:
-use a HttpPost to get the data, sending the year value
-convert response to string
-parse JSON data, and use it as you want

String result = "";
//the year data to send
ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
nameValuePairs.add(new BasicNameValuePair("year","1980"));
 
//http post
try{
        HttpClient httpclient = new DefaultHttpClient();
        HttpPost httppost = new HttpPost("http://example.com/getAllPeopleBornAfter.php");
        httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
        HttpResponse response = httpclient.execute(httppost); 
        HttpEntity entity = response.getEntity();
        InputStream is = entity.getContent();
}catch(Exception e){
        Log.e("log_tag", "Error in http connection "+e.toString());
}
//convert response to string
try{
        BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8);
        StringBuilder sb = new StringBuilder();
        String line = null;
        while ((line = reader.readLine()) != null) {
                sb.append(line + "\n");
        }
        is.close();
 
        result=sb.toString();
}catch(Exception e){
        Log.e("log_tag", "Error converting result "+e.toString());
}
 
//parse json data
try{
        JSONArray jArray = new JSONArray(result);
        for(int i=0;i<jArray.length();i++){
                JSONObject json_data = jArray.getJSONObject(i);
                Log.i("log_tag","id: "+json_data.getInt("id")+
                        ", name: "+json_data.getString("name")+
                        ", sex: "+json_data.getInt("sex")+
                        ", birthyear: "+json_data.getInt("birthyear")
                );
        }
}
}catch(JSONException e){
        Log.e("log_tag", "Error parsing data "+e.toString());
}

Of course it is possible to use HTTPS and send password to access data, or do more complex data processing on either side, or write more general code, which

does not include this much predefined parameters in the database accessing query.

Tags:

Bookmark/Search this post with

Connecting to MySQL database

Main menu

New tutorials from Helloandroid

Recent Apps

Android on Twitter

Poll

Useful resources

Android Development Projects