How to get table values from a MySQL database and show it in Android as Tables.?

Here we are accessing a mysql database and get the table values and show it in the Android side as tables.

These are some tutorials that I have posted on connection with php and getting tablevalues.
1. Android phpmySQL connection redone.

2. Simplest Lazy Loading ListView Example in Android with data populated from a MySQL database using php.

Here we have 3 classes
1. GetDataFromDB.java – Gets value from DB in the form of JSON.
2. Users.java _ Class which holds the one row table object.
3. MainActivity.java _ Which implements these classes and show the tables.

MainActivity.java

package com.coderzheaven.gettablefromphp;

import java.util.ArrayList;
import java.util.Iterator;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import android.app.Activity;
import android.   graphics.   Color;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.FrameLayout;
import android.widget.LinearLayout;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TableRow.LayoutParams;
import android.widget.TextView;

public class MainActivity extends Activity {

	String data = "";
	TableLayout tl;
	TableRow tr;
	TextView label;

	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);

		tl = (TableLayout) findViewById(R.id.maintable);

		final GetDataFromDB getdb = new GetDataFromDB();
		new Thread(new Runnable() {
			public void run() {
				data = getdb.getDataFromDB();
				System.out.println(data);
				
				runOnUiThread(new Runnable() {
					
					@Override
					public void run() {
						ArrayList<Users> users = parseJSON(data);
						addData(users);						
					}
				});
				
			}
		}).start();
	}

	public ArrayList<Users> parseJSON(String result) {
		ArrayList<Users> users = new ArrayList<Users>();
		try {
			JSONArray jArray = new JSONArray(result);
			for (int i = 0; i < jArray.length(); i++) {
				JSONObject json_data = jArray.getJSONObject(i);
				Users user = new Users();
				user.setId(json_data.getInt("id"));
				user.setName(json_data.getString("name"));
				user.setPlace(json_data.getString("place"));
				users.add(user);
			}
		} catch (JSONException e) {
			Log.e("log_tag", "Error parsing data " + e.toString());  
		}
		return users;
	}

	void addHeader(){
		/** Create a TableRow dynamically **/
		tr = new TableRow(this);

		/** Creating a TextView to add to the row **/
		label = new TextView(this);
		label.setText("User");
		label.setLayoutParams(new LayoutParams(LayoutParams.WRAP_CONTENT,
				LayoutParams.WRAP_CONTENT));
		label.setPadding(5, 5, 5, 5);
		label.setBackgroundColor(Color.RED);
		LinearLayout Ll = new LinearLayout(this);
		LinearLayout.LayoutParams params = new LinearLayout.LayoutParams(LayoutParams.FILL_PARENT,
				LayoutParams.WRAP_CONTENT);
		params.setMargins(5, 5, 5, 5);
		//Ll.setPadding(10, 5, 5, 5);
		Ll.addView(label,params);
		tr.addView((View)Ll); // Adding textView to tablerow.

		/** Creating Qty Button **/
		TextView place = new TextView(this);
		place.setText("Place");
		place.setLayoutParams(new LayoutParams(LayoutParams.WRAP_CONTENT,
				LayoutParams.WRAP_CONTENT));
		place.setPadding(5, 5, 5, 5);
		place.setBackgroundColor(Color.RED);
		Ll = new LinearLayout(this);
		params = new LinearLayout.LayoutParams(LayoutParams.FILL_PARENT,
				LayoutParams.WRAP_CONTENT);
		params.setMargins(0, 5, 5, 5);
		//Ll.setPadding(10, 5, 5, 5);
		Ll.addView(place,params);
		tr.addView((View)Ll); // Adding textview to tablerow.

		 // Add the TableRow to the TableLayout
        tl.addView(tr, new TableLayout.LayoutParams(
                LayoutParams.FILL_PARENT,
                LayoutParams.WRAP_CONTENT));
	}
	
	@SuppressWarnings({ "rawtypes" })
	public void addData(ArrayList<Users> users) {

		addHeader();
		
		for (Iterator i = users.iterator(); i.hasNext();) {

			Users p = (Users) i.next();

			/** Create a TableRow dynamically **/
			tr = new TableRow(this);

			/** Creating a TextView to add to the row **/
			label = new TextView(this);
			label.setText(p.getName());
			label.setId(p.getId());
			label.setLayoutParams(new LayoutParams(LayoutParams.WRAP_CONTENT,
					LayoutParams.WRAP_CONTENT));
			label.setPadding(5, 5, 5, 5);
			label.setBackgroundColor(Color.GRAY);
			LinearLayout Ll = new LinearLayout(this);
			LinearLayout.LayoutParams params = new LinearLayout.LayoutParams(LayoutParams.FILL_PARENT,
					LayoutParams.WRAP_CONTENT);
			params.setMargins(5, 2, 2, 2);
			//Ll.setPadding(10, 5, 5, 5);
			Ll.addView(label,params);
			tr.addView((View)Ll); // Adding textView to tablerow.

			/** Creating Qty Button **/
			TextView place = new TextView(this);
			place.setText(p.getPlace());
			place.setLayoutParams(new LayoutParams(LayoutParams.WRAP_CONTENT,
					LayoutParams.WRAP_CONTENT));
			place.setPadding(5, 5, 5, 5);
			place.setBackgroundColor(Color.GRAY);
			Ll = new LinearLayout(this);
			params = new LinearLayout.LayoutParams(LayoutParams.FILL_PARENT,
					LayoutParams.WRAP_CONTENT);
			params.setMargins(0, 2, 2, 2);
			//Ll.setPadding(10, 5, 5, 5);
			Ll.addView(place,params);
			tr.addView((View)Ll); // Adding textview to tablerow.

			 // Add the TableRow to the TableLayout
            tl.addView(tr, new TableLayout.LayoutParams(
                    LayoutParams.FILL_PARENT,
                    LayoutParams.WRAP_CONTENT));
		}
	}
}

