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.

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