1. What is WAMP Server

WAMP is acronym for Windows, Apache, MySQL and PHP, Perl, Python. WAMP software is one click installer which creates an environment for developing PHP, MySQL web application. By installing this software you will be installing ApacheMySQL and PHP. Alternatively you can use XAMP Server also.

wamp server installation

2. Installing and Running WAMP Server

Download & Install WAMP server from www.wampserver.com/en/. Once you have installed wamp server, launch the program from Start -> All Programs -> WampServer -> StartWampServer.

You can test your server by opening the address http://localhost/ in your browser.
Also you can check phpmyadmin by opening http://localhost/phpmyadmin

Following is a screen cast of Downloading and Installing WAMP Server.

3. Creating and Running PHP Project

Now you have the environment ready to develop a PHP & MySQL project. Go to the location where you installed WAMP server (In my case i installed in C:\wamp\) and go to www folder and create a new folder for your project. You have to place all your project files inside this folder.

Create a folder called android_connect and create a new php file called test.php and try out simple php code. After placing following code try to openhttp://localhost/android_connect/test.php and you should see a message called “Welcome, I am connecting Android to PHP, MySQL“.

test.php

<?php
    echo "Welcome, I am connecting Android to PHP, MySQL";
?>

Following is a screen cast of Creating and Running a simple PHP project.

4. Creating MySQL Database and Tables

In this tutorial i am creating a simple database with one table. Through out this tutorial i am using same table to perform example operations. Now open phpmyadmin by opening the address http://localhost/phpmyadmin/ in your browser. You can use the PhpMyAdmin tool to create a database and a table.

I am creating a database named androidhive and a table called products.

CREATE DATABASE androidhive;
CREATE TABLE products(
pid int(11) primary key auto_increment,
name varchar(100) not null,
price decimal(10,2) not null,
description text,
created_at timestamp default now(),
updated_at timestamp
);

Following is a screen cast of Creating database and tables in phpmyadmin

5. Connecting to MySQL database using PHP

Now the actual server side coding starts. Create a PHP class to connect to MySQL database. The main purpose of this class is to open a connection to database and close the connection whenever its not needed. So create two files called db_config.php and db_connect.php

db_config.php – will have database connection variables
db_connect.php – a class file to connect to database

Following is code for two php files

db_config.php

db_config.php
<?php
 
/*
 * All database connection variables
 */
 
define('DB_USER', "root"); // db user
define('DB_PASSWORD', ""); // db password (mention your db password here)
define('DB_DATABASE', "androidhive"); // database name
define('DB_SERVER', "localhost"); // db server
?>

db_connect.php

db_connect.php
<?php
 
/**
 * A class file to connect to database
 */
class DB_CONNECT {
 
    // constructor
    function __construct() {
        // connecting to database
        $this->connect();
    }
 
    // destructor
    function __destruct() {
        // closing db connection
        $this->close();
    }
 
    /**
     * Function to connect with database
     */
    function connect() {
        // import database connection variables
        require_once __DIR__ . '/db_config.php';
 
        // Connecting to mysql database
        $con = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD) or die(mysql_error());
 
        // Selecing database
        $db = mysql_select_db(DB_DATABASE) or die(mysql_error()) or die(mysql_error());
 
        // returing connection cursor
        return $con;
    }
 
    /**
     * Function to close db connection
     */
    function close() {
        // closing db connection
        mysql_close();
    }
 
}
 
?>

Usage: When ever you want to connect to MySQL database and do some operations use the db_connect.php class like this

$db = new DB_CONNECT(); // creating class object(will open database connection)

6. Basic MySQL CRUD Operations using PHP

In this tutorial i am covering basic CRUD (Create, Read, Update, Delete) operations on MySQL database using PHP.
If you are a novice about PHP and MySQL i suggest, you to learn basic PHP and SQL here.

6.a) Creating a row in MySQL (Creating a new product row)

In your PHP project create a new php file called create_product.php and place the following code. This file is mainly for creating a new product in products table.

In the following code i am reading product data via POST and storing them in products table. At the end i am echoing appropriate JSON as response.

create_product.php
<?php
 
/*
 * Following code will create a new product row
 * All product details are read from HTTP Post Request
 */
 
// array for JSON response
$response = array();
 
