The Android Guide

Android Database connectivity using php

Most of the upcoming Android developers find difficulty in connecting android device to mysql externally. We can use sqlite which is an internal database of Android but it will hardly solve our problem.

Another approach is to make use of php scripts. Php scripts act as an interface between Android device and a database.

Things required:

Android sdk
Eclipse
Mysql
Xampp/Wamp server(xampp server used for this example)
Php scripts (Basic knowledge of php for querying data from db)

Android device alone cannot connect to mysql database neither it has any drivers to do so. Android fetches the data from mysql through php scripts in the form of JSON. JSON is a data interchange format. Other data interchange format is xml. It is light and simple to use JSON as compared to xml.

Ex of JSON Data :

[
{
“id”:1                                           – JSON Object 1
“name”:test1
},
{
“id”:2                                          – JSON Object 2
“name”:test2
},
{
“id”:3                                         –   JSON Object 3
“name”:test3
}
]

id	Name
1	Test1
2	Test2
3	Test3

All requested data is inside the “[“ and “]” which is an array. The main JSON array will contain JSON objects and other JSON arrays. Each JSON object starts with a “{“ and ends with a “}”.

Create the following database schema and table

Database Name : test
Database Table : testable
CREATE TABLE `test`.`testtable` (
`id` int(10) unsigned NOT NULL auto_increment,
`field1` text NOT NULL,
`field2` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Briefing

We will be making use of post parameters to pass parameters from Android to php. This parameters sent from Android will be encoded and appended to the url. Php will fetch the parameters, substitute in the required place and execute the query. The result of the query will be converted into json format and sent back to Android device as response.

Create 2 php files (testphp.php and testphp1.php) in c:\xampp\htdocs and copy paste the following content in it.

PHP script: testphp.php

<?php
$f1= $_POST[‘field1’]; //used to collect parameters sent by android to php
$f2=$_POST[‘field2’]; //used to collect parameters sent by android to php
$dbhandle = mysql_connect(“localhost:3306”, “root”,”root”) //used to connect to database(server,username, password)
or die(“Unable to connect to MySQL”);
$selected = mysql_select_db(“test”,$dbhandle) //used to select database
or die(“Could not select examples”);
$result=mysql_query(“insert into testtable (field1,field2) values(‘$f1′,’$f2’)”);
mysql_close($dbhandle);
?>

PHP script: testphp1.php

<?php
$dbhandle = mysql_connect(“localhost:3306”, “root”,”root”) //used to connect to database
or die(“Unable to connect to MySQL”);
$selected = mysql_select_db(“test”,$dbhandle) //used to select database
or die(“Could not select examples”);
$result=mysql_query(“SELECT * FROM testtable”);
while($row=mysql_fetch_assoc($result))
$nrows[]=$row;
print(json_encode($nrows)); //convert the resultset into json
mysql_close($dbhandle);
?>

Go to Eclipse>File>New Project>Android Application Project
Enter project name as DBTestProject.
Choose lowest Minimum reuired sdk as api 8 froyo, Target sdk as api 17 jelly beans and compiled with api 17 jelly beans. So with this your application will work from android device with 2.2 OS to android device with 4.2 os. Click Next and finish.

The following image depicts the same:

Create following files in Eclipse

ViewData.java in src/com.example.dbtestproject/
view.xml in res/layout

Existing files in Eclipse

MainActivity.java in src/com.example.dbtestproject/
activity_main.xml in res/layout

Now copy and paste the content from below into following files.

MainActivity.java

package com.example.dbtestproject;import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;import android.app.Activity;
import android.app.ProgressDialog;
import android.content.Intent;
import android.graphics.Paint.Join;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;public class MainActivity extends Activity{
public static ProgressDialog progressDialog;
public static Button btn,btn11;
public static EditText edt1,edt2;
public static JSONArray JArray;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
btn=(Button)findViewById(R.id.button1);
btn11=(Button)findViewById(R.id.button2);
edt1=(EditText)findViewById(R.id.editText1);
edt2=(EditText)findViewById(R.id.editText2);
btn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
new Async_loading().execute();
}
});btn11.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
startActivity(new Intent(MainActivity.this,ViewData.class));
}
});
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.activity_main, menu);
return true;
}public static JSONArray executePOST(String url,int nvp_count,ArrayList<NameValuePair> nvp) throws ClientProtocolException, IOException, JSONException
{HttpClient httpclient       =             new DefaultHttpClient();
HttpPost httppost                   =             new HttpPost(url);
if(nvp_count>0)
httppost.setEntity(new UrlEncodedFormEntity(nvp));
HttpResponse response       =             httpclient.execute(httppost);
HttpEntity entity                      =             response.getEntity();
InputStream inputStream =                entity.getContent();
BufferedReader reader        =             new BufferedReader(new InputStreamReader(inputStream,”iso-8859-1″),8);
StringBuilder sBuild                 =             new StringBuilder();
String line                                    =             null;
while ((line = reader.readLine()) != null){
sBuild.append(line + “\n”);
}inputStream.close();
return new JSONArray(sBuild.toString());
}
class Async_loading extends AsyncTask<Void, Integer, Void>
{
@Override
protected void onPreExecute() {
// TODO Auto-generated method stub
Log.i(“preexecute”,”enter”);
progressDialog = new ProgressDialog(MainActivity.this);
progressDialog.setTitle(“Loading”);
progressDialog.setMessage(“Please wait Loading”);
progressDialog.setProgress(0);
progressDialog.setMax(100);
progressDialog.setIndeterminate(false);
progressDialog.setCancelable(false);
progressDialog.show();
Log.i(“preexecute”,”leave”);
}
@Override
protected Void doInBackground(Void… params)
{
synchronized (this)
{
try
{
Log.i(“backgrnd”,”enter”);
ArrayList<NameValuePair> nvp=new ArrayList<NameValuePair>(4);
nvp.add(new BasicNameValuePair(“field1”,edt1.getText().toString()));
nvp.add(new BasicNameValuePair(“field2”,edt2.getText().toString()));JArray = executePOST(“http://10.0.2.2/testphp.php”,2,nvp);
this.wait(100);
publishProgress(100);
Log.i(“backgrnd”,”leave”);}catch (InterruptedException e)     { e.printStackTrace();Log.i(“catch2”, “done”+e);         }catch (Exception e)      {      e.printStackTrace();Log.i(“catch3”, “done”+e);         }}return null;}@Override
protected void onProgressUpdate(Integer… values) {
// TODO Auto-generated method stub
progressDialog.setProgress(values[0]);
}@Override
protected void onPostExecute(Void result)
{
progressDialog.dismiss();
Toast.makeText(MainActivity.this, “Data Added”, Toast.LENGTH_LONG).show();
edt1.setText(“”);
edt2.setText(“”);
}
}
}

