Android App to connect to MySQL

I recently ditched iOS to give Android a go and got myself a Samsung Galaxy II in order to get more involved in the ever growing industry which is mobile application development. Unlike Apple iOS the Android SDK has far less hardware restrictions and the cost of development and deployment is minimal at most. The biggest advantage I’ve found so far with my brief experience in app development is the fact that a bit of Java and PHP knowledge will go a very long way.

As I’m getting married next year and have spend a good deal of my time recently putting together a website for our wedding I decided that as a tester my first app would be an RSVP form which would connect to the website and populate a drop-down in the mobile app with the guest list taken from my MySQL database. Obviously for this post I won’t go through the whole app development but I will give you the basic code I’ve used to connect to a MySql database and populate a drop-down with the data.

First thing you need to do is get all the software installed you need. I would recommend going to the main Android SDK page for the information at the following location:
http://developer.android.com/sdk/index.html

For the purpose of this example I have been developing the app using  the Eclipse IDE which you can download from the following location:
http://www.eclipse.org/downloads/

I would recommend going  with the Eclipse Classic and it give you the most customisable options. Once you’re all installed and ready to go the first thing you need to do is create the PHP code on the server. The idea here is to create an application which calls a .php file on your server, the PHP does the query and returns the result in a format which the Java in the app can parse and print out.

So if we start with the connection php file, this will hold the information for connecting to the database itself, the db name, username and password.

connect.php 

<?php

   $conn = mysql_connect("localhost","username","password");
  $db = mysql_select_db("dbname");

?>

Obviously you will need to replace the string variables with what corresponds to your database. Save this on your server in the root folder so that it is easily accessible.
Next we’re going to create the .php file to make the query to the database.

select.php

<?php
include_once("connect.php");
$sqlString = "select * from tbl_guest order by Name ";
$rs = mysql_query($sqlString);
if($rs){
   while($objRs = mysql_fetch_assoc($rs)){
      $output[] = $objRs;
   }
   echo json_encode($output);
}
mysql_close();
?>

For the example I’ve created a table called tbl_guest with only one column called Name which we’re going to order the list by. All you need to do is modify the query to fit your own needs and then upload this file to the same location as connect.php.

So that’s the PHP done, now we need to go to the Eclipse IDE and set up the actual mobile application. Open Eclipse and select New>Project and follow the steps as shown below


Once you’ve created the project open up the LoginSQL.java file located under the src folder and add the following code:

LoginSQL.java

package com.login.app;

import java.io.BufferedReader;
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.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import android.app.Activity;
import android.app.ListActivity;
import android.app.ProgressDialog;
import android.content.DialogInterface;
import android.content.DialogInterface.OnCancelListener;
import android.net.ParseException;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;
import android.view.ContextMenu;
import android.view.View;
import android.view.ContextMenu.ContextMenuInfo;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;

public class LoginSQL extends Activity {
	TextView text_1,text_2 ;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		// TODO Auto-generated method stub
		super.onCreate(savedInstanceState);
		setContentView(R.layout.main);
		new task().execute();
	}

class task extends AsyncTask
{
	private ProgressDialog progressDialog = new ProgressDialog(Login.this); 
    InputStream is = null ;
    String result = "";
    protected void onPreExecute() {
       progressDialog.setMessage("Download data...");
       progressDialog.show();
       progressDialog.setOnCancelListener(new OnCancelListener() {
	public void onCancel(DialogInterface arg0) {
		task.this.cancel(true);
	   }
	});
     }
       @Override
	protected Void doInBackground(String... params) {
	  String url_select = "yoursite/select.php";

	  HttpClient httpClient = new DefaultHttpClient();
	  HttpPost httpPost = new HttpPost(url_select);

          ArrayList param = new ArrayList();

	    try {
		httpPost.setEntity(new UrlEncodedFormEntity(param));

		HttpResponse httpResponse = httpClient.execute(httpPost);
		HttpEntity httpEntity = httpResponse.getEntity();

		//read content
		is =  httpEntity.getContent();					

		} catch (Exception e) {

		Log.e("log_tag", "Error in http connection "+e.toString());
		}
	try {
	    BufferedReader br = new BufferedReader(new InputStreamReader(is));
		StringBuilder sb = new StringBuilder();
		String line = "";
		while((line=br.readLine())!=null)
		{
		   sb.append(line+"\n");
		}
			is.close();
			result=sb.toString();				

				} catch (Exception e) {
					// TODO: handle exception
					Log.e("log_tag", "Error converting result "
                                        +e.toString());
				}

			return null;

		}
	protected void onPostExecute(Void v) {

		// ambil data dari Json database
		try {
			JSONArray Jarray = new JSONArray(result);
			ArrayAdapter  adapter = new ArrayAdapter  
                        (Login.this, android.R.layout.simple_spinner_item );
			adapter.setDropDownViewResource(android.R.layout.simple_spinner_
                        dropdown_item);

			for(int i=0;i<Jarray.length();i++)
                        {
                             JSONObject Jasonobject = null;
                             text_1 = (TextView)findViewById(R.id.txt1);
                             Jasonobject = Jarray.getJSONObject(i);
                             adapter.add(Jasonobject.getString("Name"));
                        }
                        Spinner s = (Spinner) findViewById(R.id.ddName);
                        s.setAdapter(adapter);
                        this.progressDialog.dismiss();
                 } catch (Exception e) {
                        Log.e("log_tag", "Error parsing data "+e.toString());
                 }
        }
}
}

This will call the PHP on your server and then populate a dropdown, or as the Android OS calls them, Spinner. The only modification you should need to make here is to change the select.php location.

Next open up the main.xml found in the res/layout folder. Replace all of the code in there with the following:

main.xml

<?xml version="1.0" encoding="utf-8"?>
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="wrap_content" >

<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:orientation="vertical" >

<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Name:"
android:textAppearance="?android:attr/textAppearanceSmall" />

<Spinner
android:id="@+id/ddName"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
/>

<TextView
android:id="@+id/txt1"
android:layout_width="wrap_content"
android:layout_height="wrap_content" />
</LinearLayout>

</ScrollView>

Nothing should need changing here so save the file and close. This xml is used to generate the UI for those of you that don’t know.
The last thing we need to do is a small modification of the manifest for the project. By default android development applications will not have access to the network so we need to allow the permissions so that the Java code can make the call to your server and the .php files.

Open the  AndroidManifest.xml found in the root folder and add the following code:

AndroidManifest.xml

<uses-permission android:name="android.permission.INTERNET" />
<uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />

 That’s it, now all you need to do is compile it and run the application. You should get something that looks like the screenshot below where the dropdown is populated with the data from your table. 

Just as a disclaimer I have been playing about with the code so they may be minor errors but it runs for me when I run it so you should have any problem.

SIMILAR ARTICLES