GetDataFromDB.java

package com.coderzheaven.gettablefromphp;

import org.apache.http.client.HttpClient;
import org.apache.http.client.ResponseHandler;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.BasicResponseHandler;
import org.apache.http.impl.client.DefaultHttpClient;

public class GetDataFromDB {

	public String getDataFromDB() {
		try {

			HttpPost httppost;
			HttpClient httpclient;
			httpclient = new DefaultHttpClient();
			httppost = new HttpPost(
					"http://10.0.2.2/test/GetUsers.php"); // change this to your URL.....
			ResponseHandler<String> responseHandler = new BasicResponseHandler();
			final String response = httpclient.execute(httppost,
					responseHandler);
			
			return response.trim();

		} catch (Exception e) {
			System.out.println("ERROR : " + e.getMessage());
			return "error";
		}
	}
}

Users.java

package com.coderzheaven.gettablefromphp;

public class Users {

	int id;
	String name;
	String place;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPlace() {
		return place;
	}
	public void setPlace(String place) {
		this.place = place;
	}
	
}

The activity_main.xml

<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" >

    <ScrollView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:fillViewport="true"
    android:    scrollbars  =  "none"
    android:layout_below="@+id/textView1">
        <TableLayout
            android:layout_width="wrap_content"
            android:layout_height="0dp"
            android:stretchColumns="1,0,0"
            android:id="@+id/maintable" >
        </TableLayout>
    </ScrollView>
    
</RelativeLayout>

The server Side php.

GetUsers.php

<?php

	mysql_connect("localhost","root",""); // host, username, password...
	mysql_select_db("testdb"); // db name...
	 
	$q=mysql_query("SELECT * FROM users");
	while($row=mysql_fetch_assoc($q))
			$json_output[]=$row;
	 
	print(json_encode($json_output));
	 
	mysql_close();
	
?>

Table Php Android

Table Php Android

Download the complete source code from here.

Download
.

Join the Forum discussion on this post

