Retrieve Data from Mysql Database using PHP and displaying it by TableView in Android

In this blog post, I would like to explain how to get data from MySQL database and display it by table view in Android.

If you want to know more about JSON and simple parsing technique, Please read this blog before continuing..

First, you have to create a Layout using XML code. Next, you have to give Internet Permission in Android Manifest(Controller of Android Project) file  because, we are going to connect with server to get data. Next, you have to create a PHP file (Server side scripting – To get the data from database and send to the Android project). Finally, you have to create a Activity using Java.

The output will be

demo

Step 1:

First, we have to create a User Interface, and in order to create one, include the following XML code in the layout file:

activity_main.xml


<?xml version="1.0" encoding="utf-8"?>
<ScrollView 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">

    <HorizontalScrollView android:layout_width="fill_parent" android:layout_height="wrap_content" android:focusableInTouchMode="true" android:focusable="true">

        <TableLayout android:id="@+id/table" android:layout_width="wrap_content" android:layout_height="fill_parent" android:focusableInTouchMode="true" android:focusable="true"></TableLayout>

    </HorizontalScrollView>
</ScrollView>

Step 2:

If you wish to connect with Internet,  you should include the following  line:
<uses-permission android:name=“android.permission.INTERNET”/> in AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.example.demo" 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.example.demo.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>

Step 3:

To retrieve the data from MySQL database, include the following code, and save it in to your web folder. In case you are using Xampp, save this PHP file inside the “htdocs” folder.

<?php
$username =’root';
$password =”;
$hostname =’localhost';
$database =’db_name';

$localhost = mysql_connect($hostname,$username,$password) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database,$localhost);
$i=mysql_query(“select * from table_name”);

$num_rows = mysql_num_rows($i);
while($row = mysql_fetch_array($i))
{

$r[]=$row;
$check=$row['Id'];
}

if($check==NULL)
{
$r[$num_rows]=”Record is not available”;
print(json_encode($r));
}
else
{
$r[$num_rows]=”success”;
print(json_encode($r));
}

mysql_close($localhost);
?>

 

Step 4:

To display the output from PHP in a table format, include following code in Mainactivity.java class:

MainActivity.java


import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
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.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.Button;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;
import android.widget.Toast;
import android.widget.TableRow.LayoutParams;

public class MainActivity extends Activity {
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		/* Button button = (Button) findViewById(R.id.button1);
button.setOnClickListener(new View.OnClickListener()
{
@SuppressWarnings(“deprecation”)
public void onClick(View view)
{*/
		String result = null;
		InputStream is = null;
		try {
			HttpClient httpclient = new DefaultHttpClient();
			HttpPost httppost = new HttpPost(“http: //ip_address/folder/demo.php”);
			HttpResponse response = httpclient.execute(httppost);
			HttpEntity entity = response.getEntity();
			is = entity.getContent();

			Log.e(“log_tag”, “connection success“);
			//   Toast.makeText(getApplicationContext(), “pass”, Toast.LENGTH_SHORT).show();
			} catch (Exception e) {
				Log.e(“log_tag”, “Error in http connection“ + e.toString());
				Toast.makeText(getApplicationContext(), “Connection fail”, Toast.LENGTH_SHORT).show();

			}
			//convert response to string
			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”);
					//  Toast.makeText(getApplicationContext(), “Input Reading pass”, Toast.LENGTH_SHORT).show();
				}
				is.close();

				result = sb.toString();
			} catch (Exception e) {
				Log.e(“log_tag”, “Error converting result“ + e.toString());
				Toast.makeText(getApplicationContext(), ”Input reading fail”, Toast.LENGTH_SHORT).show();

			}

			//parse json data
			try {
				JSONArray jArray = new JSONArray(result);
				TableLayout tv = (TableLayout) findViewById(R.id.table);
				tv.removeAllViewsInLayout();
				int flag = 1;
				for (int i = -1; i < jArray.length() - 1; i++) {
					TableRow tr = new TableRow(MainActivity.this);
					tr.setLayoutParams(new LayoutParams(
					LayoutParams.FILL_PARENT,
					LayoutParams.WRAP_CONTENT));
					if (flag == 1) {
						TextView b6 = new TextView(MainActivity.this);
						b6.setText(“Id”);
						b6.setTextColor(Color.BLUE);
						b6.setTextSize(15);
						tr.addView(b6);
						TextView b19 = new TextView(MainActivity.this);
						b19.setPadding(10, 0, 0, 0);
						b19.setTextSize(15);
						b19.setText(“Name”);
						b19.setTextColor(Color.BLUE);
						tr.addView(b19);
						TextView b29 = new TextView(MainActivity.this);
						b29.setPadding(10, 0, 0, 0);
						b29.setText(“Status”);
						b29.setTextColor(Color.BLUE);
						b29.setTextSize(15);
						tr.addView(b29);
						tv.addView(tr);
						final View vline = new View(MainActivity.this);
						vline.setLayoutParams(new TableRow.LayoutParams(TableRow.LayoutParams.FILL_PARENT, 2));
						vline.setBackgroundColor(Color.BLUE);
						tv.addView(vline);
						flag = 0;
					} else {
						JSONObject json_data = jArray.getJSONObject(i);
						Log.i(“log_tag”, ”id: “ + json_data.getInt(“Id”) + “, Username: “ + json_data.getString(“username”) + “, No: “ + json_data.getString(“comment”));
						TextView b = new TextView(MainActivity.this);
						String stime = String.valueOf(json_data.getInt(“Id”));
						b.setText(stime);
						b.setTextColor(Color.RED);
						b.setTextSize(15);
						tr.addView(b);
						TextView b1 = new TextView(MainActivity.this);
						b1.setPadding(10, 0, 0, 0);
						b1.setTextSize(15);
						String stime1 = json_data.getString(“username”);
						b1.setText(stime1);
						b1.setTextColor(Color.BLACK);
						tr.addView(b1);
						TextView b2 = new TextView(MainActivity.this);
						b2.setPadding(10, 0, 0, 0);
						String stime2 = json_data.getString(“comment”);
						b2.setText(stime2);
						b2.setTextColor(Color.BLACK);
						b2.setTextSize(15);
						tr.addView(b2);
						tv.addView(tr);
						final View vline1 = new View(MainActivity.this);
						vline1.setLayoutParams(new TableRow.LayoutParams(TableRow.LayoutParams.FILL_PARENT, 1));
						vline1.setBackgroundColor(Color.WHITE);
						tv.addView(vline1);
					}
				}
			} catch (JSONException e) {
				Log.e(“log_tag”, “Error parsing data“ + e.toString());
				Toast.makeText(getApplicationContext(), “JsonArray fail”, Toast.LENGTH_SHORT).show();
			}

		}

	}

Screenshot

demo

Congrats, you have successfully retrieved data from MySQL database.

Comments
  1. shradha
    • Nithya Govind
  2. Neema
  3. kriziah
  4. lavanya
    • Nithya Govind
  5. SATEESH REDDY
    • Nithya Govind
  6. saipriya
  7. usama
  8. usama
  9. john
    • Nithya Govind
  10. Anthony
    • Nagarajan
      • Anthony
  11. Rahel
    • Nagarajan
      • jason.
        • Nithya Govind
          • thara
    • thara
  12. sneha
  13. Darshana
  14. Supriya
  15. Rajat
  16. agustri
  17. Sandeep
  18. aduukidato3@gmail.com
    • Aduu
  19. surekha kharde

Leave a Reply

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