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

alobo said:

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

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

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],

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

Click to expand...

Click to collapse

FrozenRocker said:

Thank you for this great guide!

I have one more question. How to make program and script to update Age and Points for FirstName (ex. John)?
How can I do that?

Thanks in advance.

Click to expand...

Click to collapse

alobo said:

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      [user=714032]@old_[/user]CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET      [user=714032]@old_[/user]CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET      [user=714032]@old_[/user]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      [user=714032]@old_[/user]CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS      [user=714032]@old_[/user]CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION      [user=714032]@old_[/user]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:

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






 

Click to expand...

Click to collapse

Edieneo said:

I cant access my database through Postman? It shown "Could not complete query. Missing parameter" , I did everything, I change the type, put the URL and key also the value that 'John' as your tutorial.

Click to expand...

Click to collapse

Nikotiko said:

Did you really have to quote the WHOLE thread?

Click to expand...

Click to collapse