35 thoughts on “How to get table values from a MySQL database and show it in Android as Tables.?

  1. steve

    thanks for your efforts ,

    but actually it doesn’t work there is a problem says,

    error parsing data org.json.JSONException: value error of type java.lang.string cannot be converted to JSONArray

    here is the out put of GetUsers.php

    [{"id":"1","name":"lolo","place":"usa"},{"id":"2","name":"tala","place":"nyc"}]

    would you please help me to fix it ,,

    thanks in advance

    Reply
    1. James Post author

      Hey Steve :- Some extra characters are coming along with your output from the PHP side, so it cannnot be parsed into JSON array.

      Reply
  2. derek

    getting this error

    The method run() of type new Runnable(){} must override a superclass method MainActivity.java /com.coderzheaven.gettablefromphp.MainActivity/src/com/coderzheaven/gettablefromphp line 45 Java Problem

    Reply
  3. James

    hanks for your efforts ,

    but actually it doesn’t work there is a problem says,

    error parsing data org.json.JSONException: value error of type java.lang.string cannot be converted to JSONArray

    here is the out put of GetUsers.php

    [{"id":"1","name":"James","place":"India"}]
    would you please help me to fix it ,,

    Reply
  4. Kennedy Abad

    hello. thanks for the tutorial, the codes works only when i run it on the emulator, it showed me the result that i expected, but every time i install the .apk file on a real android device the application doesn’t retrieve any data.

    can you please help me with this?

    i guest, it has something to do when my

    httppost = new HttpPost(
    “http://10.0.2.2/myrecords/RetrieveUsers.php”);

    i started writing android apps last month so i merely need your help…

    thanks a lot. . .

    Reply
  5. Pingback: What are webservices? How we can use in Android?

  6. malik

    Error parsing data org.json.JSONException: Value error of type java.lang.String cannot be converted to JSONArray

    Reply
    1. James Post author

      You have some error in the JSON. Check whether you are not adding anything(such as a character or string) to the JSON String from the server. You should only pass JSON String from the server so that the JSON Parser can understand the string.

      Reply
  7. udhayabanu

    sir,
    I have an android application wherein i extract record from mysql based on user input as listview in android. In the later stage when i click on one of the list it should direct me to another activity. While running this application in emulator i am not getting the result. Can u please help. I am trying this for past 2 weeks.

    My main activity
    package com.example.pharmacylist;

    import java.util.ArrayList;
    import java.util.HashMap;
    import org.apache.http.NameValuePair;
    import org.apache.http.message.BasicNameValuePair;
    import org.json.JSONArray;
    import org.json.JSONException;
    import org.json.JSONObject;
    import android.app.ListActivity;
    import android.content.Intent;
    import android.os.Bundle;
    import android.os.StrictMode;

    import com.example.pharmacylist.R;
    import com.example.pharmacylist.SinglePlace;
    import com.example.pharmacylist.JSONParser;
    import android.util.Log;
    import android.view.View;
    import android.widget.AdapterView;
    import android.widget.Button;
    import android.widget.EditText;
    import android.widget.TextView;
    //import android.widget.ListAdapter;
    import android.widget.ListView;
    import android.widget.SimpleAdapter;
    import android.widget.AdapterView.OnItemClickListener;
    //import android.widget.Toast;
    //import android.widget.AdapterView.OnClickListener;

    //import android.view.View.OnClickListener;
    public class MainActivity extends ListActivity {
    ArrayList<HashMap> pharmacy;
    EditText byear; // To take birthyear as input from user
    Button submit;
    //TextView tv;
    ListView tv;

    // TextView to show the result of MySQL query
    JSONArray returnString; // to store the result of MySQL query after decoding JSON
    //String returnString;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
    StrictMode.setThreadPolicy(new StrictMode.ThreadPolicy.Builder()
    .detectDiskReads().detectDiskWrites().detectNetwork() // StrictMode is most commonly used to catch accidental disk or network access on the application’s main thread
    .penaltyLog().build());
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    pharmacy = new ArrayList<HashMap>();
    byear = (EditText) findViewById(R.id.editText1);
    submit = (Button) findViewById(R.id.submitbutton);
    tv=getListView();
    tv = (ListView) findViewById(android.R.id.list);
    //tv = (TextView) findViewById(R.id.showresult);
    // define the action when user clicks on submit button
    submit.setOnClickListener(new View.OnClickListener(){
    public void onClick(View v) {
    // declare parameters that are passed to PHP script i.e. the name “birthyear” and its value submitted by user
    ArrayList postParameters = new ArrayList();

    // define the parameter
    postParameters.add(new BasicNameValuePair(“search”,
    byear.getText().toString()));
    String response = null;

    // call executeHttpPost method passing necessary parameters
    try {
    response = JSONParser.executeHttpPost(
    //”http://129.107.187.135/CSE5324/jsonscript.php”, // your ip address if using localhost server
    “http://www.sablabs.com/Calendar/pharmacy1.php”, // in case of a remote server
    postParameters);

    // store the result returned by PHP script that runs MySQL query
    String result = response.toString();
    // String[] result = response;
    //parse json data
    try{

    returnString =null;
    JSONArray jArray = new JSONArray(result);
    // JSONObject jsonObj= new JSONObject(new String(result));

    // JSONArray json_data = jArray.getJSONArray(“json_data”);
    //returnString = response.getJSONArray(“pharmacy”);
    for(int i=0;i<jArray.length();i++){

    JSONObject json_data = jArray.getJSONObject(i);
    //Log.i("log_tag","search: "+json_data.getString("search")+
    // ",name: "+json_data.getString("name"));
    //Get an output to the screen

    String name = "\n"+ json_data.getString("name");

    HashMap map = new HashMap();
    //map.put(“name”,json_data.toString(“name”));
    // map.put(“name”,returnString);
    map.put(“name”,name);
    pharmacy.add(map);

    // name.append((“Name : ” +name);)
    //returnString += “\n” +json_data.getString(“name”);
    }
    }

    catch(JSONException e){
    Log.e(“log_tag”, “Error parsing data “+e.toString());
    }

    try{
    //tv.setFilterText(pharmacy);
    //ArrayAdapter adapter=new ArrayAdapter(this, android.R.layout.simple_list_item_1, );
    //listView.setAdapter(adapter);
    SimpleAdapter adapter = new SimpleAdapter(
    MainActivity.this, pharmacy,
    android.R.layout.simple_list_item_1, new String[] { “returnString”},
    new int[] {android. R.id.list});

    // SimpleAdapter adapter = new SimpleAdapter(
    // MainActivity.this,android.R.layout.simple_list_item_1, R.id.list,pharmacy);
    tv.setAdapter(adapter);

    adapter.notifyDataSetChanged();

    //((TextView) tv).setText(returnString);
    //tv.setText(returnString);

    }
    catch(Exception e){
    Log.e(“log_tag”,”Error in Display!” + e.toString());;
    }
    }
    catch (Exception e) {
    Log.e(“log_tag”,”Error in http connection!!” + e.toString());
    }
    }
    });
    // tv.setAdapter(adapter);
    //tv.setOnClickListener(new View.OnClickListener(){

    tv.setOnItemClickListener(new OnItemClickListener() {

    //@Override
    public void onItemClick(AdapterView parent, View view,
    int position, long id) {
    //@Override
    // public void onClick( View view) {

    // getting values from selected ListItem
    String reference = ((TextView) view.findViewById(android.R.id.list)).getText().toString();
    Bundle b = new Bundle();
    //b.putString(“result.name”, reference);
    b.putString(“tv.getItemAtPosition(position)”, reference);
    // b.putString(“result.address2″, reference);
    //b.putString(“result.address3″, reference);
    //b.putString(“result.telephone”, reference);
    // Starting new intent
    Intent in = new Intent(getApplicationContext(),
    SinglePlace.class);
    in.putExtras(b);
    // Sending place refrence id to single place activity
    // place refrence id used to get “Place full details”
    startActivity(in);
    }

    });
    }
    }

    My http request
    package com.example.pharmacylist;

    import java.io.BufferedReader;

    import java.io.IOException;

    import java.io.InputStreamReader;

    import java.net.URI;

    import java.util.ArrayList;

    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.HttpGet;

    import org.apache.http.client.methods.HttpPost;

    import org.apache.http.conn.params.ConnManagerParams;

    import org.apache.http.impl.client.DefaultHttpClient;

    import org.apache.http.params.HttpConnectionParams;

    import org.apache.http.params.HttpParams;

    import android.util.Log;

    public class JSONParser {

    /** The time it takes for our client to timeout */

    public static final int HTTP_TIMEOUT = 30 * 1500; // milliseconds

    /** Single instance of our HttpClient */

    private static HttpClient mHttpClient;

    /**

    * Get our single instance of our HttpClient object.

    *

    * @return an HttpClient object with connection parameters set

    */

    private static HttpClient getHttpClient() {

    if (mHttpClient == null) {

    mHttpClient = new DefaultHttpClient();

    final HttpParams params = mHttpClient.getParams();

    HttpConnectionParams.setConnectionTimeout(params, HTTP_TIMEOUT);

    HttpConnectionParams.setSoTimeout(params, HTTP_TIMEOUT);

    ConnManagerParams.setTimeout(params, HTTP_TIMEOUT);

    }

    return mHttpClient;

    }

    /**

    * Performs an HTTP Post request to the specified url with the specified

    * parameters.

    *

    * @param url

    * The web address to post the request to

    * @param postParameters

    * The parameters to send via the request

    * @return The result of the request

    * @throws Exception

    */

    public static String executeHttpPost(String url,

    ArrayList postParameters) throws Exception {

    BufferedReader in = null;

    try {

    HttpClient client = getHttpClient();

    HttpPost request = new HttpPost(url);

    UrlEncodedFormEntity formEntity = new UrlEncodedFormEntity(

    postParameters);

    request.setEntity(formEntity);

    HttpResponse response = client.execute(request);

    in = new BufferedReader(new InputStreamReader(response.getEntity()

    .getContent()));

    StringBuffer sb = new StringBuffer(“”);

    String line = “”;

    String NL = System.getProperty(“line.separator”);

    while ((line = in.readLine()) != null) {

    sb.append(line + NL);

    }

    in.close();

    String result = sb.toString();

    return result;

    } finally {

    if (in != null) {

    try {

    in.close();

    } catch (IOException e) {

    Log.e(“log_tag”, “Error converting result “+e.toString());

    e.printStackTrace();

    }

    }

    }

    }

    /**

    * Performs an HTTP GET request to the specified url.

    *

    * @param url

    * The web address to post the request to

    * @return The result of the request

    * @throws Exception

    */

    public static String executeHttpGet(String url) throws Exception {

    BufferedReader in = null;

    try {

    HttpClient client = getHttpClient();

    HttpGet request = new HttpGet();

    request.setURI(new URI(url));

    HttpResponse response = client.execute(request);

    in = new BufferedReader(new InputStreamReader(response.getEntity()

    .getContent()));

    StringBuffer sb = new StringBuffer(“”);

    String line = “”;

    String NL = System.getProperty(“line.separator”);

    while ((line = in.readLine()) != null) {

    sb.append(line + NL);

    }

    in.close();

    String result = sb.toString();

    return result;

    } finally {

    if (in != null) {

    try {

    in.close();

    } catch (IOException e) {

    Log.e(“log_tag”, “Error converting result “+e.toString());

    e.printStackTrace();

    }

    }

    }

    }

    }

    My activity_main

    Can u please help me get the result in listview.

    Reply
  8. fa,eee

    Hello.why is it that the eclipse emulator not displaying the values? table is empty even though data is inserted through mysql workbench.

    Reply
  9. Pingback: Retrieve database value into Textview in androidCopyQuery CopyQuery | Question & Answer Tool for your Technical Queries,CopyQuery, ejjuit, query, copyquery, copyquery.com, android doubt, ios question, sql query, sqlite query, nodejsquery, dns query, u

  10. john

    hello! Thank you for this tutorial. I tried it and it worked! However, is it possible to display the database value in textviews and not in table format? I have a database which has a table “marketing”. The first column is “product” and the second column is “price”. I wanted to retrieve the value of product in textview1 which is above textview2. textview2 will display the table value for price. Both textviews will be at the center of the screen. Is this even possible?

    Reply
  11. John

    Hey this tutorial is really helpful!
    I have a question for you, How can the data were renewed within this time?
    It will help me really say one way.
    Sorry for my English.

    Reply
  12. Ronald

    Hi! Thank you for this tutorial. There is no error in parsing data, the screen display the rows with background color gray but it’s not displaying any texts.. Sir can you please help me how to solve this problem… Thank you in advance..

    Reply
  13. james

    I try too parsing session data from this code. So not all data from database printed at table.
    I use login and from login I keep session login
    ex: I want printed data with id=2,
    so at my php file

    Reply
  14. Naresh

    James

    Thanks for the tutorial. I am facing an issue here. The following exception is through from the code

    org.apache.http.NoHttpResponseException: the target server failed to respond

    I used the same code for httpConnection. Please help me out in proceeding further.

    Thanks

    Reply
  15. Gwendoline Stacy

    Hi! Thanks for the tutorial.. But the code shows an error in converting java.lang.String to JSONArray. I am new to android and have no prior knowledge of the problem. Could you please explain how to solve this? Thank you..

    Reply
    1. James Post author

      Some unidentified characters may be there in the result or some JSON format error. Please try to print out the exception and see.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>