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