Android Tutorial For Remote Mysql data storing in a table Layout

This tutorial describes fetching remote data from MySql database using PHP and storing that in table layout .

This task done with 5 steps
1) Create mysql database
2) PHP script for fetching data from remote database and echo it as JSON Format
3)Create XML table layout
5) Create JSONPARSER class to parse JSON Data
4) Convert JSON encoded data and store it in JSON parsed array .Extract value from JSON parsed array and store it in table



 1)First Create Database and table Example  in mysql
      Example.sql

CREATE TABLE `Example` (

  `COURSE` varchar(10) COLLATE latin1_general_ci NOT NULL,

  `COURSE_NAME` varchar(25) COLLATE latin1_general_ci NOT NULL,

  `CR` varchar(10) COLLATE latin1_general_ci NOT NULL,

  `CT` varchar(10) COLLATE latin1_general_ci NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

2)Create PHP script for fetch data from database and echo it as JSON format  

     example.php

<?php

mysql_connect("localhost","MYSQL USERNAME","MYSQL PASSWORD");

mysql_select_db("DATABASE NAME");

$response = array();

$sql = mysql_query("SELECT *FROM Example") or die(mysql_error());

// check for empty result

if (mysql_num_rows($sql) > 0) {

    // looping through all results

    // products node

    $response["Assessments1"] = array();

    while ($row = mysql_fetch_array($sql)) {

        // temp user array

        $Assessments12 = array();

        $Assessments12["COURSE"] = $row["COURSE"];

        $Assessments12["COURSE_NAME"] = $row["COURSE_NAME"];

        $Assessments12["CR"] = $row["CR"];

        $Assessments12["CT"] = $row["CT"];

        

        // push single product into final response array

        array_push($response["Assessments1"], $Assessments12);

    }

    // success

    $response["success"] = 1;

    // echoing JSON response

    echo json_encode($response);

} else {

    // no products found

    $response["success"] = 0;

    $response["message"] = "No products found";

    // echo no users JSON

    echo json_encode($response);

}

?>

3)Table layout for data to be store here only layout is specified and the TableRow and columns are in java code 

    tablemain.xml

   <?xml version="1.0" encoding="utf-8"?>

 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

    android:layout_width="fill_parent"

    android:layout_height="fill_parent"

    android:orientation="horizontal"

    android:background="#3b3b3b">

    

    <HorizontalScrollView

  android:layout_width="fill_parent"

android:layout_height="fill_parent"

android:fillViewport="true"

> 

    <TableLayout xmlns:android="http://schemas.android.com/apk/res/android"

                    android:stretchColumns="0,1"

                    android:id="@+id/main_table" 

                    android:layout_weight="1" 

                    android:layout_height="wrap_content" 

                    android:layout_width="fill_parent"

        >

       

            </TableLayout>

</HorizontalScrollView>

</LinearLayout>

4) Java code for fetch the JSON array and parse JSON array into JSON Object and store it in table layout

   4.1)Create JSONPARSER class to parse json data later this class will be called for parsing json data throughout the application

JSONParser.java

   import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.json.JSONException;
import org.json.JSONObject;

import android.util.Log;

public class JSONParser {

static InputStream is = null;
static JSONObject jObj = null;
static String json = "";

// constructor
public JSONParser() {

}

public JSONObject getJSONFromUrl(String url) {

// Making HTTP request
try {
// defaultHttpClient
DefaultHttpClient httpClient = new DefaultHttpClient();
HttpPost httpPost = new HttpPost(url);

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

} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (ClientProtocolException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

try {
BufferedReader reader = new BufferedReader(new InputStreamReader(
is, "iso-8859-1"), 8);
StringBuilder sb = new StringBuilder();
String line = null;
while ((line = reader.readLine()) != null) {
sb.append(line + "\n");
}
is.close();
json = sb.toString();
} catch (Exception e) {
Log.e("Buffer Error", "Error converting result " + e.toString());
}

// try parse the string to a JSON object
try {
jObj = new JSONObject(json);
} catch (JSONException e) {
Log.e("JSON Parser", "Error parsing data " + e.toString());
}

// return JSON String
return jObj;

}
}

4.2) Create class parse json data by calling JSONparser class and display it in table 

