Menampilkan Data dari MySQL ke Dalam List View Pada Android Menggunakan PHP

Posted by:

Pada tutorial ini, anda akan belajar membuat sebuah aplikasi android sederhana yang menggunakan List View. Data yang akan ditampilkan adalah kumpulan aplikasi android yang populer di Play Store lengkap dengan icon, total download dan data rating dari masing-masing aplikasi. Data total download dan rating yang kita gunakan dalam tutorial ini bukan merupakan data asli, akan tetapi hanya berupa data dumi untuk tujuan demo.

Data dumi tersebut akan disimpan dalam database MySQL. Kemudian anda juga akan membuat script PHP yang akan mengambil data dumi tadi dari database MySQL. Aplikasi android yang anda buat kemudian memanggil script PHP tadi untuk mengambil data dumi dalam format JSON (Javascript Object Notation). Sebelum saya menjelaskan tahap-tahap pembuatan aplikasi tersebut. Saya akan menjelaskan terlebih dahulu konsep-konsep dasar yang perlu anda pahami.


Mengenal JSON

JSON (Javascript Object Notation) merupakan format pertukaran data. Sebuah objek JSON merupakan kumpulan dari pasangan key dan value yang diawali dengan tanda “{” dan diakhiri dengan tanda “}”. Berikut ini adalah contoh sebuah objek JSON yang akan anda gunakan dalam tutorial ini.

