Post Reply

[GUIDE] Android Client-Server Communication (PHP-MYSQL REST API)

16th June 2013, 02:12 AM   |  #1  
OP Senior Member
Flag Waterloo
Thanks Meter: 213
 
163 posts
Join Date:Joined: Mar 2012
More
Hey XDA, this is my first guide and first proper contribution to the community!

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:
  1. Setting up a database and a PHP script
  2. Testing the server
  3. Accessing it from Android.
To make it relevant, we're going to use data that we might see in an actual app: First & Last Name, Age and Points.


Requirements:
  1. Android Device*
  2. Computer*
  3. Apache/PHP/MySQL Server – I use WAMP (for Windows) (PHP v 5.4)
  4. Postman Rest Client for Google Chrome
(*Both must be connected to the same network!)

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:
  1. The client makes a request using a HTTP POST to a server
  2. The PHP script queries the MYSQL server
  3. The PHP script gets the SQL data
  4. The PHP script puts the data into an array and assigns keys for the values. The script then outputs the data as a JSON array. JSON (JavaScript Object Notation) is a standard for data exchange, and formats the data in a way both humans and computers can easily read.
  5. The app parses the JSON and displays the data.

Code!

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.


SQL Code:
Code:
-- 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 */;
Your database should now look like this:




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)


PHP Code:
<?php

    
#Ensure that the client has provided a value for "FirstNameToSearch"
    
