Skip to content
September 24, 2010 / Daniel Freeman

Dr Android Answers: PHP and mySQL

Following on from WCs questions, I appreciate that the Android course didn’t go into any detail storing data on the server.  It was a Android course after all, not a server technology course.  Having said that, I always thought an overview of PHP and mySQL would be beneficial to a lot of students, who had no prior experience in server-side code.  So here it is, in a nutshell:-

I need to update the data on weekly basis.
May i know where the data can be stored? Does google provide a server to host the data?
My apps is about the daily consumer good pricing.
WC

It’s up to you to host your data.  Personally, I use a free hosting service provided by http://www.byethost.net.  Free hosts like this come with the ability to run PHP, and set up mySQL databases.

Let’s start with a simple PHP program:

<html>
<body>
<?php echo "Hello World!"; ?>
</body>
</html>

You can mix up PHP with HTML, as above. The parts of the program that do PHP things are enclosed by <?   ?> tags.  For example, the above program prints (echoes) “Hello World”.

PHP can execute commands to query an SQL database.  You will have already learnt some SQL language on the Android course.  This was related to Android’s SQLlite.  Here is a program to set up a database table, and populate it with some records:-

<?php
mysql_connect("localhost","root","password");
mysql_select_db("");
mysql_query("DROP DATABASE DEMO;");
mysql_query("CREATE DATABASE DEMO;");
mysql_select_db("DEMO");
mysql_query("CREATE TABLE PEOPLE(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,NAME TEXT,AGE INT);");
mysql_query("INSERT INTO PEOPLE (NAME,AGE) VALUES ('Albert Smith',32);");
mysql_query("INSERT INTO PEOPLE (NAME,AGE) VALUES ('Justin Archer',28);");
mysql_query("INSERT INTO PEOPLE (NAME,AGE) VALUES ('Sarah Tully',35);");
mysql_query("INSERT INTO PEOPLE (NAME,AGE) VALUES ('Janet Brown',30);");
mysql_query("INSERT INTO PEOPLE (NAME,AGE) VALUES ('Robert Jones',26);");
mysql_query("INSERT INTO PEOPLE (NAME,AGE) VALUES ('Alice Wendell',38);");
?>

As you can see, this program CREATEs a new TABLE, and INSERTs some records.

The strings that you pass to mysql_connect will relate to the details of the database you set up. You’ll need to set up a database first from the web host control panel. This is quite straight forward usually – but your web host probably has a user discussion forum, and there’s much more help and information on the internet about PHP and mySQL than there is for Android – so you should be able to sort this out yourself.

The following PHP program reads the database, and spits out the data as XML.

<?php
mysql_connect("localhost","root","password");
mysql_select_db("DEMO");
$query="SELECT * FROM PEOPLE;";
$result=mysql_query($query) or die("error:".$query);
header('Content-type: application/xml; charset="utf-8"',true);
echo "<table>\n";
while ($row=mysql_fetch_array($result)) {
	echo "<record>\n";
	echo "<id>".$row['ID']."</id>\n";
	echo "<name>".$row['NAME']."</name>\n";
	echo "<age>".$row['AGE']."</age>\n";
	echo "</record>\n";
	}
echo "</table>\n";
?>

There are other ways to manipulate XML in PHP, but strings, and echo are easy to understand.
Finally, sometimes, you’ll need to pass data from the Android phone to the server. Like this:-

public void postData() {

HttpClient httpclient = new DefaultHttpClient();
HttpPost httppost = new HttpPost("http://www.yoursite.com/insert.php");

try {
        List<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>(2);
        nameValuePairs.add(new BasicNameValuePair("name", "Fred Bloggs"));
        nameValuePairs.add(new BasicNameValuePair("age", "45"));
        httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));

        HttpResponse response = httpclient.execute(httppost);

       } catch (ClientProtocolException e) {

       } catch (IOException e) {
   }
}

And insert.php looks like this:-

<?php
mysql_connect("localhost","root","password");
mysql_select_db("DEMO");
$name = $_POST['name'];
$age = $_POST['age'];
mysql_query("INSERT INTO PEOPLE (NAME,AGE) VALUES ('$name',$age);");
?>

