Monday, September 5, 2011

Connecting to Remote MySQL database


Introduction

               In this post we can see how to connect remote MySQL database throw android application

Connect to MySQL

We can code the data in JSON format, between Android and PHP with the easy to use built in JSON functions in both languages.
               

I present some sample code, which selects data from a database depending on a given condition and creates a log message on the android side with the received data.
Lets suppose that we have a MySQL database named PeopleData, and a table int created, with the following SQL:
CREATE TABLE people (                
   
      id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
   
      name VARCHAR( 100 ) NOT NULL ,
   
      sex BOOL NOT NULL DEFAULT '1',
   
      birthyear INT NOT NULL
   
      )


PHP File


The PHP code will be very simple:
    - connect to the database
    - run an SQL query, with a WHERE block depending on data from POST/GET values
    - output it in JSON format


For example we will have this functionality in the getAllPeopleBornAfter.php file:
<?php 

      mysql_connect("127.0.0.1","root","xxpasswordxx");

      mysql_select_db("peopledata");

      $q=mysql_query("SELECT * FROM people WHERE birthyear>'".$_REQUEST['year']."'");

      while($e=mysql_fetch_assoc($q))

              $output[]=$e;

           print(json_encode($output));
     
    mysql_close();
?>




Android Part

The Android part is only a bit more complicated:
    - use a HttpPost to get the data, sending the year value
    - convert response to string
    - parse JSON data, and use it as you want






package com.connector;


import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;


import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;


import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.widget.LinearLayout;
import android.widget.TextView;




public class whitehat extends Activity {
/** Called when the activity is first created. */
   
   TextView txt;
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    // Create a crude view - this should really be set via the layout resources  
    // but since its an example saves declaring them in the XML.  
    LinearLayout rootLayout = new LinearLayout(getApplicationContext());  
    txt = new TextView(getApplicationContext());  
    rootLayout.addView(txt);  
    setContentView(rootLayout);  


    // Set the text and call the connect function.  
    txt.setText("Connecting..."); 
  //call the method to run the data retreival
    txt.setText(getServerData(KEY_121)); 






}
public static final String KEY_121 = "http://xx.xx.xxx.xxx/hellomysql/mysqlcon.php"; //i use my real ip here






private String getServerData(String returnString) {
    
   InputStream is = null;
    
   String result = "";
    //the year data to send
    ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
    nameValuePairs.add(new BasicNameValuePair("year","1970"));


    //http post
    try{
            HttpClient httpclient = new DefaultHttpClient();
            HttpPost httppost = new HttpPost(KEY_121);
            httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            HttpResponse response = httpclient.execute(httppost);
            HttpEntity entity = response.getEntity();
            is = entity.getContent();


    }catch(Exception e){
            Log.e("log_tag", "Error in http connection "+e.toString());
    }


    //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");
            }
            is.close();
            result=sb.toString();
    }catch(Exception e){
            Log.e("log_tag", "Error converting result "+e.toString());
    }
    //parse json data
    try{
            JSONArray jArray = new JSONArray(result);
            for(int i=0;i<jArray.length();i++){
                    JSONObject json_data = jArray.getJSONObject(i);
                    Log.i("log_tag","id: "+json_data.getInt("id")+
                            ", name: "+json_data.getString("name")+
                            ", sex: "+json_data.getInt("sex")+
                            ", birthyear: "+json_data.getInt("birthyear")
                    );
                    //Get an output to the screen
                    returnString += "\n\t" + jArray.getJSONObject(i); 
            }
    }catch(JSONException e){
            Log.e("log_tag", "Error parsing data "+e.toString());
    }
    return returnString; 
}    
    
}






