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
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
Congrats, you have successfully retrieved data from MySQL database.
Related Posts
Nithya Govind
Latest posts by Nithya Govind (see all)
- Yrold boy yrs old Georgetown, or unexpected situations - February 3, 2023
- Hence one hundred % totally free Connection other sites need real Profiles? - February 3, 2023
- Fantastiche Escort Gemona del Friuli al di la qualunque immaginazione - February 3, 2023
I am new at android programming.. can you post tutorials for retrieval of images too along with other data like above from database
Is your image is uploaded in server folder?
Thanks..I got the result.. but I want to update the table details and save into the database table.. please help me…
Is their a way to display a data in textview?
hi
ur code is working but i need to show 5columns. i dont know to modify this code.. Help me
Hi Lavanya,
Consider you are getting 5 columns from your php code, then include the following code inside the loop( if (flag == 1) )
TextView b4 = new TextView(MainActivity.this);
b4.setText(“4th Column”);
b4.setTextColor(Color.BLUE);
b4.setTextSize(15);
tr.addView(b4);
TextView b4 = new TextView(MainActivity.this);
b4.setText(“5th Column”);
b4.setTextColor(Color.BLUE);
b4.setTextSize(15);
tr.addView(b4);
And add the following code inside(Before the line: final View vline1 = new View(MainActivity.this))
TextView b = new TextView(MainActivity.this);
String stime = String.valueOf(json_data.getInt(“4th Coloumn”));
b.setText(stime);
b.setTextColor(Color.RED);
b.setTextSize(15);
tr.addView(b);
TextView b = new TextView(MainActivity.this);
String stime = String.valueOf(json_data.getInt(“5th column”));
b.setText(stime);
b.setTextColor(Color.RED);
b.setTextSize(15);
tr.addView(b);
Thanks for your post thats really helpful. Hope you can help me resolve the issue i am facing
I am new to Android programming and I am using Sider menu using Navigation Drawer but after using the above code I am getting the error at the line TextView txtTitle = (TextView) convertView.findViewById(R.id.title); in the below code. Please guide me how to resolve this.
@Override
public View getView(int position, View convertView, ViewGroup parent) {
if (convertView == null) {
LayoutInflater mInflater = (LayoutInflater)
context.getSystemService(Activity.LAYOUT_INFLATER_SERVICE);
convertView = mInflater.inflate(R.layout.drawer_list_item, null);
}
ImageView imgIcon = (ImageView) convertView.findViewById(R.id.icon);
TextView txtTitle = (TextView) convertView.findViewById(R.id.title);
TextView txtCount = (TextView) convertView.findViewById(R.id.counter);
imgIcon.setImageResource(navDrawerItems.get(position).getIcon());
txtTitle.setText(navDrawerItems.get(position).getTitle());
// displaying count
// check whether it set visible or not
if(navDrawerItems.get(position).getCounterVisibility()){
txtCount.setText(navDrawerItems.get(position).getCount());
}else{
// hide the counter view
txtCount.setVisibility(View.GONE);
}
return convertView;
}
Can you please post your logcat details?
it is saying my app is unfortunately stopped plss sir help me fast rly plss
Missing styles. Is the correct theme chosen for this layout?
Use the Theme combo box above the layout to choose a different layout, or fix the theme style references.
Failed to find style ‘scrollViewStyle’ in current theme
Failed to find style ‘horizontalScrollViewStyle’ in current theme
this error come from the xml file and no show the layout in xml file any help???
if u dont mind plz send me source code of complete application on this email usamamehar@hotmail.com
774-774/com.example.john.retrive_from_mysql E/log_tag﹕ Error converting resultjava.lang.NullPointerException: lock == null
Can you send me the full LogCat error list?
In what Android folder did you place the PHP file in?
You have to place PHP files in your web server. PHP is a server side scripting language.
Thanks for the reply, but let me be more specific about my question. This is line 38 –> HttpPost httppost = new HttpPost(“http: //ip_address/folder/demo.php”);
This doesn’t specify exactly what “demo.php” is, but I assume that this is the credentials file in Step 3. So if we are trying to implement this example, where should we put the credentials file to connect to the DB? What folder is it? I’m new at Android. Or do we post the PHP file on a site (e.g. using FileZilla) and then copy the link and use that in our code like this user did in line 38?
my app is unfortunately stopped.. how can i fix this..? there’s a lot of red color in logcat
Can you give more details about the logcat errors.
I Got an i error..
i hope you can fix this…
03-25 01:49:38.745: E/AndroidRuntime(30877): FATAL EXCEPTION: main
03-25 01:49:38.745: E/AndroidRuntime(30877): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.aaaa.sdsad/com.aaaa.sdsad.MainActivity}: java.lang.NullPointerException
03-25 01:49:38.745: E/AndroidRuntime(30877): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2184)
03-25 01:49:38.745: E/AndroidRuntime(30877): at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2211)
03-25 01:49:38.745: E/AndroidRuntime(30877): at android.app.ActivityThread.access$600(ActivityThread.java:149)
03-25 01:49:38.745: E/AndroidRuntime(30877): at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1300)
03-25 01:49:38.745: E/AndroidRuntime(30877): at android.os.Handler.dispatchMessage(Handler.java:99)
03-25 01:49:38.745: E/AndroidRuntime(30877): at android.os.Looper.loop(Looper.java:153)
03-25 01:49:38.745: E/AndroidRuntime(30877): at android.app.ActivityThread.main(ActivityThread.java:4994)
03-25 01:49:38.745: E/AndroidRuntime(30877): at java.lang.reflect.Method.invokeNative(Native Method)
03-25 01:49:38.745: E/AndroidRuntime(30877): at java.lang.reflect.Method.invoke(Method.java:511)
03-25 01:49:38.745: E/AndroidRuntime(30877): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:821)
03-25 01:49:38.745: E/AndroidRuntime(30877): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:584)
03-25 01:49:38.745: E/AndroidRuntime(30877): at dalvik.system.NativeStart.main(Native Method)
03-25 01:49:38.745: E/AndroidRuntime(30877): Caused by: java.lang.NullPointerException
03-25 01:49:38.745: E/AndroidRuntime(30877): at org.json.JSONTokener.nextCleanInternal(JSONTokener.java:116)
03-25 01:49:38.745: E/AndroidRuntime(30877): at org.json.JSONTokener.nextValue(JSONTokener.java:94)
03-25 01:49:38.745: E/AndroidRuntime(30877): at org.json.JSONArray.(JSONArray.java:87)
03-25 01:49:38.745: E/AndroidRuntime(30877): at org.json.JSONArray.(JSONArray.java:103)
03-25 01:49:38.745: E/AndroidRuntime(30877): at com.aaaa.sdsad.MainActivity.onCreate(MainActivity.java:74)
03-25 01:49:38.745: E/AndroidRuntime(30877): at android.app.Activity.performCreate(Activity.java:5020)
03-25 01:49:38.745: E/AndroidRuntime(30877): at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1080)
03-25 01:49:38.745: E/AndroidRuntime(30877): at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2148)
03-25 01:49:38.745: E/AndroidRuntime(30877): … 11 more
You are getting error, because your response from server is empty. Please check out your demo.php file and response.
i changed the code for my library database. the php file is giving me the output. but the project is not working
04-08 06:56:18.109 2243-2243/com.example.library I/Process﹕ Sending signal. PID: 2243 SIG: 9
04-08 06:57:33.777 2294-2294/com.example.library E/log_tag﹕ Error in http connectionjava.lang.IllegalArgumentException: Illegal character in scheme specific part at index 5: http: //192.168.56.1/lib.php
04-08 06:57:33.825 2294-2294/com.example.library E/log_tag﹕ Error converting resultjava.lang.NullPointerException: lock == null
04-08 06:57:33.853 2294-2294/com.example.library D/AndroidRuntime﹕ Shutting down VM
04-08 06:57:33.853 2294-2294/com.example.library W/dalvikvm﹕ threadid=1: thread exiting with uncaught exception (group=0xa4d6ab20)
04-08 06:57:33.965 2294-2294/com.example.library E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: com.example.library, PID: 2294
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.library/com.example.library.MainActivity}: java.lang.NullPointerException
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2184)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2233)
at android.app.ActivityThread.access$800(ActivityThread.java:135)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5001)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.NullPointerException
at org.json.JSONTokener.nextCleanInternal(JSONTokener.java:116)
at org.json.JSONTokener.nextValue(JSONTokener.java:94)
at org.json.JSONArray.(JSONArray.java:92)
at org.json.JSONArray.(JSONArray.java:108)
at com.example.library.MainActivity.onCreate(MainActivity.java:138)
at android.app.Activity.performCreate(Activity.java:5231)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2148)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2233)
at android.app.ActivityThread.access$800(ActivityThread.java:135)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5001)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
at dalvik.system.NativeStart.main(Native Method)
04-08 06:57:50.281 2294-2294/com.example.library I/Process﹕ Sending signal. PID: 2294 SIG: 9
04-08 06:57:56.833 2314-2314/com.example.library E/log_tag﹕ Error in http connectionjava.lang.IllegalArgumentException: Illegal character in scheme specific part at index 5: http: //192.168.56.1/lib.php
04-08 06:57:56.889 2314-2314/com.example.library E/log_tag﹕ Error converting resultjava.lang.NullPointerException: lock == null
04-08 06:57:56.917 2314-2314/com.example.library D/AndroidRuntime﹕ Shutting down VM
04-08 06:57:56.921 2314-2314/com.example.library W/dalvikvm﹕ threadid=1: thread exiting with uncaught exception (group=0xa4d6ab20)
04-08 06:57:57.033 2314-2314/com.example.library E/AndroidRuntime﹕ FATAL EXCEPTION: main
Process: com.example.library, PID: 2314
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.library/com.example.library.MainActivity}: java.lang.NullPointerException
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2184)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2233)
at android.app.ActivityThread.access$800(ActivityThread.java:135)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5001)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
at dalvik.system.NativeStart.main(Native Method)
Caused by: java.lang.NullPointerException
at org.json.JSONTokener.nextCleanInternal(JSONTokener.java:116)
at org.json.JSONTokener.nextValue(JSONTokener.java:94)
at org.json.JSONArray.(JSONArray.java:92)
at org.json.JSONArray.(JSONArray.java:108)
at com.example.library.MainActivity.onCreate(MainActivity.java:138)
at android.app.Activity.performCreate(Activity.java:5231)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1087)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2148)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2233)
at android.app.ActivityThread.access$800(ActivityThread.java:135)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1196)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:136)
at android.app.ActivityThread.main(ActivityThread.java:5001)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
at dalvik.system.NativeStart.main(Native Method)
04-08 06:58:00.661 2314-2314/com.example.library I/Process﹕ Sending signal. PID: 2314 SIG: 9
these are my various logcat errors. please
same hee…what to do ???? 🙁
please give me guidance…im in a hurry !!! 🙁
hello
same code giving null pointer exception…. And also I want to display data in List instead table…
Please help me to solve the problem
i want to get the data from my database and display them in textarea and also want to fetch the image , its like a profile . plz help me make it more attractive
how to create database for this code and how to resolve log errors in code?
Fetched table data showing in logcat but not in android screen..only headings appear
haloo… how to retrieve data and view in horizontal mode.
04-13 15:32:15.298: E/AndroidRuntime(1177): at org.json.JSONTokener.nextCleanInternal(JSONTokener.java:116)
04-13 15:32:15.298: E/AndroidRuntime(1177): at org.json.JSONTokener.nextValue(JSONTokener.java:94)
04-13 15:32:15.298: E/AndroidRuntime(1177): at org.json.JSONArray.(JSONArray.java:92)
04-13 15:32:15.298: E/AndroidRuntime(1177): at org.json.JSONArray.(JSONArray.java:108)
04-13 15:32:15.298: E/AndroidRuntime(1177): at com.example.rate.MainActivity.onItemSelected(MainActivity.java:194)
//JSONArray jArray = new JSONArray(result); //logcat is showing error in this line. Tell the solution to fix it.
sorry bro, what is log_tag required java
pls help me now!
I am new at android programming.. can you post tutorials for fetch data from database in android using json and php??