// check for required fields
if (isset($_POST['name']) && isset($_POST['price']) && isset($_POST['description'])) {
 
    $name = $_POST['name'];
    $price = $_POST['price'];
    $description = $_POST['description'];
 
    // include db connect class
    require_once __DIR__ . '/db_connect.php';
 
    // connecting to db
    $db = new DB_CONNECT();
 
    // mysql inserting a new row
    $result = mysql_query("INSERT INTO products(name, price, description) VALUES('$name', '$price', '$description')");
 
    // check if row inserted or not
    if ($result) {
        // successfully inserted into database
        $response["success"] = 1;
        $response["message"] = "Product successfully created.";
 
        // echoing JSON response
        echo json_encode($response);
    } else {
        // failed to insert row
        $response["success"] = 0;
        $response["message"] = "Oops! An error occurred.";
 
        // echoing JSON response
        echo json_encode($response);
    }
} else {
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";
 
    // echoing JSON response
    echo json_encode($response);
}
?>

For the above code JSON response will be like

When POST param(s) is missing

{
    "success": 0,
    "message": "Required field(s) is missing"
}

When product is successfully created

{
    "success": 1,
    "message": "Product successfully created."
}

When error occurred while inserting data

{
    "success": 0,
    "message": "Oops! An error occurred."
}

6.b) Reading a Row from MySQL (Reading product details)

Create a new php file called get_product_details.php and write the following code. This file will get single product details by taking product id (pid) as post parameter.

get_product_details.php
<?php
 
/*
 * Following code will get single product details
 * A product is identified by product id (pid)
 */
 
// array for JSON response
$response = array();
 
// include db connect class
require_once __DIR__ . '/db_connect.php';
 
// connecting to db
$db = new DB_CONNECT();
 
// check for post data
if (isset($_GET["pid"])) {
    $pid = $_GET['pid'];
 
    // get a product from products table
    $result = mysql_query("SELECT *FROM products WHERE pid = $pid");
 
    if (!empty($result)) {
        // check for empty result
        if (mysql_num_rows($result) > 0) {
 
            $result = mysql_fetch_array($result);
 
            $product = array();
            $product["pid"] = $result["pid"];
            $product["name"] = $result["name"];
            $product["price"] = $result["price"];
            $product["description"] = $result["description"];
            $product["created_at"] = $result["created_at"];
            $product["updated_at"] = $result["updated_at"];
            // success
            $response["success"] = 1;
 
            // user node
            $response["product"] = array();
 
            array_push($response["product"], $product);
 
            // echoing JSON response
            echo json_encode($response);
        } else {
            // no product found
            $response["success"] = 0;
            $response["message"] = "No product found";
 
            // echo no users JSON
            echo json_encode($response);
        }
    } else {
        // no product found
        $response["success"] = 0;
        $response["message"] = "No product found";
 
        // echo no users JSON
        echo json_encode($response);
    }
} else {
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";
 
    // echoing JSON response
    echo json_encode($response);
}
?>

The json response for the above file will be

When successfully getting product details

{
    "success": 1,
    "product": [
        {
            "pid": "1",
            "name": "iPHone 4S",
            "price": "300.00",
            "description": "iPhone 4S white",
            "created_at": "2012-04-29 01:41:42",
            "updated_at": "0000-00-00 00:00:00"
        }
    ]
}

When no product found with matched pid

{
    "success": 0,
    "message": "No product found"
}

6.c) Reading All Rows from MySQL (Reading all products)

We need a json to list all the products on android device. So create a new php file namedget_all_products.php and write following code.

get_all_products.php
<?php
 
/*
 * Following code will list all the products
 */
 
// array for JSON response
$response = array();
 
// include db connect class
require_once __DIR__ . '/db_connect.php';
 
// connecting to db
$db = new DB_CONNECT();
 
// get all products from products table
$result = mysql_query("SELECT *FROM products") or die(mysql_error());
 