50 comments:

  1. Hi My Name is Guru. I used your code but i got the o/p in JSON itslef not in a list... can you help me out please...

    ReplyDelete
  2. @Guru.M
    Hi
    You want display the list means you declare the array list and add the value to that array list.
    Display that array list in a List view for example

    List r = new ArrayList();

    JSONArray jArray = new JSONArray(result);
    JSONObject json_data=null;
    for(int i=0;i(this, android.R.layout.simple_list_item_1,r));

    ReplyDelete
  3. If you want to save the data you get back from mysql and save it into your slqite database i believe you have to do something like this:

    String qureylog ="insert or replace into table(name, sex, birthyear) " +
    "values('"+json_data.getString("name")+"'," +
    " '"+json_data.getString("sex")+"'," +
    " '"+json_data.getString("birthyear")+"')";
    dbHelper.insert(qureylog, returnString, returnString);


    And then where ever your Database Helper is; you will need the insert method:

    But how do you complete that insert method?

    I made this but does not seem to work:

    public long insert (String name, String sex, String birthyear) {
    ContentValues initialValues = createContentValues (sex, name, birthyear);
    return database.insert(DATABASE_TABLE, null, initialValues);
    }

    private ContentValues createContentValues(String sex, String name,
    String birthyear) {
    ContentValues values = new ContentValues();
    values.put(KEY_NAME, name);
    values.put(KEY_SEX, sex);
    values.put(KEY_BIRTHYEAR, birthyear);


    return values;
    }

    ReplyDelete
  4. Thanks Anonymous for sharing these things

    ReplyDelete
  5. Hi Krishna, can you explain a little clearer for

    "@Guru.M
    Hi
    You want display the list means you declare the array list and add the value to that array list.
    Display that array list in a List view for example

    List r = new ArrayList();

    JSONArray jArray = new JSONArray(result);
    JSONObject json_data=null;
    for(int i=0;i(this, android.R.layout.simple_list_item_1,r));"

    I am new to Android.

    I have an listview

    "ListView listview = (ListView) this.findViewById(R.id.listview);"

    How do I add all the data into the listview?

    ReplyDelete
  6. Hi Yi Wei

    Declare a class with the values. Ex- Item
    Item.class source
    public class Item implements Comparable {

    private long id;
    private String caption;

    public Item(long id, String caption) {
    super();
    this.id = id;
    this.caption = caption;
    }

    public long getId() {
    return id;
    }

    public void setId(long id) {
    this.id = id;
    }

    public String getCaption() {
    return caption;
    }

    public void setCaption(String caption) {
    this.caption = caption;
    }
    }


    Derive the class

    private List value;

    value.add(new Item(json_data.getLong("id"), json_data
    .getString("value")));

    ListAdapter adapter=new ListAdapter(value);
    listName.setAdapter(adapter);

    Hope this helps.Face any problem let me know
    Thanks
    Krishna

    ReplyDelete
  7. nameValuePairs.add(new BasicNameValuePair("year","1970"));

    If you want to get the "1970" from your local database, How would you get that?

    Then replace it with: e.g. BasicNameValuePair("year","KEY_DATE"));

    ReplyDelete
  8. Hi, well my doubt is I have to use my ip address or I can use the domain name?

    ReplyDelete
    Replies
    1. Hi Anonymous

      You want to change the line of code here

      public static final String KEY_121 = "http://xx.xx.xxx.xxx/hellomysql/mysqlcon.php";

      instead of xx.xx.xxx.xxx use your own ip address or your domain

      Delete
  9. can anyone please give me link or code about when i fetch data from database i should able to click on text to go to next activity…example suppose we have book store names in database after we get result from above code then if i click on perticular book shop name it should go to next activity….thnx in advance

    ReplyDelete
    Replies
    1. Hi

      Add new Activity opening code in the onClick or onTouch event of the particular UI.That means based on your example you are displaying a Book store name in a Text field or List means you write the new Activity code for that particular Text field or List

      Code for new Activity opening is given

      Intent i= new Intent(currentActivity.this,nextActivity.class);
      startActivity(i);

      Delete
  10. Would anyone here be able to help with something like this with my app?

    ReplyDelete
    Replies
    1. Can you please tell me the requirement...

      Delete
    2. Hi Krishna.

      Basically, i have a mysql database with 3 tables = users, classes, class_bookings.

      The user table holds the log in details of users.
      The classes table holds a list of gym classes and their dates and times.
      The class bookings holds the booking id, user id and class id. (joining table).

      Currently, my user can log in or register within the app. Once logged in, they are able to view a dashboard.
      The dashboard will display two buttons (options). These are "view class timetables" and "my bookings".

      In the timetable area, the user should be able to view the classes available from the classes table. They must be able to select a class from this list to book.

      Once they have booked, they can view "my bookings" and the user should be able to see the class they have just booked. They must also be able to cancel the bookings they have made (this will update the database).

      I hope you understand what i am looking for. Basically a booking system

      Delete
    3. Hi
      First you login the system take the user id from mysql table and insert the booking of the particular user booking based on the user id.
      Then display the bookings based on the user id
      Thats it

      Delete
    4. Do you know of a tutorial for this? I cannot work out how to do the parts after the log in. Can I email my work i have completed so far if you can have a look?

      Delete
  11. Do you know of a tutorial for this? I cannot work out how to do the parts after the log in. Can I email my work i have completed so far if you can have a look?

    ReplyDelete
    Replies
    1. Ok please send to my email my email is gk.krishna14@gmail.com
      And this one need some basic knowledge about php.You have any idea in php

      Delete
    2. I have some. I shall send you the files

      Delete
    3. ok send.And tell your name and you are from

      Delete
    4. I have sent. My name is Justine and I am from London

      Delete
  12. Krishna you have disappeared :(

    ReplyDelete
  13. Sorry Justine little busy with my work

    ReplyDelete
  14. Hi Krishna, me again. Not sure if you are receiving my emails. Please let me know :)
    many thanks

    ReplyDelete
  15. Hi ...

    The above tutorial is successfully worked for me...Thank for posting such a nice tutorial...But i wish to connecting mysql database using SOAP WEBSERVICES.please help me yar.

    ReplyDelete
  16. Hi Krishna

    even though i don't get any exception, i can't retrieve any row from my

    table. I only retrieve by my IP address (is shown returnString => TextView txt)

    that I requested. Do you have any idea why I can't get query result;

    Note: I check my query result from phpmyadmin, no problem have seen

    ReplyDelete
    Replies
    1. Hi Seven
      Can you please give me your table rows

      Delete
  17. This code is not working for me , i get following errors in log:

    error opening trace file: No such file or directory (2)
    Error in http connection android.os.NetworkOnMainThreadException
    Error converting result java.lang.NullPointerException
    Error parsing data org.json.JSONException: End of input at character 0 of

    Someone pls help ....

    ReplyDelete
  18. getting this error
    Error in http connection android.os.NetworkOnMainThreadException

    at the execution of
    public static final String KEY_121 = "http://www.fbtech.co.in/getallpeople.php";

    try{
    HttpClient httpclient = new DefaultHttpClient();
    HttpPost httppost = new HttpPost(KEY_121);
    // httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
    HttpResponse response = httpclient.execute(httppost);
    HttpEntity entity = response.getEntity();
    is = entity.getContent();


    }catch(Exception e){
    Log.e("log_tag", "Error in http connection "+e.toString());
    }
    plzzzzzzz help

    ReplyDelete
    Replies
    1. You cannot run network actions on the UI thread, you must use a thread...
      So, you can either add this right above your try function:

              DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
              DocumentBuilder db;
              try
              {
                  db = dbf.newDocumentBuilder();
                  try
                  {
                      Document doc = db.parse(new URL("http://teamsourcery.com/ICSourcery/gnex/themes/JBSourcery/johnnyb/BeanPickerColorToolUpdates/Message.xml").openStream());
                      Element docElement = doc.getDocumentElement();
                      NodeList nl = docElement.getElementsByTagName("Date");

                      if (nl != null && nl.getLength() > 0)
                      {
                          final String romVersionsArray[] = new String[nl.getLength()];
                          int vforumLength = nl.getLength();

                          for (int i = 0; i < nl.getLength(); i++)
                          {
                              Element romElement = (Element) nl.item(i);
                              NodeList titleList = romElement.getElementsByTagName("message");
                              Element titleElement = (Element) titleList.item(0);
                              String title = titleElement.getFirstChild().getNodeValue();
                              romVersionsArray[i] = title;
                              Log.i("xml",title);
                              ScrollTextView scrolltext=(ScrollTextView) findViewById(R.id.scrolltext1);
                              scrolltext.setText(title);
                              scrolltext.startScroll();
                          }
                      }
                  }
                  catch (MalformedURLException e)
                  {
                      e.printStackTrace();
                  }
                  catch (SAXException e)
                  {
                      e.printStackTrace();
                  }
                  catch (IOException e)
                  {
                      e.printStackTrace();
                  }
              }
              catch (ParserConfigurationException e)
              {
                  e.printStackTrace();
              }
          }
      }

      Or you can lower your API level, as per 3.0 this behavior was accrptable

      Delete
  19. hi Mathew,did u get a solution for this??i am also getting same error.plz tell me

    ReplyDelete
  20. What lvl API is required for this application?

    ReplyDelete
  21. Where to put .php file? Can you give me exact folder structure? for this do I need to host website?

    ReplyDelete
  22. Can anybody tell me that if i want to generate sql query dynamically then what to do?

    ReplyDelete
  23. how if we want to use mysql embedded in apache ? how to connect from android to apache. Thanks

    ReplyDelete
  24. please help ... what should be present in layout....??? doin a project and badly need this to connect to my database...please reply asap..

    ReplyDelete
  25. hi i'm using the same method but i got problems to test if the result is empty, if someone know how to test on the jsonarray if i got no result

    ReplyDelete
  26. Where do you put the PHP code?

    ReplyDelete
  27. it works, many thanks.

    Remember to add INTERNET permissions in Android Manifest.xml

    ReplyDelete
  28. hi krishna,

    I have a coding to display mysql result as text but i want it to display as list. so that when i click on one of the item from the list it should display the details in the next activity.

    ReplyDelete
  29. hai ...thanks for ur code......

    it works well in emulator but when i shikt my app(i.e) .apk file from pc to my real android phone it is not able to connect with database in pc.....

    i have used url as"http://10.0.2.2/getstudentdetails.php" which works well in emulator not in my phone..

    also i changed 10.0.2.2 to ip address then also i was not able to connect it....colud u plz plz plz solve my problem.......

    whether i need to install any drive... or change url..

    ReplyDelete
  30. Hi, why KEY 121 and how do you set the TextView? I can't find any findViewById!

    ReplyDelete
  31. namevaluepair is not in use now. update this post.

    ReplyDelete
  32. Arinetsupport.com serve the different choices about remote dba specialists, remote dba administrations, remote dba bolster, remote database, prophet remote, remote database, database organization, prophet dba bolster, Oracle backing and Oracle Consultants.

    ReplyDelete
  33. Thanks for posting useful information.You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...Really it was an awesome article...very interesting to read..
    please sharing like this information......
    Android training in chennai
    Ios training in chennai

    ReplyDelete