ViewData.java

package com.example.dbtestproject;
import java.util.ArrayList;
import org.apache.http.NameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import android.app.Activity;
import android.app.ProgressDialog;
import android.content.Intent;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
import android.widget.Toast;
public class ViewData extends Activity
{
public static Button btn;
public static TextView tv;
public static String str;
public static ProgressDialog progressDialog;public static JSONArray JArray;
@Override
protected void onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.view);
btn=(Button)findViewById(R.id.button11);
tv=(TextView)findViewById(R.id.textView11);
new Async_loading().execute();
btn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
startActivity(new Intent(ViewData.this,MainActivity.class));
}
});
}class Async_loading extends AsyncTask<Void, Integer, Void>
{
@Override
protected void onPreExecute() {
// TODO Auto-generated method stub
Log.i(“preexecute”,”enter”);
progressDialog = new ProgressDialog(ViewData.this);
progressDialog.setTitle(“Loading”);
progressDialog.setMessage(“Please wait Loading”);
progressDialog.setProgress(0);
progressDialog.setMax(100);
progressDialog.setIndeterminate(false);
progressDialog.setCancelable(false);
progressDialog.show();
Log.i(“preexecute”,”leave”);
}@Override
protected Void doInBackground(Void… params)
{
synchronized (this)
{
try
{
Log.i(“backgrnd”,”enter”);
ArrayList<NameValuePair> nvp=new ArrayList<NameValuePair>(0);JArray = MainActivity.executePOST(“http://10.0.2.2/testphp1.php”,0,nvp);
str=””;
for(int i=0;i<JArray.length();i++)
{
try
{
str=str+JArray.getJSONObject(i).getString(“id”)+”\t”+JArray.getJSONObject(i).getString(“field1”)+”\t”+JArray.getJSONObject(i).getString(“field2″)+”\n”;
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
this.wait(100);
publishProgress(100);
Log.i(“backgrnd”,”leave”);
}
catch (InterruptedException e) { e.printStackTrace();Log.i(“catch2”, “done”+e); }
catch (Exception e) { e.printStackTrace();Log.i(“catch3”, “done”+e); }
}
return null;
}@Override
protected void onProgressUpdate(Integer… values) {
// TODO Auto-generated method stub
progressDialog.setProgress(values[0]);
}@Override
protected void onPostExecute(Void result)
{
progressDialog.dismiss();
Toast.makeText(ViewData.this, “Data Added”, Toast.LENGTH_LONG).show();
tv.setText(str+””);
}
}
}

activity_main.xml

view.xml

<?xml version=“1.0” encoding=“utf-8”?>
<RelativeLayout xmlns:android=“http://schemas.android.com/apk/res/android”
android:layout_width=“match_parent”
android:layout_height=“match_parent”
android:orientation=“vertical” ><TextView
android:id=“@+id/textView11”
android:layout_width=“wrap_content”
android:layout_height=“wrap_content”
android:layout_above=“@+id/button11”
android:layout_alignParentLeft=“true”
android:layout_alignParentRight=“true”
android:layout_alignParentTop=“true”
android:text=“TextView” />
<Button
android:id=“@+id/button11”
android:layout_width=“wrap_content”
android:layout_height=“wrap_content”
android:layout_alignParentBottom=“true”
android:layout_centerHorizontal=“true”
android:text=“Button” />
</RelativeLayout>

AndroidManifest.xml

<?xml version=“1.0” encoding=“utf-8”?>
<manifest xmlns:android=“http://schemas.android.com/apk/res/android”
package=“com.example.dbtestproject”
android:versionCode=“1”
android:versionName=“1.0” >
<uses-sdk
android:minSdkVersion=“8”
android:targetSdkVersion=“17” />
<uses-permission android:name=“android.permission.INTERNET”/>
<application
android:allowBackup=“true”
android:icon=“@drawable/ic_launcher”
android:label=“@string/app_name”
android:theme=“@style/AppTheme” >
<activity
android:name=“com.example.dbtestproject.MainActivity”
android:label=“@string/app_name” >
<intent-filter>
<action android:name=“android.intent.action.MAIN” />
<category android:name=“android.intent.category.LAUNCHER” />
</intent-filter>
</activity>
<activity android:name=“ViewData”></activity>
</application></manifest>

Important Points

Namevaluepair is used in Android code to pass the parameters to php script.

ArrayList<NameValuePair> nvp=new ArrayList<NameValuePair>(4);
nvp.add(new BasicNameValuePair(“field1”,edt1.getText().toString()));

where field1 = Same as the post parameter($_POS[‘field1’]) used in php scripts.

edt1.getText().toString() = Value which is to be sent to php script.

i.e “field1” is the name and “edt1.getText().toString()” is the value and hence called as name value pair.

Remember the following

$f1= $_POST[‘field1‘]; nvp.add(new BasicNameValuePair(“field1“,edt1.getText().toString()));

‘field1 ‘ parameter should be same in php code as well as android code.

public static JSONArray executePOST(String url,int nvp_count,ArrayList<NameValuePair> nvp)

This function takes the url(location of php script), count of namevaluepair and the namevaluepair object and returns the data fetched by php script in JSON Array format.
Eg MainActivity.executePOST(“http://10.0.2.2/testphp1.php”,0,nvp);

In the code asynchronous task has been used so that the code can run on any platform right from 2.x to 4.x.Till Android 2.x the Network state could be accessed directly from main thread but from Android 4.x it should be accessed in a background thread.

Earlier php scripts could be directly executed in the main thread till Android 2.x. But from Android 3.x onwards we have to execute in background thread (ie background method of asynchronous taks.). If you do not use Asynchronous task then the code will work only on Android devices with OS upto 2.x.

Download sample project.