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.



41 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
  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
  17. This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..Android Training in chennai | Android Training|Android Training in chennai with placement | Android Training in velachery

    ReplyDelete
  18. no network Security config android Use platform deflut

    ReplyDelete
  19. no network Security config android Use platform deflut

    ReplyDelete
  20. Result screen displayed this message :(

    Parse error: syntax error, unexpected '}' in C:\xampp\htdocs\demons\selectiondb.php on line 21

    ReplyDelete
  21. E/log_tag: Error parsing data org.json.JSONException: Value <!DOCTYPE of type java.lang.String cannot be converted to JSONArray.. what could be the error?

    ReplyDelete
  22. Step by step instructions to Recover Deleted Rows from MySQL Database through DB Recovery Support
    On the off chance that shockingly you found that a portion of your information is erased from MySQL Database and you have no clue how to recoup the information then one of the finest methods to get back your information is Cognegic's DB Recovery Services or Exchange Database Recovery. Here we offer propel client benefit which effectively limit the hazard and cost and step by step pick up the business advantage. Our Backup Recovery process is very simple and at moderate value go.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  23. The most effective method to Recover Dropped Database on MySQL through DB Recovery Support |Cognegic|
    Dropping a database is most happening issue which typically asked by the clients. Be that as it may, recuperating this database is some place vital. In more often than not your IT overseer likewise not ready to recuperate it. That is the reason I am instructing you to recoup your dropped database with the assistance of Cognegic's DB Recovery Support or Exchange Database Recovery. We completely comprehend the issues or difficulties looked by you with respect to information recuperation. By remembering these things we give Backup Recovery to a wide range of databases including MongoDB, Cassandra, and MySQL et cetera.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  24. Not able to Transfer MySQL Data to Cassandra Database? Contact to Cassandra Technical Support.
    On the off chance that you are utilizing MySQL Server underway side and that is at present having 200GB then unquestionably it turns out to be extremely awkward to oversee MySQL server since it is developing exponentially. To comprehend these sorts of issue, Cassandra database appeared which has capacity to store substantial measure of information without making any issue. In any case, the inquiry how you can exchange all your MySQL Data to Cassandra database? Well! Try not to freeze since we at Cognegic give Apache Cassandra Support or Cassandra Customer Service to explain these sorts of issues.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  25. everytime I run it,the app stops.What can be the possible reasons?

    ReplyDelete
  26. Hi dears! I want to fetch data by clicking on recyclerview item. I want to display the user's data by clicking on specific name in the recyclerview.

    ReplyDelete
  27. i cant connect with mysql db,help me guys..........

    ReplyDelete