Connect Android and Mysql via PHP Webservice Part 1

Hi Readers

I'll explain how to connect an Android application to a mysql database via PHP web service.
First we'll need to create the database which we are going to access via the PHP service.

Here the application is simple we are going to make a simple employee system where the Manager logs in and see all the details of employees who are managed by him.

--Create the database
CREATE DATABASE EmployeeDB;

--Select the database
USE EmployeeDB;

--Create the table
CREATE Table Employees
(
 ID int PRIMARY KEY AUTO_INCREMENT,
 Username varchar(20) UNIQUE,
 Password varchar(8),
 Name varchar(40),
 Address varchar(50),
 Manager int references Employees(ID)
);

Now we'll insert some values

Insert into Employees(username,password,name,address)
 values ('guru','123','Guruparan','Colombo');

Insert into Employees (username,password,name,address,manager)
 values ('saman','123','saman','colombo',1);

Insert into Employees (username,password,name,address,manager)
 values ('john','123','john','New York',1);

Insert into Employees(username,password,name,address,manager)
 values ('sean','123','sean','Washington',1);

Now we'll make the PHP web service which generates the JSON response The explanation is given via inline comments

<?php

 //Get the name of the Method
 //The method name has to be passed as Method via post

 $Request_Method=$_REQUEST['method'] or die('Method name not found');

 //Connect to the database
 $Connection = mysql_connect("localhost","root","") or die('Cannot connect to Database');

 //Select the database
 mysql_select_db("EmployeeDB") or die('Cannot select Database');

 //Method to verify the users login
 if($Request_Method=="verifyLogin")
 {
  //username and password are password are passed via querystrings
  $username=$_REQUEST['username'];
 $password=$_REQUEST['password'];

 //Generate the sql query based on username and password
 $query="select id from Employees where username='$username' and password='$password'";

 //Execute the query
 $result = mysql_query($query);

 //Get the rowcount
 $rowcount= mysql_num_rows($result);

 //if the count is 0 then no matching rows are found
 if($rowcount==0)
 {
  echo json_encode(array('result'=>0));
 }
 //Else there is an employee with the given credentials
 else {
  $row = mysql_fetch_assoc($result);
  //Get and return his employee id
  echo json_encode(array('result'=>$row['id']));
 }
 }

 //Get all th employees that are managed the by the given emplyee
 if($Request_Method=="getEmployees")
 {
  $id=$_REQUEST['id'];
 $query="select name,address from Employees where manager=$id";

 $result = mysql_query($query);

 while($row = mysql_fetch_assoc($result))
 {
  $resultArray[] = $row;
 }

 echo json_encode($resultArray);
 }

 //Close Connection
 mysql_close($Connection);
?>

The Android application will be discussed in Part 2