Thursday, July 5, 2012

Retrieve database from mysql and displaying using dynamic table in android




Click here to download this program 


I Installed WAMP server on my local machine for excuting PHP programs.

I have programmed in windows 7.

If You need WAMP server, Click here to download.

Before run this program, start wampserver

programs --> wampserver --> start wampserver

see right side below of taskbar WAMPSERVER -server offline icon present.









This is the wampserver icon







 Left click icon --> start all services













package name: selva.select

project name: Dbselect

version :  1.5 ( support 1.5 and above versions)




main.xml



<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >
    
    <Button
        android:id="@+id/button1"
        android:layout_below="@+id/e1"
        android:layout_width="276dp"
        android:layout_height="wrap_content"
        android:text="Button" />

   
 <ScrollView
         android:layout_below="@+id/button1"
        android:layout_width="fill_parent"
        android:layout_height="fill_parent"
        android:focusableInTouchMode="true"
        android:focusable="true" >

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

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

    </HorizontalScrollView>
     
   </ScrollView>

</RelativeLayout>


AndroidManifest.xml



<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="selva.select"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk android:minSdkVersion="3" />
      <uses-permission android:name="android.permission.INTERNET"/>
     
    <application
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name" >
        <activity
            android:label="@string/app_name"
            android:name=".DbselectActivity" >
            <intent-filter >
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>