var json = {
    'title' : 'Facebook',
    'rating' : 5,
    'total_dl' : 10990998,
    'icon' : 'facebook'

Sebuah array dari JSON merupakan serangkaian object JSON. Di bawah ini merupakan contoh array dari beberapa objek JSON di atas.

var json = [
    {'title' : 'Facebook',
    'rating' : 5,
    'total_dl' : 10990998,
    'icon' : 'facebook'},
    {'title' : 'Twitter',
    'rating' : 5,
    'total_dl' : 12343487,
    'icon' : 'twitter'},
    {'title' : 'Whatsapp',
    'rating' : 4,
    'total_dl' : 5635989,
    'icon' : 'whatsapp'}


Konsep Dasar


As I’ve stated before, we’ll create simple android application that show a list of top application. We use android list view to display the data. The list data come from MySQL server. So this application will have a http connection feature. Communication between server and android application is use JSON based data. In this tutorial we’ll use apached server in localhost that running AVD (Android Virtual Device).
The data that are fetched from server including applicatoin title, rating, total download and icon name. For simplicity of the tutorial, we’ll use an icons that are saved in /res/drawable folder for applications. This will reduce the complexity of downloading the icons from server.
Below is final screenshot of our tutorial

Populate Listview Using JSON

Populate Listview Using JSON

Server Side Task

1. Creating A Database in MySQL

Before creating a database in MySQL, make sure you’ve create a user to login into the MySQL server. In this tutorial, we use a default user root with blank password.
We’ll create a simple database scheme for the application. The database will be used to store application data. Execute the following sql to create such database.


Now, we have a database apps. We need to create a table in database apps that will store the application title, total download, rating and icon file name. Execute the following sql to create the table.

CREATE TABLE `app_data`(
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `app_title` varchar(150) NOT NULL,
    `total_dl` int unsigned NOT NULL default 0,
    `rating` int unsigned NOT NULL default 0,
    `icon` varchar(120) NOT NULL,
    primary key(`id`)

Now, we must put sample application data into the table. Execute the following sql to create sample application data.

INSERT INTO app_data VALUES(null, "Facebook", 20099099, 5, "facebook");
INSERT INTO app_data VALUES(null, "Twitter", 11342099, 5, "twitter");
INSERT INTO app_data VALUES(null, "Google +", 10123023, 4, "google");
INSERT INTO app_data VALUES(null, "Whatsapp", 10033876, 3, "whatsapp");
INSERT INTO app_data VALUES(null, "Youtube", 10023444, 4, "youtube");
INSERT INTO app_data VALUES(null, "Line", 9023434, 5, "line");
INSERT INTO app_data VALUES(null, "Kakao Talk", 8247836, 3, "kakao");
INSERT INTO app_data VALUES(null, "Linked In", 784736, 4, "linkedin");
INSERT INTO app_data VALUES(null, "Angry Bird", 693847, 2, "angrybird");
INSERT INTO app_data VALUES(null, "Skype", 528374, 3, "skype");


2. Create PHP Script to Fetch the Data

The database now ready, we need create a PHP script that connect to MySQL server and get the application data. Then the application data is converted into JSON string that will send into client (android application). This script use user root with blank password to login into MySQL server, you can change them to meet your server account.

$host = "localhost"; // host of MySQL server
$user = "root"; // MySQL user
$pwd = ""; // MySQL user's password
$db = "apps"; // database name

// Create connection
$con = mysqli_connect($host, $user, $pwd, $db);

// Check connection
if(mysqli_connect_errno($con)) {
    die("Failed to connect to MySQL: " . mysqli_connect_error());

// query the application data
$sql = "SELECT * FROM app_data ORDER By id";
$result = mysqli_query($con, $sql);

// an array to save the application data
$rows = array();

// iterate to query result and add every rows into array
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $rows[] = $row; 

// close the database connection

// echo the application data in json format
echo json_encode($rows);

Save above source as PHP file and give a name apps.php and you must place it in apache document root, for example http://localhost/test/apps.php.

3. Testing PHP Script in A Browser

We must test the PHP script we’ve created to make sure the script works well. Open your browser and goto http://localhost/test/apps.php (the url may depend on your environtment). If the script works well, you’ll see the following result in your browser.

JSON In Browser

JSON In Browser

Client Side Task (Android Application)

After the server side is ready and tested, now we’ll create an android application that will create a http request to PHP script that we’ve created previously to get all application data and display the data in a list view. In order to display all of data like application title, total downloads, rating and icon, we must create a custom adapter for the list view.
Open your Eclipse IDE and create new android application. Use JsonDemo as the project’s name.
Do the following step by step to create the application.

1. Create Main Layout File

This file is an xml layout file that defines the main user interface. It contains a list view that will display all of the application data.
Save below source code as /res/layout/activity_main.xml.

<LinearLayout xmlns:android="" xmlns:tools="" android:layout_width="match_parent" android:layout_height="match_parent" tools:context=".MainActivity" >

    <ListView android:id="@android:id/list" android:layout_width="match_parent" android:layout_height="wrap_content" >



2. Create Custom List View Layout File

This file is an xml layout file. It responsibles to define a custom view the list view that is contained in the main layout. This file will be used in the custom adapter that will be defined later. The layout contains 3 TextView and 1 ImageView. The image view is used to display the application icon. It is placed on the left. First text view is used to display application’s title. It is placed on the right of the icon. Second text is used to display application’s rating. It is placed on the bottom application’s title. The last text view is used to display the application’ total download.
Save below source code as /res/layout/app_custom_list.xml.

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" >

    <ImageView android:id="@+id/appIcon" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignParentLeft="true" android:layout_alignParentTop="true" android:layout_marginLeft="3dp" android:layout_marginTop="3dp" android:src="@drawable/facebook" />
    <TextView android:id="@+id/titleTxt" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignTop="@id/appIcon" android:layout_toRightOf="@id/appIcon" android:layout_marginLeft="3dp" android:text="Application Title" android:textSize="22dp"/>

    <LinearLayout android:id="@+id/ratingCntr" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_toRightOf="@id/appIcon" android:layout_below="@id/titleTxt" android:layout_marginLeft="5dp" >

    <TextView android:id="@+id/dlTxt" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_below="@id/titleTxt" android:layout_alignParentRight="true" android:layout_marginRight="3dp" android:text="0 dl" />    



3. Create Application Value Object File

This file is a java object class that represents an application value object. It has several properties that are mapped from the app_data table from our database. It has properties such as application name, total downloads, rating and icon. Below is definition of the file. Below is the source code that define application value object. Save it as /src/com/sj/jsondemo/


public class Application {
    private String title;
    private long totalDl;
    private int rating;
    private String icon;
    public String getTitle() {
        return title;
    public void setTitle(String title) {
        this.title = title;
    public long getTotalDl() {
        return totalDl;
    public void setTotalDl(long totalDl) {
        this.totalDl = totalDl;
    public int getRating() {
        return rating;
    public void setRating(int rating) {
        this.rating = rating;
    public String getIcon() {
        return icon;
    public void setIcon(String icon) {
        this.icon = icon;


4. Create Custom Adapter File

This file is list view’s custom adapter that will inflate /res/layout/app_custom_list.xml layout file. It’s constructor is defined with 1 parameter. The parameter is an array list of application value object. The adapter will inflates the layout for each application object in the array list and change the application title, rating, total downloads and icons dynamically.
Below is source code that defines the custom adapter. Save it as /src/com/sj/jsondemo/


import java.text.NumberFormat;
import java.util.List;

import android.content.Context;
import android.content.res.Resources;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.ImageView;
import android.widget.LinearLayout;
import android.widget.TextView;

public class ApplicationAdapter extends ArrayAdapter<Application>{
    private List<Application> items;
    public ApplicationAdapter(Context context, List<Application> items) {
        super(context, R.layout.app_custom_list, items);
        this.items = items;
    public int getCount() {
        return items.size();
    public View getView(int position, View convertView, ViewGroup parent) {
        View v = convertView;
        if(v == null) {
            LayoutInflater li = LayoutInflater.from(getContext());
            v = li.inflate(R.layout.app_custom_list, null);            
        Application app = items.get(position);
        if(app != null) {
            ImageView icon = (ImageView)v.findViewById(;
            TextView titleText = (TextView)v.findViewById(;
            LinearLayout ratingCntr = (LinearLayout)v.findViewById(;
            TextView dlText = (TextView)v.findViewById(;
            if(icon != null) {
                Resources res = getContext().getResources();
                String sIcon = "" + app.getIcon();
                icon.setImageDrawable(res.getDrawable(res.getIdentifier(sIcon, null, null)));
            if(titleText != null) titleText.setText(app.getTitle());
            if(dlText != null) {
                NumberFormat nf = NumberFormat.getNumberInstance();
                dlText.setText(nf.format(app.getTotalDl())+" dl");            
            if(ratingCntr != null && ratingCntr.getChildCount() == 0) {        
                 * max rating: 5
                for(int i=1; i<=5; i++) {
                    ImageView iv = new ImageView(getContext());
                    if(i <= app.getRating()) {
                    else {                
        return v;


5. Create An Async Task File To Get The Data From Server

This file responsibles to get all the application data by creating a http request to PHP script. It is defined by subclassed AsyncTask object class. This means the task will be execute in the background, so it never block the user interface. When the task is executed, a progress bar will be shown to make the interface user friendly. This file communicate to activity via a listener that we’ll create later. This communication is important because we need to notify the activity when all the data has been fetched.
Below is the source code that define the async task. Save it as /src/com/sj/jsondemo/


import java.util.ArrayList;
import java.util.List;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.os.AsyncTask;

public class FetchDataTask extends AsyncTask<String, Void, String>{
    private final FetchDataListener listener;
    private String msg;
    public FetchDataTask(FetchDataListener listener) {
        this.listener = listener;
    protected String doInBackground(String... params) {
        if(params == null) return null;
        // get url from params
        String url = params[0];
        try {
            // create http connection
            HttpClient client = new DefaultHttpClient();
            HttpGet httpget = new HttpGet(url);
            // connect
            HttpResponse response = client.execute(httpget);
            // get response
            HttpEntity entity = response.getEntity();
            if(entity == null) {
                msg = "No response from server";
                return null;        
            // get response content and convert it to json string
            InputStream is = entity.getContent();
            return streamToString(is);
        catch(IOException e){
            msg = "No Network Connection";
        return null;
    protected void onPostExecute(String sJson) {
        if(sJson == null) {
            if(listener != null) listener.onFetchFailure(msg);
        try {
            // convert json string to json array
            JSONArray aJson = new JSONArray(sJson);
            // create apps list
            List<Application> apps = new ArrayList<Application>();
            for(int i=0; i<aJson.length(); i++) {
                JSONObject json = aJson.getJSONObject(i);
                Application app = new Application();
                // add the app to apps list
            //notify the activity that fetch data has been complete
            if(listener != null) listener.onFetchComplete(apps);
        } catch (JSONException e) {
            msg = "Invalid response";
            if(listener != null) listener.onFetchFailure(msg);
     * This function will convert response stream into json string
     * @param is respons string
     * @return json string
     * @throws IOException
    public String streamToString(final InputStream is) throws IOException{
        BufferedReader reader = new BufferedReader(new InputStreamReader(is));
        StringBuilder sb = new StringBuilder(); 
        String line = null;
        try {
            while ((line = reader.readLine()) != null) {
                sb.append(line + "\n");
        catch (IOException e) {
            throw e;
        finally {           
            try {
            catch (IOException e) {
                throw e;
        return sb.toString();


6. Create Fetch Data Listener

This file is simple Java interface. It has two method’s declarations, onFetchCompleted and onFetchFailure. onFetchCompleted method will be called from FetchDataTask when data fetching is complete. onFetchFailure will be called from FetchDataTask when data fetching is failure.
Save below source code as /src/com/sj/jsondemo/


import java.util.List;

public interface FetchDataListener {
    public void onFetchComplete(List<Application> data);
    public void onFetchFailure(String msg);


7. Create Activity

This is main activity that will inflate /res/layout/activity_main.xml to create main user interface. When the activity is created, it’s create new FetchDataTask instance and call execute method of the instance. The task runs and start to get all application data in the background. Before execute the task, the activity show progress bar to the user to indicate that the system is loading the data.
This activity also implements FecthDataListener and overriden onFetchCompleted and onFetchFailure. onFetchFailure is used by activity to hide the progress bar and display failure message to user. onFetchCompleted is used by activity to hide the progress bar and display the application data into the list.
The apache server that we used is localhost which same as host that run AVD(Android Virtual Device), so to refers this host from AVD, we must use IP address For more information please visit Android Developer Site.
Save below source code as /src/com/sj/jsondemo/


import java.util.List;

import android.os.Bundle;
import android.widget.Toast;

public class MainActivity extends ListActivity implements FetchDataListener{
    private ProgressDialog dialog;
    protected void onCreate(Bundle savedInstanceState) {

    private void initView() {
        // show progress dialog
        dialog =, "", "Loading...");
        String url = "";
        FetchDataTask task = new FetchDataTask(this);
    public void onFetchComplete(List<Application> data) {
        // dismiss the progress dialog
        if(dialog != null)  dialog.dismiss();
        // create new adapter
        ApplicationAdapter adapter = new ApplicationAdapter(this, data);
        // set the adapter to list

    public void onFetchFailure(String msg) {
        // dismiss the progress dialog
        if(dialog != null)  dialog.dismiss();
        // show failure message
        Toast.makeText(this, msg, Toast.LENGTH_LONG).show();        

8. Create Manifest File

The application make a http request, so the the manifest file must contains network usage permission. Below is complete manifest file. Save it as Android.Manifest.

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="" package="" 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="" android:label="@string/app_name" >
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />


Filter associative array based on array key in PHP

Posted by:

PHP has so many built in function to work with array. In this tutorial I would present to you how to filter an associative array based on array keys. For example, you have an associative array [cci]$groups[/cci]:

$groups = array(
    'a' => 'book',
    'b' => 'pencil',
    'c' => 'pen'

And array of keys:

$keys = array('a', 'b');

Now, how to get sub array of [cci]$groups[/cci] which key contained in [cci]$keys[/cci]? Our goal is to create new associative array like this:

$news = array(
    'a' => 'book',
    'b' => 'pencil'

Every keys in array [cci]$news[/cci] contained in [cci]$keys[/cci]. Here the way to do that:

$news = array();

foreach($groups as $k=>$v){
    if(in_array($k, $keys)){
        $news[$k]  = $v;

Passing PHP variable to javascript file

Posted by:

PHP and Javascript work on different sides. PHP work on server side while Javascript work at client side. If your javascript file is separate from html file, you cannot refers to PHP variables. But there is a tricky way how to passing PHP variable into javascript file.

Write you Javascript file as PHP

	header("content-type: application/x-javascript");
	$base_url = $_REQUEST['base_url'];
document.ready = function(e){
alert('<?php echo $base_url?>');

In the above code, you have read PHP variable in javascript file. Save above code as [cci]js.php[/cci]

Include javascript file in html head

This is simple [cci]index.php[/cci] file:

	$base_url = $_SERVER['HTTP_HOST'];
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
<html xmlns="">
<script type="text/javascript" src="js.php?base_url=<?php echo $base_url?>"></script>