// check for empty result
if (mysql_num_rows($result) > 0) {
    // looping through all results
    // products node
    $response["products"] = array();
 
    while ($row = mysql_fetch_array($result)) {
        // temp user array
        $product = array();
        $product["pid"] = $row["pid"];
        $product["name"] = $row["name"];
        $product["price"] = $row["price"];
        $product["created_at"] = $row["created_at"];
        $product["updated_at"] = $row["updated_at"];
 
        // push single product into final response array
        array_push($response["products"], $product);
    }
    // success
    $response["success"] = 1;
 
    // echoing JSON response
    echo json_encode($response);
} else {
    // no products found
    $response["success"] = 0;
    $response["message"] = "No products found";
 
    // echo no users JSON
    echo json_encode($response);
}
?>

And the JSON response for above code

Listing all Products

{
    "products": [
        {
            "pid": "1",
            "name": "iPhone 4S",
            "price": "300.00",
            "created_at": "2012-04-29 02:04:02",
            "updated_at": "0000-00-00 00:00:00"
        },
        {
            "pid": "2",
            "name": "Macbook Pro",
            "price": "600.00",
            "created_at": "2012-04-29 02:04:51",
            "updated_at": "0000-00-00 00:00:00"
        },
        {
            "pid": "3",
            "name": "Macbook Air",
            "price": "800.00",
            "created_at": "2012-04-29 02:05:57",
            "updated_at": "0000-00-00 00:00:00"
        },
        {
            "pid": "4",
            "name": "OS X Lion",
            "price": "100.00",
            "created_at": "2012-04-29 02:07:14",
            "updated_at": "0000-00-00 00:00:00"
        }
    ],
    "success": 1
}

When products not found

{
    "success": 0,
    "message": "No products found"
}

6.d) Updating a Row in MySQL (Updating product details)

Create a php file named update_product.php to update product details. Each product is identified by pid.

update_product.php
<?php
 
/*
 * Following code will update a product information
 * A product is identified by product id (pid)
 */
 
// array for JSON response
$response = array();
 
// check for required fields
if (isset($_POST['pid']) && isset($_POST['name']) && isset($_POST['price']) && isset($_POST['description'])) {
 
    $pid = $_POST['pid'];
    $name = $_POST['name'];
    $price = $_POST['price'];
    $description = $_POST['description'];
 
    // include db connect class
    require_once __DIR__ . '/db_connect.php';
 
    // connecting to db
    $db = new DB_CONNECT();
 
    // mysql update row with matched pid
    $result = mysql_query("UPDATE products SET name = '$name', price = '$price', description = '$description' WHERE pid = $pid");
 
    // check if row inserted or not
    if ($result) {
        // successfully updated
        $response["success"] = 1;
        $response["message"] = "Product successfully updated.";
 
        // echoing JSON response
        echo json_encode($response);
    } else {
 
    }
} else {
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";
 
    // echoing JSON response
    echo json_encode($response);
}
?>

The json reponse of above code, when product is updated successfully

{
    "success": 1,
    "message": "Product successfully updated."
}

6.e) Deleting a Row in MySQL (Deleting a product)

The last operation is deletion on database. Create a new php file called delete_product.php and paste the following code. The main functionality of this file is to delete a product from database.

delete_product.php
<?php
 
/*
 * Following code will delete a product from table
 * A product is identified by product id (pid)
 */
 
// array for JSON response
$response = array();
 
// check for required fields
if (isset($_POST['pid'])) {
    $pid = $_POST['pid'];
 
    // include db connect class
    require_once __DIR__ . '/db_connect.php';
 
    // connecting to db
    $db = new DB_CONNECT();
 
    // mysql update row with matched pid
    $result = mysql_query("DELETE FROM products WHERE pid = $pid");
 
    // check if row deleted or not
    if (mysql_affected_rows() > 0) {
        // successfully updated
        $response["success"] = 1;
        $response["message"] = "Product successfully deleted";
 
        // echoing JSON response
        echo json_encode($response);
    } else {
        // no product found
        $response["success"] = 0;
        $response["message"] = "No product found";
 
        // echo no users JSON
        echo json_encode($response);
    }
} else {
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";
 
    // echoing JSON response
    echo json_encode($response);
}
?>

When product successfully deleted

{
    "success": 1,
    "message": "Product successfully deleted"
}

When product not found

{
    "success": 0,
    "message": "No product found"
}

Until now, we built a simple api for our products table. We are now done with the server side coding (PHP) and its time to take a break and start our actual android application coding.

+ Recent posts