if (isset($_POST["FirstNameToSearch"]) && $_POST["FirstNameToSearch"] != ""){
        
        
#Setup variables
        
$firstname $_POST["FirstNameToSearch"];
        
        
#Connect to Database
        
$con mysqli_connect("localhost","root","""mytestdatabase");
        
        
#Check connection
        
if (mysqli_connect_errno()) {
            echo 
'Database connection error: ' mysqli_connect_error();
            exit();
        }

        
#Escape special characters to avoid SQL injection attacks
        
$firstname mysqli_real_escape_string($con$firstname);
        
        
#Query the database to get the user details.
        
$userdetails mysqli_query($con"SELECT * FROM users WHERE FirstName = '$firstname'");

        
#If no data was returned, check for any SQL errors
        
if (!$userdetails) {
            echo 
'Could not run query: ' mysqli_error($con);
            exit;
        }

        
#Get the first row of the results
        
$row mysqli_fetch_row($userdetails);

        
#Build the result array (Assign keys to the values)
        
$result_data = array(
            
'FirstName' => $row[1],
            
'LastName' => $row[2],
            
'Age' => $row[3],
            
'Points' => $row[4],
            );

        
#Output the JSON data
        
echo json_encode($result_data); 
    }else{
        echo 
"Could not complete query. Missing parameter"
    }
?>


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
Type: POST
Key: FirstNameToSearch
Value: John

Hit send, and you should see this:
Code:
{"FirstName":"John","LastName":"Doe","Age":"25","Points":"61"}
Congrats – your server just returned a result! Try some of the other names in the database (Glen, Helen, Karen, Bill, Mary) and see how their data is returned.

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.

Code:
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)

Code:
//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));
The following code executes the POST, gets the result and converts it to a string:

Code:
HttpResponse response = httpclient.execute(httppost);
HttpEntity entity = response.getEntity();

String result = EntityUtils.toString(entity);
Finally, the following code creates a JSON object from the result string and extracts our data:

Code:
// 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


Feedback
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)


Attached Files
File Type: zip ClientServerRESTDemo.zip - [Click for QR Code] (913.8 KB, 1745 views)
File Type: zip ClientServerRESTDemo v2.zip - [Click for QR Code] (914.2 KB, 2404 views)
Last edited by alobo; 3rd November 2013 at 09:27 PM.
The Following 36 Users Say Thank You to alobo For This Useful Post: [ View ]
16th June 2013, 02:13 AM   |  #2  
OP Senior Member
Flag Waterloo
Thanks Meter: 213
 
163 posts
Join Date:Joined: Mar 2012
More
Additional Information
Changelog

November 3, 2013
  • Added a link to the GitHub repository.

June 26, 2013
  • Updated PHP Code. It's more reliable and uses the newer MySQL APIs. Thanks to @dbarrera & @vijai2011

July 7, 2013
  • Updated the Android project and added Internet permissions (ClientServerRESTDemo v2.zip)
Last edited by alobo; 3rd November 2013 at 09:28 PM.
The Following 2 Users Say Thank You to alobo For This Useful Post: [ View ]
16th June 2013, 07:53 AM   |  #3  
Recognized Developer
Flag chennai
Thanks Meter: 460
 
943 posts
Join Date:Joined: Oct 2011
Donate to Me
More
Thanks for this amazing guide but I have a issue.I have my own table and columns.So I changed your php code according to that and when I do a post query in rest,I get all null.But if I do the same in phpmyadmin,I have results.
This is what I get in postman:
Code:
{"pid":null,"name":null,"UID":null,"mobile":null,"description":null,"created_at":null,"updated_at":null}
attached the output of phpmyadmin.

And my phpcode:
Code:
<?php

	#Setup variables
	$firstname = $_POST["FirstNameToSearch"];

	#Avoid SQL injection attacks
	$firstname = mysql_real_escape_string($firstname);
	
	#Connect to Database
	$con = mysql_connect("localhost","user","pass");
	if (!$con)
	{
		die('Could not connect');
	}

	#Select the test database
	mysql_select_db("mydb", $con);

	#Get the user details from the database
	$userdetails = mysql_query("SELECT * FROM mytable WHERE name = '$firstname'");

	#Catch any errors
	if (!$userdetails) {
		echo 'It seems the server is down.Please try later';
		exit;
	}

	#Get the first row of the results
	$row = mysql_fetch_row($userdetails);

	#Build the result array (Assign keys to the values)
	$result_data = array(
		'pid' => $row[1],
		'name' => $row[2],
		'UID' => $row[3],
		'mobile' => $row[4],
                'description' => $row[5],
                'created_at' => $row[6],
                'updated_at' => $row[7],
		);

	#Output the JSON data
	echo json_encode($result_data); 

?>
Thanks for help.

Edit: just found that your app is missing internet permission in manifest
Code:
<uses-permission android:name="android.permission.INTERNET" />
Attached Thumbnails
Click image for larger version

Name:	Screenshot from 2013-06-16 12:16:02.jpg
Views:	721
Size:	21.5 KB
ID:	2046452  
Last edited by vijai2011; 16th June 2013 at 08:14 AM.
16th June 2013, 03:15 PM   |  #4  
cyberbeast's Avatar
Junior Member
Thanks Meter: 0
 
5 posts
Join Date:Joined: Aug 2010
:thumbup:

Thanks so much for this. I will try it out soon.

Sent from my HTC Explorer A310e using xda app-developers app
Last edited by cyberbeast; 17th June 2013 at 05:13 AM.
16th June 2013, 04:27 PM   |  #5  
OP Senior Member
Flag Waterloo
Thanks Meter: 213
 
163 posts
Join Date:Joined: Mar 2012
More
Quote:
Originally Posted by vijai2011

Thanks for this amazing guide but I have a issue.I have my own table and columns.So I changed your php code according to that and when I do a post query in rest,I get all null.But if I do the same in phpmyadmin,I have results.
This is what I get in postman:

Code:
{"pid":null,"name":null,"UID":null,"mobile":null,"description":null,"created_at":null,"updated_at":null}
attached the output of phpmyadmin.

Thanks for help.


I was able to reproduce the null result - it means that the result was not available in the database. You'll want to double check the value you are passing to the script in Postman. (When I used 'FirstNameToSearch' and 'test' - I got a correct result. However, when I used 'testa', I got a null result.)


You can try adding this into the PHP script to catch this problem:


Code:
#Get the first row of the results
$row = mysql_fetch_row($userdetails);

#Check to see if a result was returned.
if(!$row){
	echo 'User does not exist';
	exit;
}

I also noticed a few things in your PHP script:
  1. In your screenshot, your table name appears to be 'Myapp', however in your PHP script, it looks like you are using 'mytable'
  2. When you build the result array at the end, you are trying to access a column that doesn't exist:


    This code tries to access an 8th column/index:
    Code:
    	'pid' => $row[1],
    	'name' => $row[2],
    	'UID' => $row[3],
    	'mobile' => $row[4],
            'description' => $row[5],
            'created_at' => $row[6],
            'updated_at' => $row[7],
    You only have seven columns, so it should be:

    Code:
    	'pid' => $row[0],
    	'name' => $row[1],
    	'UID' => $row[2],
    	'mobile' => $row[3],
            'description' => $row[4],
            'created_at' => $row[5],
            'updated_at' => $row[6],

Quote:
Originally Posted by vijai2011

Edit: just found that your app is missing internet permission in manifest

Code:
<uses-permission android:name="android.permission.INTERNET" />

Good catch - Thanks! I'll update the project asap.
16th June 2013, 05:44 PM   |  #6  
Recognized Developer
Flag chennai
Thanks Meter: 460
 
943 posts
Join Date:Joined: Oct 2011
Donate to Me
More
Quote:
Originally Posted by Alkonic

I was able to reproduce the null result - it means that the result was not available in the database. You'll want to double check the value you are passing to the script in Postman. (When I used 'FirstNameToSearch' and 'test' - I got a correct result. However, when I used 'testa', I got a null result.)

:snip:

Good catch - Thanks! I'll update the project asap.

That is not a error in table name because I just wanted to hide it out here but actually it got revealed in the screenshot .No issues will try your php code and correct my json array too.thanks


Sent from my GT-N7000 using xda app-developers app
17th June 2013, 04:52 AM   |  #7  
Recognized Developer
Flag chennai
Thanks Meter: 460
 
943 posts
Join Date:Joined: Oct 2011
Donate to Me
More
For some reason,the problem was caused by the mysql_real_escape_string.I commented that line and it is working now.
The Following User Says Thank You to vijai2011 For This Useful Post: [ View ]
17th June 2013, 05:13 AM   |  #8  
OP Senior Member
Flag Waterloo
Thanks Meter: 213
 
163 posts
Join Date:Joined: Mar 2012
More
Quote:
Originally Posted by vijai2011

For some reason,the problem was caused by the mysql_real_escape_string.I commented that line and it is working now.

Now that's interesting.. I've never experienced a problem with that before.

I took a look at the PHP docs and found that mysql_real_escape_string() is depreciated - that could be contributing to the problem. I'll investigate this further and adjust the guide as necessary.

Thanks for sharing your solution!.
The Following User Says Thank You to alobo For This Useful Post: [ View ]
17th June 2013, 05:32 AM   |  #9  
Recognized Developer
Flag chennai
Thanks Meter: 460
 
943 posts
Join Date:Joined: Oct 2011
Donate to Me
More
Quote:
Originally Posted by Alkonic

Now that's interesting.. I've never experienced a problem with that before.

I took a look at the PHP docs and found that mysql_real_escape_string() is depreciated - that could be contributing to the problem. I'll investigate this further and adjust the guide as necessary.

Thanks for sharing your solution!.

Maybe you are using ancient php module :P.BTW Can I also put data into tables using your php and slightly modifying "mysql_query" and using post?Or should I use put along with mysql_query?If later is the solution,could give me the snippet of how a put variable looks?because I dono php and I was waiting for someone to write this guide because before I was connecting to db with JDBS which isnt safe.Thanks and sorry for the trouble.

Edit: I got it to work like I said.But only issue is the created at and updated at time stamp which is not the part of php nor the app.I will correct it from mysql.Thanks once again.Will be happy to trouble you soon lol...No dont take it serious BTW
Last edited by vijai2011; 17th June 2013 at 05:36 AM.
17th June 2013, 05:39 AM   |  #10  
OP Senior Member
Flag Waterloo
Thanks Meter: 213
 
163 posts
Join Date:Joined: Mar 2012
More
Quote:
Originally Posted by vijai2011

Maybe you are using ancient php module :P.BTW Can I also put data into tables using your php and slightly modifying "mysql_query" and using post?Or should I use put along with mysql_query?If later is the solution,could give me the snippet of how a put variable looks?because I dono php and I was waiting for someone to write this guide because before I was connecting to db with JDBS which isnt safe.Thanks and sorry for the trouble.

Edit: I got it to work like I said.But only issue is the created at and updated at time stamp which is not the part of php nor the app.I will correct it from mysql.Thanks once again.Will be happy to trouble you soon lol...No dont take it serious BTW

Ha you beat me to it! I'll definitely try and add a section into the guide about updating tables. I appreciate your feedback on the guide, and I'm glad it helped you. Feel free to trouble me

Post Reply Subscribe to Thread

Tags
android, app, client server, mysql, php
Previous Thread Next Thread
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes


Top Threads in Java for Android App Development by ThreadRank