In this post I'm going to describe how we can read data from MySQL
database and show them in a Android list view. You can download the complete Android project from
here. To fetch data here I used
a PHP script which encodes data into json format.
This project has three main parts.
1. MySQL database
2. PHP web service
3.Android web service client
1. MySQL database.
My database has only one table named "emp_info" and it has two columns. "employee name" and "employee no". "employee no" is the primary key.
2.PHP web service
Use following PHP script to fetch data from the database and to encode data in to json format.
<?php
$host="XXXXX"; //replace with database hostname
$username="XXXXX"; //replace with database username
$password="XXXXX"; //replace with database password
$db_name="XXXXXX"; //replace with database name
$con=mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql = "select * from emp_info";
$result = mysql_query($sql);
$json = array();
if(mysql_num_rows($result)){
while($row=mysql_fetch_assoc($result)){
$json['emp_info'][]=$row;
}
}
mysql_close($con);
echo json_encode($json);
?>
You can see the output of php by clicking below url:
http://cpriyankara.coolpage.biz/employee_details.php
3.Android web service client.
This part is bit complected. Android activity is a combination of Async Task json and list view. If you are not familiar with those stuff look following tutorials.
Android Async Task and web service access
http://codeoncloud.blogspot.com/2013/07/android-web-service-access-using-async.html
Android list view
http://codeoncloud.blogspot.com/2013/07/how-to-populate-android-list-view-from.html
Here is the code for main Android activity.
package com.axel.mysqlphpjson;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
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.os.AsyncTask;
import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.Toast;
public class MainActivity extends Activity {
private String jsonResult;
private String url = "http://cpriyankara.coolpage.biz/employee_details.php";
private ListView listView;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
listView = (ListView) findViewById(R.id.listView1);
accessWebService();
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
// Async Task to access the web
private class JsonReadTask extends AsyncTask<String, Void, String> {
@Override
protected String doInBackground(String... params) {
HttpClient httpclient = new DefaultHttpClient();
HttpPost httppost = new HttpPost(params[0]);
try {
HttpResponse response = httpclient.execute(httppost);
jsonResult = inputStreamToString(
response.getEntity().getContent()).toString();
}
catch (ClientProtocolException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
private StringBuilder inputStreamToString(InputStream is) {
String rLine = "";
StringBuilder answer = new StringBuilder();
BufferedReader rd = new BufferedReader(new InputStreamReader(is));
try {
while ((rLine = rd.readLine()) != null) {
answer.append(rLine);
}
}
catch (IOException e) {
// e.printStackTrace();
Toast.makeText(getApplicationContext(),
"Error..." + e.toString(), Toast.LENGTH_LONG).show();
}
return answer;
}
@Override
protected void onPostExecute(String result) {
ListDrwaer();
}
}// end async task
public void accessWebService() {
JsonReadTask task = new JsonReadTask();
// passes values for the urls string array
task.execute(new String[] { url });
}
// build hash set for list view
public void ListDrwaer() {
List<Map<String, String>> employeeList = new ArrayList<Map<String, String>>();
try {
JSONObject jsonResponse = new JSONObject(jsonResult);
JSONArray jsonMainNode = jsonResponse.optJSONArray("emp_info");
for (int i = 0; i < jsonMainNode.length(); i++) {
JSONObject jsonChildNode = jsonMainNode.getJSONObject(i);
String name = jsonChildNode.optString("employee name");
String number = jsonChildNode.optString("employee no");
String outPut = name + "-" + number;
employeeList.add(createEmployee("employees", outPut));
}
} catch (JSONException e) {
Toast.makeText(getApplicationContext(), "Error" + e.toString(),
Toast.LENGTH_SHORT).show();
}
SimpleAdapter simpleAdapter = new SimpleAdapter(this, employeeList,
android.R.layout.simple_list_item_1,
new String[] { "employees" }, new int[] { android.R.id.text1 });
listView.setAdapter(simpleAdapter);
}
private HashMap<String, String> createEmployee(String name, String number) {
HashMap<String, String> employeeNameNo = new HashMap<String, String>();
employeeNameNo.put(name, number);
return employeeNameNo;
}
}
Add Internet permission to AndroidManifest.xml
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.axel.mysqlphpjson"
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.axel.mysqlphpjson.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>
</application>
</manifest>
Code for main activity layout.
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context=".MainActivity" >
<ListView
android:id="@+id/listView1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentTop="true"
android:layout_centerHorizontal="true"
android:layout_marginTop="14dp" >
</ListView>
</RelativeLayout>
Quick demo of the application:
Was above information helpful?
Your comments always encourage me to write more...