$_POST[label] is used to retrieve the values sent by the Android device.

About these ads

16 Comments

Leave a Comment
  1. Mat Yus / Jun 12 2011 9:56 pm

    why HttpResponse response = httpclient.execute(httppost); is never read?
    and i never try insert.php but i change the command to update command and it give the null value to my database,why? this is my code

    .java

    HttpClient httpclient = new DefaultHttpClient();
    HttpPost httppost = new HttpPost(“http://*******.com/mobileproject/updateclass.php”);
    try {
    List nameValuePairsUpdate = new ArrayList(2);
    nameValuePairsUpdate.add(new BasicNameValuePair(“code”, aCode[i]));
    nameValuePairsUpdate.add(new BasicNameValuePair(“section”, aSection[i]));
    nameValuePairsUpdate.add(new BasicNameValuePair(“registered”, Integer.toString(registered)));
    nameValuePairsUpdate.add(new BasicNameValuePair(“empty”, Integer.toString(empty)));
    nameValuePairsUpdate.add(new BasicNameValuePair(“status”, status));
    httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));

    HttpResponse response = httpclient.execute(httppost);

    } catch (ClientProtocolException e) {
    } catch (IOException e) {
    }

    .php

    what is the problem?

    • Mat Yus / Jun 12 2011 9:58 pm

      .php

      what is the problem?

  2. Mat Yus / Jun 12 2011 10:00 pm

    why HttpResponse response = httpclient.execute(httppost); is never read?
    and i never try insert.php but i change the command to update command and it give the null value to my database,why? this is my code

    .java

    HttpClient httpclient = new DefaultHttpClient();
    HttpPost httppost = new HttpPost(“http://*******.com/mobileproject/updateclass.php”);
    try {
    List nameValuePairsUpdate = new ArrayList(2);
    nameValuePairsUpdate.add(new BasicNameValuePair(“code”, aCode[i]));
    nameValuePairsUpdate.add(new BasicNameValuePair(“section”, aSection[i]));
    nameValuePairsUpdate.add(new BasicNameValuePair(“registered”, Integer.toString(registered)));
    nameValuePairsUpdate.add(new BasicNameValuePair(“empty”, Integer.toString(empty)));
    nameValuePairsUpdate.add(new BasicNameValuePair(“status”, status));
    httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));

    HttpResponse response = httpclient.execute(httppost);

    } catch (ClientProtocolException e) {
    } catch (IOException e) {
    }

    .php
    //

    what is the problem?

    • Daniel Freeman / Jun 12 2011 11:25 pm

      Can you show me updateclass.php ? If you never tried insert.php – (and you never created and inserted records into your table) – how can you update it?

      • Mat Yus / Jun 13 2011 5:15 am

        this is my updateclass.php

        mysql_connect(“localhost”,”******”,”********”);

        mysql_select_db(“*******”);

        $registered = $_POST['registered'];
        $empty = $_POST['empty'];
        $status = $_POST['status'];
        $code = $_POST['code'];
        $section = $_POST['section'];

        mysql_query(“UPDATE Class SET registered = ‘$registered’ , empty = ‘$empty’ , status = ‘$status’ WHERE code = ‘$code’ AND section = ‘$section’”);

        i already create the database and table in phpmyadmin..from the initial,all the column in class table have values,but when i klik update button,the values of registered,empty and status column disappeared,why?

  3. Sharing / Aug 24 2011 1:10 pm

    thanks so much, this post very helpfull for me !!!

  4. Fredrick / Nov 29 2011 1:47 pm

    Thanks for this post. It has been really helpful and it works like a dream. I am trying to do the opposite. Saving the data from PHP into the android database. I already have all the code upto parsing data in android. But I need to save it as well.
    Can you please help with that?

    • Daniel Freeman / Nov 30 2011 1:44 pm

      Do you know about using SQLiteOpenHelper?

      Assuming you’ve subclassed this to create your database table, you could write something like this:-

      MyDBHelper myDBHelper = new MyDBHelper(this);
      SQLiteDatabase db = myDBHelper.getReadableDatabase();
      db.rawQuery(“INSERT INTO myTable (myField1, myField2) VALUES (‘”+parsedVal1+”‘,’”+parsedVal2+”‘);”, null);

      Does this help?

      • Fredrick / Nov 30 2011 4:41 pm

        I will give that a go tomorrow, I created a test app here ( http://stackoverflow.com/questions/8091882/saving-data-from-php-to-android-database ) some time ago but no-one could help me. All the code for creating the database etc is there as well but It is that part of (‘”+parseVal1+”‘) i cant make work. I am really desperate to get this working.

      • Fredrick / Dec 1 2011 12:41 pm

        //parse json data
        try{
        JSONArray jArray = new JSONArray(result);
        for(int i=0;i<jArray.length();i++){
        JSONObject json_data = jArray.getJSONObject(i);
        String qureylog ="INSERT OR REPLACE INTO people(refno, name, surename) " +
        "values('"+json_data.getString("refno")+"'" +
        ",'"+json_data.getString("name")+"'" +
        ",'"+json_data.getString("surename")+"')";
        dbA.insert(querylog);

        Then in dbA:

        public static void insert(Object querylog) {
        // TODO Auto-generated method stub
        // I need the code to insert the paring data into the database.
        dbA.querylog KEY_REFNO, KEY_NAME, KEY_SURENAME)
        VALUES ("refno","name","surename");”, null);

        As you can see, I am making a real @r$ of this.

      • Daniel Freeman / Dec 3 2011 4:56 pm

        Instead of db.insert, try db.rawQuery().

        I have another database example here:-

        http://madskool.wordpress.com/2010/09/29/dr-android-answers-saving-camera-pictures-to-sql-lite/

        Although I used insert, only because I was inserting BLOBs not text. But in your case, rawQuery is easier to debug, and Log the query to check that it looks ok.

      • Fredrick / Dec 5 2011 9:41 am

        I still cant figure it out. As far as I know, rawQuery reads from the database, But I need to insert it.
        If you have the time, can you give me a price an I will pay you to write it for me, because I just cant make it work. To keep it personal you can email me at xxxxxxxxxxxxxxxxxxxxxx . Looking forward to hear back from you.

        Thanks

      • Daniel Freeman / Dec 5 2011 10:00 am

        Doh! sorry, I meant db.execSQL(). I’m sure you can work it out with that. (So sorry for misleading you with rawQuery().) I only do larger commercial projects, but I’ll see if I dig out an Android SQL sample later and send it to you.

      • Fredrick / Dec 7 2011 9:57 am

        Makes more sense now:

        Ok in the reading JSON:

        //parse json data
        try{
        JSONArray jArray = new JSONArray(result);
        for(int i=0;i<jArray.length();i++){

        JSONObject json_data = jArray.getJSONObject(i);
        String qureylog =
        "INSERT OR REPLACE INTO people(refno, name, surename) " +
        "values('"+json_data.getString("refno")+"'" +
        ",'"+json_data.getString("name")+"'" +
        ",'"+json_data.getString("surename")+"')";
        dbA.execSQL(qureylog, result, returnString);
        }
        }
        catch(JSONException e){
        Log.e("log_tag", "Error parsing data"+e.toString());
        }
        }
        }

        AND in database Helper I got:

        public static void execSQL(String refno, String name, String surename) {
        ContentValues initialValues = createContentValues(refno, name, surename);
        }
        private static ContentValues createContentValues(String refno, String name,
        String surename) {
        // TODO Auto-generated method stub
        ContentValues values = new ContentValues();
        values.put(KEY_REFNO, refno);
        values.put(KEY_NAME, name);
        values.put(KEY_SURENAME, surename);
        return values;

        }

        }
        There is no errors but It does not save data into the SQLite database.

      • Fredrick / Dec 7 2011 1:16 pm

        I got it !!! YASSSS. Thanks allot man.

      • Fredrick / Dec 5 2011 9:48 am

        I meant xxxxxxxxxxxxxxxxx

To discuss MadComponents/MC3D, join the Facebook group!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 86 other followers

%d bloggers like this: