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

-- 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 */;

<?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"; 
	}
?>

{"FirstName":"John","LastName":"Doe","Age":"25","Points":"61"}

ArrayList<NameValuePair> nameValuePairs = new ArrayList<NameValuePair>();
nameValuePairs.add(new BasicNameValuePair("FirstNameToSearch", strNameToSearch));

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

//setup variables
	$firstname = trim($_POST["FirstNameToSearch"]); // trim the string to remove spaces from beginning and end (never trust the user)
	
//connect to Database
	try // try will attempt a connection
	{		
		$con = PDO("mysql:host=localhost;dbname=mytestdatabase","username","password");
	}
	catch(PDOException $e) // catch an error if try fails, like wrong credentials or no db found
	{
		// stop execution and print the error
		die("Error: ". $e->getMessage()) 
	}
	
	// set PDO error mode to exception. errors will throw exceptions when encountered
	$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	// set charset, utf8 helps avoid issues
	$con->exec("SET NAMES utf8");

	// run your query and select only the columns that are needed, saves memory and makes understanding the database structure easier!
	$query = $con->prepare("SELECT FirstName, LastName, Age, Points FROM users WHERE FirstName = ? LIMIT 1"); 
	
	// bind the query variable to the first ?-token in the query
	$query->bindParam(1, $firstname); 
	
	// run the query
	$query->execute(); 

	echo json_encode($query->fetch()); // print the result encoded in json. fetch will return an array