Android load mysql data in listview

When you create an Android application that reads a table from a Mysql database, it is common to display the table in a ListView. In this Android tip, I am going to show you how to load Mysql data in an Android ListView.

To have a workable example application that loads MySQL data in a ListView, first you need to create a database called dbtest and a table called tblproduct that has three fields: pid, pname, and uprice. The pid field is an auto-increment field. You also need to create an account with "test" user name and "test" password. The account will be used in PHP code to connect to the dbtest database. Then run the sql statements below to insert some sample data.



INSERT INTO `tblproduct`(`pname`, `uprice`) VALUES ('iPad Mini2','495')
INSERT INTO `tblproduct`(`pname`, `uprice`) VALUES ('iPad Air','578')
INSERT INTO `tblproduct`(`pname`, `uprice`) VALUES ('Samsung Galaxy Note3','577')
INSERT INTO `tblproduct`(`pname`, `uprice`) VALUES ('Samsung Galaxy S5','570')



After adding the sample data, create two PHP files. The name of the first file should be getproducts.php and the second file is connection.php. The getproducts.php file will be executed to get all products and unit prices from the tblproduct table. The data are stored in JSON array.

getproducts.php file

<?php
include("connection.php");
$mysqli->query("SET NAMES 'utf8'");
$sql="SELECT pname, uprice FROM tblproduct";
$result=$mysqli->query($sql);
while($e=mysqli_fetch_assoc($result)){
     $output[]=$e;
}

print(json_encode($output));
$mysqli->close();
?>

connection.php file

<?php
$DB_USER='test';        
$DB_PASS='test';      
$DB_HOST='localhost';    
$DB_NAME='dbtest';
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
/* check connection */
if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}


?>


These two files must be saved in the root folder of your web server. In my case, they are in D:\wamp\www.

Next, you will create a new Android project. Then modify the activity_main.xml file to add a ListView as shown below. The two EditTexts allow the user to input user name, and password. The ListView will display names and unit prices of products.

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
   xmlns:tools="http://schemas.android.com/tools"
   android:id="@+id/container"
   android:layout_width="match_parent"
   android:layout_height="match_parent"
   android:orientation="vertical"
   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/product_list"
     android:layout_width="fill_parent"
     android:layout_height="wrap_content"
      />

  
</LinearLayout>



Each item or row of the ListView contains two parts: product name and unit prices. So you need to create a separate xml file to be a row template of the customized ListView. The name of the file is list_item.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:padding="5dp" >
  
<TextView
     android:id="@+id/pro_name"
     android:layout_width="wrap_content"
     android:layout_height="match_parent"
     android:layout_marginRight="10dp"
     android:textSize="20sp"
       />

<TextView
     android:id="@+id/pro_uprice"
     android:layout_width="wrap_content"
     android:layout_height="match_parent"
     android:textSize="20sp"
     android:layout_alignParentRight="true"
      />

  
</RelativeLayout>


Then in the src directory, create a class called CustomAdapter that extends the ArrayAdapter. The object of the CustomAdapter will be used to supply data to the ListView.

package com.example.andtip;
import java.util.ArrayList;
import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.TextView;

public class CustomAdapter extends ArrayAdapter<Product> {

int groupid;
ArrayList<Product> records;
Context context;

public CustomAdapter(Context context, int vg, int id, ArrayList<Product> records) {
super(context, vg, id, records);
this.context = context;
groupid = vg;
this.records = records;

}

public View getView(int position, View convertView, ViewGroup parent) {

LayoutInflater inflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
View itemView = inflater.inflate(groupid, parent, false);
TextView textName = (TextView) itemView.findViewById(R.id.pro_name);
textName.setText(records.get(position).getpName());
TextView textPrice = (TextView) itemView.findViewById(R.id.pro_uprice);
textPrice.setText(records.get(position).getuPrice() + "$");

return itemView;
}
}

You also create Product class. An instance of the Product class is used to store product information (product name and unit price). It has methods to set and get product information.


		public class Product {
  private String pName;
  private int uPrice;
  public void setpName(String pName){this.pName=pName;}
  public void setuPrice(int uPrice){this.uPrice=uPrice;}
  public String getpName(){return pName;}
  public int getuPrice(){return uPrice;}

}


In the MainActivity class, you write code as shown below. The background task to load MySQL data in to the ListView starts from the onStart method. The progress dialog is provided so that the user will wait while the data is loading. In the onPreExecute method, a ProgressDialog instance is created and the dialog is shown. In the doInBackground, you will write code to make a request to the web server and retrieve data back to show in the ListView. In the onPostExecute method the dialog will be closed and the ListView is notified of data source changed.

  package com.example.andtip;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.InputStream;
import java.util.ArrayList;
import android.app.Activity;
import android.app.ProgressDialog;
import android.os.Bundle;
import android.util.Log;
import android.widget.ListView;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
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.JSONObject;

import android.os.AsyncTask;

public class MainActivity extends Activity{
Activity context;
HttpPost httppost;
StringBuffer buffer;
HttpResponse response;
HttpClient httpclient;
ProgressDialog pd;
CustomerAdapter adapter;
ListView listProduct;
ArrayList<Product> records;
protected void onCreate(Bundle savedInstanceState) {
//TODO Auto-generated method stub
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
context=this;
records=new ArrayList<Product>();
listProduct=(ListView)findViewById(R.id.product_list);
adapter=new CustomerAdapter(context, R.layout.list_item,R.id.pro_name, records);
listProduct.setAdapter(adapter);
}
public void onStart(){
super.onStart();
//execute background task
BackTask bt=new BackTask();
bt.execute();

}

//background process to make a request to server and list product information
private class BackTask extends AsyncTask<Void,Void,Void>{
protected void onPreExecute(){
super.onPreExecute();
pd = new ProgressDialog(context);
pd.setTitle("Retrieving data");
pd.setMessage("Please wait.");
pd.setCancelable(true);
pd.setIndeterminate(true);
pd.show();

}

protected Void doInBackground(Void...params){

InputStream is=null;
String result="";
try{

httpclient=new DefaultHttpClient();
httppost= new HttpPost("http://10.0.2.2:8080/getproducts.php");
response=httpclient.execute(httppost);
HttpEntity entity = response.getEntity();
// Get our response as a String.
is = entity.getContent();

}catch(Exception e){

if(pd!=null)
pd.dismiss(); //close the dialog if error occurs
Log.e("ERROR", e.getMessage());

}

//convert response to string
try{
BufferedReader reader = new BufferedReader(new InputStreamReader(is,"utf-8"),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("ERROR", "Error converting result "+e.toString());

}

//parse json data
try{
// Remove unexpected characters that might be added to beginning of the string
result=result.substring(result.indexOf("["));
JSONArray jArray =new JSONArray(result);
for(int i=0;i<jArray.length();i++){
JSONObject json_data =jArray.getJSONObject(i);
Product p=new Product();
p.setpName(json_data.getString("pname"));
p.setuPrice(json_data.getInt("uprice"));

records.add(p);

}


}
catch(Exception e){
Log.e("ERROR", "Error pasting data "+e.toString());


}

return null;
}


protected void onPostExecute(Void result){

if(pd!=null) pd.dismiss(); //close dialog
Log.e("size", records.size() + "");
adapter.notifyDataSetChanged(); //notify the ListView to get new records

}

}
}

Before you run the example application, make sure to declare INTERNET permission in AndroidManifest file.

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


Android-load-mysql-data-in-listview

Posted by: Dara | post date: 07-18-2014 | Subject: Android Apps Development

write programming tip



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.