I’m writing this because I've seen many people ask a variation of the question: “How can my app get information from a database?”
This guide is intended for those who have created their first app – it is assumed you have a working development environment and are reasonable comfortable with the Android SDK and Java. I'm also assuming little to no knowledge of PHP and MYSQL
This guide walks you through:
- Setting up a database and a PHP script
- Testing the server
- Accessing it from Android.
- Android Device*
- Apache/PHP/MySQL Server – I use WAMP (for Windows) (PHP v 5.4)
- Postman Rest Client for Google Chrome
This guide will help you setup a local server. If you want to host your script and database online, you will have to purchase paid hosting.
Let's get started!
First off, what is a RESTful service?
According to Wikipedia: A RESTful web API (also called a RESTful web service) is a web API implemented using HTTP and REST principles.
How it works:
A breakdown of the steps:
- The client makes a request using a HTTP POST to a server
- The PHP script queries the MYSQL server
- The PHP script gets the SQL data
- The app parses the JSON and displays the data.
Part 1: The Server
We’re going to start by setting up the server!
Install WAMP server. Leave the settings at the default values.
Start WAMP server and let it come online.
Try and open http://localhost/phpmyadmin/ - if you installed it correctly, you should be greeted by the phpMyAdmin welcome screen. We're going to be using phpMyAdmin to create our database.
Creating the Database:
Create a database called ‘mytestdatabase’. Now click the SQL tab, paste in the following SQL Code and hit run. This will create a test table called ‘users’ and fill it with data.
The table contains 5 columns: id, FirstName, LastName, Age, Points. It has 6 rows of sample data.
-- phpMyAdmin SQL Dump -- version 3.5.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Jun 15, 2013 at 10:07 PM -- Server version: 5.5.24-log -- PHP Version: 5.3.13 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @old_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @old_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @old_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `MyTestDatabase` -- -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `FirstName` text NOT NULL, `LastName` text NOT NULL, `Age` int(11) NOT NULL, `Points` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `users` -- INSERT INTO `users` (`id`, `FirstName`, `LastName`, `Age`, `Points`) VALUES (1, 'John', 'Doe', 25, 61), (2, 'Glen', 'Willis', 55, 3145), (3, 'Helen', 'Cook', 35, 1232), (4, 'Karen', 'Johnson', 20, 6456), (5, 'Bill', 'Cooper', 60, 3856), (6, 'Mary', 'Gomez', 30, 5422); /*!40101 SET CHARACTER_SET_CLIENT @old_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS @old_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION @old_COLLATION_CONNECTION */;
We’re now ready to move on to the PHP!
Open up your WWW directory (C:\wamp\www) and create a new folder called ‘clientservertest’. In this folder, create a file called ‘login.php’.
Paste the following code into the file. (The PHP code is commented so you can follow what is going on)
Testing the Script:
Try accessing http://localhost/clientservertest/login.php from your browser. Do you get this message:
"Could not complete query. Missing parameter"
Then it’s working! The script is looking for a POST variable called “FirstNameToSearch” – we didn't provide any, so it did't work!
To finish testing the script, open the Postman-REST client.
Set it up like so:
Request URL: http://localhost/clientservertest/login.php
Hit send, and you should see this:
Note: Before we move on to the Android section, we’re going to have to put our WAMP server online. Click the WAMP icon in the taskbar and select 'Put Online'.
Find your computers local network IP address and insert it into the URL like so: http://192.168.1.112/clientservertest/login.php
You should be able to access the script. If this doesn't work, try turning off your firewall - it could be blocking the server.
Part 2: Android
We’re now going to use our Android device to access the web server instead of the Postman client.
I'm not going to go into detail with the boilerplate UI code - I've attached the source code to this post so you can download the project files and browse through them.
Note: Android 3.x+ cannot perform Network operations on the main thread. To solve this, we have to multithread our program. To keep this as simple as possible, we’re going to use an AsyncTask. Again, the code for this can be found in the project download.
Inside of the AsyncTask, we have the most important code - where we create and execute a HTTP POST in Java.
Creating and Executing a HTTP POST in Java:
We have to first setup the name-value pairs for our POST variables. In this case, we use "FirstNameToSearch" as our Key.
ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>(); nameValuePairs.add(new BasicNameValuePair("FirstNameToSearch", strNameToSearch));
The following code sets up connection timeouts (15 seconds) and creates a HttpClient and HttpPost pointing to our url (http://192.168.1.112/clientservertest/login.php)
//Create the HTTP request HttpParams httpParameters = new BasicHttpParams(); //Setup timeouts HttpConnectionParams.setConnectionTimeout(httpParameters, 15000); HttpConnectionParams.setSoTimeout(httpParameters, 15000); HttpClient httpclient = new DefaultHttpClient(httpParameters); HttpPost httppost = new HttpPost("http://192.168.1.112/clientservertest/login.php"); httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
HttpResponse response = httpclient.execute(httppost); HttpEntity entity = response.getEntity(); String result = EntityUtils.toString(entity);
// Create a JSON object from the request response JSONObject jsonObject = new JSONObject(result); //Retrieve the data from the JSON object strFirstName = jsonObject.getString("FirstName"); strLastName = jsonObject.getString("LastName"); intAge = jsonObject.getInt("Age"); intPoints = jsonObject.getInt("Points");
That's it. It's so simple!
Where do we take it from here?
This combination of PHP/MYSQL is quite powerful. I'd recommend that you learn more about these technologies and build upon the demo in this guide. PHP Tutorials & MySQL Tutorials
Ideas for practice apps:
- Online notes application - Sync your notes to the cloud
- Build an Activation Server - Users can activate an app with a key
Please feel free to leave any followup questions, comments or suggestions! I'll try my best to respond!
You can find the source code over at GitHub. Have fun! (If you fix a bug, please send a pull request)