example.java

   public class example extends Activity {

    /** Called when the activity is first created. */

private static String url = "http://nissarcs.net78.net/android/example.php";

private static final String TAG_Assessments = "Assessments1";

private static final String TAG_CODE = "COURSE";

private static final String TAG_CNH = "COURSE_NAME";

private static final String TAG_CR = "CR";

private static final String TAG_CT = "CT";

JSONArray degree = null;

 tl.addView(tr_head, new TableLayout.LayoutParams(

                LayoutParams.FILL_PARENT,

                LayoutParams.WRAP_CONTENT));

        Integer count=0;

        ArrayList<HashMap<String, String>> contactList = new ArrayList<HashMap<String, String>>();

        JSONParser jParser = new JSONParser();

        JSONObject json = jParser.getJSONFromUrl(url);

        try {

// Getting Array of Contacts

        degree = json.getJSONArray(TAG_Assessments);

// looping through All Contacts

for(int i = 0; i < degree.length(); i++){

JSONObject c = degree.getJSONObject(i);

// Storing each json item in variable

   

    String code = c.getString(TAG_CODE);

    String name = c.getString(TAG_CNH);

    String cr = c.getString(TAG_CR);

    String ct = c.getString(TAG_CT);

   

TableRow tr = new TableRow(this);

      if(count%2!=0) tr.setBackgroundColor(Color.BLACK);

      tr.setPadding(0, 0, 0, 2);

      tr.setId(100+count);

      tr.setLayoutParams(new LayoutParams(

      LayoutParams.WRAP_CONTENT,

      LayoutParams.WRAP_CONTENT));

      //Create two columns to add as table data

        // Create a TextView to add date

       

      TextView labelCR = new TextView(this);

      labelCR.setId(200+count); 

      labelCR.setText(cr);

      labelCR.setPadding(5, 5, 5, 5);

      labelCR.setTextColor(Color.BLACK);

      labelCR.setBackgroundColor(Color.GRAY);

      tr.addView(labelCR);

       

      TextView labelSC = new TextView(this);

      labelSC.setId(200+count);

      labelSC.setText(code.toString());

      labelSC.setPadding(5, 5, 5, 5);

      labelSC.setTextColor(Color.BLACK);

      labelSC.setBackgroundColor(Color.LTGRAY);

      tr.addView(labelSC);

       

      TextView labelACTIVITY = new TextView(this);

      labelACTIVITY.setId(200+count);

      labelACTIVITY.setText(name.toString());

      labelACTIVITY.setPadding(5, 5, 5, 5);

      labelACTIVITY.setTextColor(Color.BLACK);

      labelACTIVITY.setBackgroundColor(Color.GRAY);

      tr.addView(labelACTIVITY);

       

      TextView labelSAT = new TextView(this);

      labelSAT.setId(200+count);

      labelSAT.setText(cr.toString());

      labelSAT.setPadding(5, 5, 5, 5);

      labelSAT.setTextColor(Color.BLACK);

      labelSAT.setBackgroundColor(Color.LTGRAY);

      tr.addView(labelSAT);

    // finally add this to the table row

      tl.addView(tr, new TableLayout.LayoutParams(

                          LayoutParams.FILL_PARENT,

                          LayoutParams.WRAP_CONTENT));

// creating new HashMap

HashMap<String, String> map = new HashMap<String, String>();

// adding each child node to HashMap key => value

map.put(TAG_CODE, code);

map.put(TAG_CNH, name);

map.put(TAG_CR, cr);

map.put(TAG_CT,ct);

// adding HashList to ArrayList

contactList.add(map);

}

} catch (JSONException e) {

e.printStackTrace();

}

 }

}

Output Will be like this 

Please leave the comment if you find any problem with this tutorial