DbselectActivity.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 DbselectActivity extends Activity
{
  
  
     public void onCreate(Bundle savedInstanceState)
    
     {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.main);
          
            Button button = (Button) findViewById(R.id.button1);
          
          
            button.setOnClickListener(new View.OnClickListener()
            {
            public void onClick(View view)
              {
                 String result = null;
                InputStream is = null;
              
                try{
                        HttpClient httpclient = new DefaultHttpClient();
                        HttpPost httppost = new HttpPost("http://10.0.2.2/selectall.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);
              
     
                String re=jArray.getString(jArray.length()-1);
              
  
                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(DbselectActivity.this);
                              
                                tr.setLayoutParams(new LayoutParams(
                                           LayoutParams.FILL_PARENT,
                                           LayoutParams.WRAP_CONTENT));
                              
                              
                              
                           
                                if(flag==1)
                                {
                                  
                                    TextView b6=new TextView(DbselectActivity.this);
                                     b6.setText("ID");
                                     b6.setTextColor(Color.BLUE);
                                     b6.setTextSize(15);
                                     tr.addView(b6);
                              
                                  
                                    TextView b19=new TextView(DbselectActivity.this);
                                     b19.setPadding(10, 0, 0, 0);
                                     b19.setTextSize(15);
                                     b19.setText("Name");
                                     b19.setTextColor(Color.BLUE);
                                     tr.addView(b19);
                                   
                                   TextView b29=new TextView(DbselectActivity.this);
                                 b29.setPadding(10, 0, 0, 0);
                                     b29.setText("no");
                                     b29.setTextColor(Color.BLUE);
                                     b29.setTextSize(15);
                                     tr.addView(b29);
                           
                                   
                                 tv.addView(tr);
                              
                                     final View vline = new View(DbselectActivity.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("f1")+
                                              ", Username: "+json_data.getString("f2")+
                                              ", No: "+json_data.getInt("f3"));
                           
                              
                              
                          
                            TextView b=new TextView(DbselectActivity.this);
                                String stime=String.valueOf(json_data.getInt("f1"));
                                  b.setText(stime);
                                b.setTextColor(Color.RED);
                                b.setTextSize(15);
                                tr.addView(b);
                         
                             
                               TextView b1=new TextView(DbselectActivity.this);
                                b1.setPadding(10, 0, 0, 0);
                                b1.setTextSize(15);
                                String stime1=json_data.getString("f2");
                                 b1.setText(stime1);
                                b1.setTextColor(Color.WHITE);
                                tr.addView(b1);
                              
                              TextView b2=new TextView(DbselectActivity.this);
                             b2.setPadding(10, 0, 0, 0);
                                String stime2=String.valueOf(json_data.getInt("f3"));
                                b2.setText(stime2);
                                b2.setTextColor(Color.RED);
                                b2.setTextSize(15);
                                tr.addView(b2);
                      
                                  tv.addView(tr);
                          
                          
                        final View vline1 = new View(DbselectActivity.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();
                }

              

                
           }
           });
          
          


     }
  

}




database name: ex1

Table name: t1

Fields Name: f1(bigint)  ,f2(text)  ,f3(bigint)



 selectall.php



    <?php

         $con = mysql_connect("localhost","root","");
         if (!$con)
               {
                         die('Could not connect: ' . mysql_error());
                }

           mysql_select_db("ex1", $con);

           
           $i=mysql_query("select * from t1",$con);

           $num_rows = mysql_num_rows($i);
        

           $check='';
          while($row = mysql_fetch_array($i))
            {
 
                  $r[]=$row;
                  $check=$row['f1']             }

         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($con);
              
    ?>
 

OUTPUT:





Click button






Click here to download this program.



28 comments:

  1. thank you for the tutorial, but i keep getting the unfortunately error , can you please help me with it ?

    ReplyDelete
    Replies
    1. most probably your missing networ permission. :)

      Delete
  2. @Selva Great tutorial Thanks
    I run your code and I'm getting "JSONArray fail" exception.
    can you please tell me how to resolve it.

    Thanks

    ReplyDelete
  3. thank you for the tutorial, but i keep getting the unfortunately error , can you please help me with it ?

    ReplyDelete
  4. how to multiple person select using check box using android app and store the data mysql

    ReplyDelete
  5. Error parsing data org.json.JSONException: Value <!DOCTYPE of type java.lang.String cannot be converted to JSONArray

    plz plz help me guys...........

    ReplyDelete
    Replies
    1. Guys don't forget ";" at line 22 in selectall.php file.

      Delete
    2. This comment has been removed by the author.

      Delete
    3. just simply show the result of your json,. system out to show the error result.. cannot convert it bcuz something wrong with your php script.. :)

      Delete
  6. Thank you master for the codes it works fine on me. But i have a problem i can't display the last records or new added records from my mysql database. I think i had a problem with the loop right?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  7. Is it possible to filtering it

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  8. Thanks for ur code.
    If i want to give some input in textview then the related info should be viewed in table from mysql means what i have to do. plz help me.

    ReplyDelete
  9. Great tutorial !!!!! It helped me a lot..Thanku..

    ReplyDelete
  10. nice. Please help me for advanced . Next I click on 2 id then after show the more information for 2 id. pls help me. my email id is jailupatel007@gmail.com

    Send code my emailid or comment

    ReplyDelete
  11. hi sir. what if i wanted to fetch an image from mysql database? Please help me :)

    ReplyDelete
  12. I am able retrieve only last row from sql server express database can you plzzzzz help me out

    ReplyDelete
    Replies
    1. Able to retrieve only last row from the table ...reply me soon

      Delete
  13. This comment has been removed by the author.

    ReplyDelete
  14. i got a error = java.lang.NullPointerException: Attempt to invoke virtual method 'int java.lang.String.length()' on a null object reference
    in this line : JSONArray jArray = new JSONArray(result);

    plzz anybody help me

    ReplyDelete
  15. Hello All,
    I am getting the below errorwhile trying to cnnect:

    Error converting result java.lang.NullPointerException: lock == null

    Please help me

    thank s in advance.

    ReplyDelete
    Replies
    1. I am new to android
      I am getting Null response from server
      Idon't know the reason .
      Please help him what is the possible mistake done by me.

      Delete
  16. I am new to android
    I am getting Null response from server
    Idon't know the reason .
    Please help him what is the possible mistake done by me.

    ReplyDelete