In this tutorial am going to explain how to build a simple supermarket price comparison application using android, PHP and Mysql. We are going to cover how to use android dependencies (libraries) to achieve some functionalities and we’ll also cover how to create a simple REST API using PHP and mysql.
At the end of the project, your app should look similar to this
CREATING MYSQL DATABASE & TABLES
Open phpmyadmin and execute below queries to create necessary database and table. Here we are creating only one table user to store user login information.
[php]CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(250) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`email` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`password_hash` text CHARACTER SET latin1 NOT NULL,
`supermarket` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`status` int(1) NOT NULL DEFAULT ‘1’,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
[/php]
Table products
Table Prices
Goto the location where wamp installed and open www folder. The default installation location of wamp would be C:/wamp. or C:/wamp64. Below is the final PHP project structure we are going to create in this article.
1. Go into www folder and create a folder named supermarket_api. This will be the root directory of our project.
2. In supermarket_api, create another directory named include. In this folder, we keep all the helper classes.
3. Now inside include, create a php file named Config.php and add below content. Replace the DB_USER andDB_PASSWORD values with your’s.
[php]<?php /** * Database configuration */
define(‘DB_USERNAME’, ‘root’);
define(‘DB_PASSWORD’, ”);
define(‘DB_HOST’, ‘localhost’);
define(‘DB_NAME’, ‘supermarket’);
?>[/php]
4. Create a class named DB_Connect.php in include and paste below code. In this class we handle opening and closing of database connection.
[php]<?php /** * Handling database connection
* * @author Benson * @link URL Tutorial link */
class DbConnect {
private $conn;
function __construct()
{ }
/** * Establishing database connection *
@return database connection handler */
function connect()
{
include_once dirname(__FILE__) . ‘/Config.php’;
// Connecting to mysql database
$this->conn = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
// Check for database connection error
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// returing connection resource
return $this->conn;
}
}
?>[/php]
5. Create DB_Handler.php inside include with below content. This file contains functions to store user in database, get user from database. You can also add methods like createUser, createProduct, delete user.
[php]<?php /** * Class to handle all db operations * This class will have CRUD methods for database tables * * @author Benson * @link URL Tutorial link */ class DbHandler { private $conn; function __construct() { require_once dirname(__FILE__) . ‘/DbConnect.php’; // opening db connection $db = new DbConnect(); $this->conn = $db->connect();
}
/* ————- `users` table method —————— */
/**
* Creating new user
* @param String $name User full name
* @param String $email User login email id
* @param String $password User login password
*/
public function createUser($name, $email, $password, $supermarket) {
require_once ‘PassHash.php’;
$response = array();
// First check if user already existed in db
if (!$this->isUserExists($email)) {
// Generating password hash
$password_hash = PassHash::hash($password);
// insert query
$stmt = $this->conn->prepare("INSERT INTO user(name, email, password_hash, supermarket, status) values(?, ?, ?, ?, 1)");
$stmt->bind_param("ssss", $name, $email, $password_hash, $supermarket);
$result = $stmt->execute();
$stmt->close();
// Check for successful insertion
if ($result) {
// User successfully inserted
return USER_CREATED_SUCCESSFULLY;
} else {
// Failed to create user
return USER_CREATE_FAILED;
}
} else {
// User with same email already existed in the db
return USER_ALREADY_EXISTED;
}
return $response;
}
/**
* Creating new user
* @param String $name User full name
* @param String $email User login email id
* @param String $password User login password
*/
public function createProduct($name, $description, $price, $supermarket, $image) {
$response = array();
// First check if product already existed in db
if (!$this->isProductExists($name)) {
$sql ="SELECT ProductId FROM products ORDER BY ProductId ASC";
$res = mysqli_query($this->conn,$sql);
$id = 0;
while($row = mysqli_fetch_array($res)){
$id = $row[‘ProductId’];
}
$path = "uploads/$id.png";
$path2 = "../uploads/$id.png";
$actualpath = "http://192.168.137.1/supermarket_api/$path";
// insert query
$stmt = $this->conn->prepare("INSERT INTO products(ProductName, Description, ProductPrice, supermarket, image) values(?, ?, ?, ?, ?)");
$stmt->bind_param("sssss", $name, $description, $price, $supermarket, $actualpath);
$result = $stmt->execute();
$stmt->close();
// Check for successful insertion
if ($result) {
// Product successfully inserted
// insert query supermarket prices
$stmt2 = $this->conn->prepare("INSERT INTO prices(ProductName, Price, Supermarket) values(?, ?, ?)");
$stmt2->bind_param("sss", $name, $price, $supermarket);
$result2 = $stmt2->execute();
$stmt2->close();
file_put_contents($path2,base64_decode($image));
if (!$result2){
return PRODUCT_CREATE_FAILED;
}
return PRODUCT_CREATED_SUCCESSFULLY;
} else {
// Failed to create product
return PRODUCT_CREATE_FAILED;
}
} else {
// product with same name already existed in the db
return PRODUCT_ALREADY_EXISTED;
}
return $response;
}
public function createProductPrice($name, $price, $supermarket) {
$response = array();
// insert query supermarket prices
$stmt = $this->conn->prepare("INSERT INTO prices(ProductName, Price, Supermarket) values(?, ?, ?)");
$stmt->bind_param("sss", $name, $price, $supermarket);
$result = $stmt->execute();
$stmt->close();
// Check for successful insertion
if ($result) {
return PRODUCT_CREATED_SUCCESSFULLY;
} else {
// Failed to create product
return PRODUCT_CREATE_FAILED;
}
return $response;
}
/**
* Checking user login
* @param String $email User login email id
* @param String $password User login password
* @return boolean User login status success/fail
*/
public function checkLogin($email, $password) {
// fetching user by email
$stmt = $this->conn->prepare("SELECT password_hash FROM user WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$stmt->bind_result($password_hash);
$stmt->store_result();
if ($stmt->num_rows > 0) {
// Found user with the email
// Now verify the password
$stmt->fetch();
$stmt->close();
if (PassHash::check_password($password_hash, $password)) {
// User password is correct
return TRUE;
} else {
// user password is incorrect
return FALSE;
}
} else {
$stmt->close();
// user not existed with the email
return FALSE;
}
}
/**
* Checking for duplicate user by email address
* @param String $email email to check in db
* @return boolean
*/
private function isUserExists($email) {
$stmt = $this->conn->prepare("SELECT id from user WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();
$stmt->store_result();
$num_rows = $stmt->num_rows;
$stmt->close();
return $num_rows > 0;
}
/**
* Checking for duplicate user by email address
* @param String $email email to check in db
* @return boolean
*/
private function isProductExists($name) {
$stmt = $this->conn->prepare("SELECT ProductId from products WHERE ProductName = ?");
$stmt->bind_param("s", $name);
$stmt->execute();
$stmt->store_result();
$num_rows = $stmt->num_rows;
$stmt->close();
return $num_rows > 0;
}
/**
* Fetching user by email
* @param String $email User email id
*/
public function getUserByEmail($email) {
$stmt = $this->conn->prepare("SELECT name, email, api_key, status, created_at, supermarket FROM user WHERE email = ?");
$stmt->bind_param("s", $email);
if ($stmt->execute()) {
// $user = $stmt->get_result()->fetch_assoc();
$stmt->bind_result($name, $email, $api_key, $status, $created_at, $supermarket);
$stmt->fetch();
$user = array();
$user["name"] = $name;
$user["email"] = $email;
$user["api_key"] = $api_key;
$user["status"] = $status;
$user["supermarket"] = $supermarket;
$user["created_at"] = $created_at;
$stmt->close();
return $user;
} else {
return NULL;
}
}
/**
* Fetching products
* @param String $page_id id of the products list
*/
public function getProduct($page_id) {
//Initially we show the data from 1st row that means the 0th row
$start = 0;
//Limit is 1 that means we will show 1 items at once
$limit = 2;
//Counting the total item available in the database
$total = mysqli_num_rows(mysqli_query($this->conn, "SELECT id from feed "));
//We can go atmost to page number total/limit
$page_limit = $total/$limit;
//If the page number is more than the limit we cannot show anything
if($page_id<=$page_limit){ //Calculating start for every given page number $start = ($page_id – 1) * $limit; //SQL query to fetch data of a range $sql = "SELECT * from feed limit $start, $limit"; //Getting result $result = mysqli_query($this->conn,$sql);
//Adding results to an array
$res = array();
while($row = mysqli_fetch_array($result)){
array_push($res, array(
"name"=>$row[‘name’],
"publisher"=>$row[‘publisher’],
"image"=>$row[‘image’])
);
}
//Displaying the array in json format
return json_encode($res);
}else{
return NULL;
}
}
/**
* Fetching Products
* @param String $page_id id of the Sellers list
*/
public function getProducts() {
$sql = "SELECT * from products order by DateAdded DESC LIMIT 7";
//Getting result
$result = mysqli_query($this->conn,$sql);
//Adding results to an array
$res = array();
while($row = mysqli_fetch_array($result)){
array_push($res, array(
"name"=>$row[‘ProductName’],
"description"=>$row[‘Description’],
"image"=>$row[‘image’],
"date"=>$row[‘DateAdded’]
)
);
}
//Displaying the array in json format
return json_encode($res);
}
public function getUsers() {
$sql = "SELECT * from user";
//Getting result
$result = mysqli_query($this->conn,$sql);
//Adding results to an array
$res = array();
while($row = mysqli_fetch_array($result)){
array_push($res, array(
"id"=>$row[‘id’],
"name"=>$row[‘name’],
"supermarket"=>$row[‘supermarket’],
"email"=>$row[’email’]
)
);
}
//Displaying the array in json format
return json_encode($res);
}
/**
* Fetching supermarket Products
* @param String $page_id id of the Sellers list
*/
public function getSupermarketProducts() {
$sql = "SELECT * from products";
//Getting result
$result = mysqli_query($this->conn,$sql);
//Adding results to an array
$res = array();
while($row = mysqli_fetch_array($result)){
array_push($res, array(
"productId"=>$row[‘ProductId’],
"name"=>$row[‘ProductName’],
"description"=>$row[‘Description’],
"image"=>$row[‘image’]
)
);
}
//Displaying the array in json format
return json_encode($res);
}
/**
* Fetching Sellers
* @param String $page_id id of the Sellers list
*/
public function getSellers($product_name) {
$sql = "SELECT * from prices where ProductName=’$product_name’ ";
//Getting result
$result = mysqli_query($this->conn,$sql);
//Adding results to an array
$res = array();
while($row = mysqli_fetch_array($result)){
array_push($res, array(
"supermarket"=>$row[‘Supermarket’],
"price"=>$row[‘Price’]
)
);
}
//Displaying the array in json format
return json_encode($res);
}
/**
* Deleting a task
* @param String $task_id id of the task to delete
*/
public function deleteUser($id) {
$stmt = $this->conn->prepare("DELETE FROM user WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();
$num_affected_rows = $stmt->affected_rows;
$stmt->close();
return $num_affected_rows > 0;
}
public function deleteproduct($id) {
$stmt = $this->conn->prepare("DELETE FROM products WHERE id = ?");
$stmt->bind_param("i", $id);
$stmt->execute();
$num_affected_rows = $stmt->affected_rows;
$stmt->close();
return $num_affected_rows > 0;
}
}
?>
[/php]
6. Create PassHash.php This will be used to encrypt and decrypt user password stored in mysql
[php]<?php class PassHash { // blowfish private static $algo = ‘$2a’; // cost parameter private static $cost = ‘$10’; // mainly for internal use public static function unique_salt() { return substr(sha1(mt_rand()), 0, 22); } // this will be used to generate a hash public static function hash($password) { return crypt($password, self::$algo . self::$cost . ‘$’ . self::unique_salt()); } // this will be used to compare a password against a hash public static function check_password($hash, $password) { $full_salt = substr($hash, 0, 29); $new_hash = crypt($password, $full_salt); return ($hash == $new_hash); } } ?>[/php]
7. Create a Folder named V1. Using REST api, we’ll version our project for easy data access.
create a file named .htaccess inside the V1 folder
[php]RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^(.*)$ %{ENV:BASE}index.php [QSA,L][/php]
8. Create index.php this is where we’ll access all endpoint by the help of slim framework
[php]<?php require_once ‘../include/DbHandler.php’; require_once ‘../include/PassHash.php’; require ‘.././libs/Slim/Slim.php’; \Slim\Slim::registerAutoloader(); $app = new \Slim\Slim(); // User id from db – Global Variable $user_id = NULL; /** * ———– URLS ——————————— */ /** * User Registration * url – /register * method – POST * params – name, email, password */ $app->post(‘/register’, function() use ($app) {
// check for required params
verifyRequiredParams(array(‘name’, ’email’, ‘password’, ‘supermarket’));
$response = array();
// reading post params
$name = $app->request->post(‘name’);
$email = $app->request->post(’email’);
$password = $app->request->post(‘password’);
$supermarket = $app->request->post(‘supermarket’);
// validating email address
validateEmail($email);
$db = new DbHandler();
$res = $db->createUser($name, $email, $password, $supermarket);
if ($res == USER_CREATED_SUCCESSFULLY) {
$response["error"] = false;
$response["message"] = "You are successfully registered";
} else if ($res == USER_CREATE_FAILED) {
$response["error"] = true;
$response["message"] = "Oops! An error occurred while registering";
} else if ($res == USER_ALREADY_EXISTED) {
$response["error"] = true;
$response["message"] = "Sorry, this email already existed";
}
// echo json response
echoRespnse(201, $response);
});
/**
* User Login
* url – /login
* method – POST
* params – email, password
*/
$app->post(‘/login’, function() use ($app) {
// check for required params
verifyRequiredParams(array(’email’, ‘password’));
// reading post params
$email = $app->request()->post(’email’);
$password = $app->request()->post(‘password’);
$response = array();
$db = new DbHandler();
// check for correct email and password
if ($db->checkLogin($email, $password)) {
// get the user by email
$user = $db->getUserByEmail($email);
if ($user != NULL) {
$response["error"] = false;
$response[‘name’] = $user[‘name’];
$response[’email’] = $user[’email’];
$response[‘apiKey’] = $user[‘api_key’];
$response[‘supermarket’] = $user[‘supermarket’];
$response[‘createdAt’] = $user[‘created_at’];
} else {
// unknown error occurred
$response[‘error’] = true;
$response[‘message’] = "An error occurred. Please try again";
}
} else {
// user credentials are wrong
$response[‘error’] = true;
$response[‘message’] = ‘Login failed. Incorrect credentials’;
}
echoRespnse(200, $response);
});
/**
* Listing single product of particual user
* method GET
* url /products/:id
*/
$app->get(‘/get-products/:id’, function($page_id) {
$response = array();
$db = new DbHandler();
// fetch product
$result = $db->getProduct($page_id);
if ($result != NULL) {
// $response["error"] = false;
$response = $result;
echo $response;
} else {
// $response["error"] = true;
$response = "over";
echo $response;
}
});
$app->get(‘/get-all-products’, function() use ($app) {
$response = array();
$db = new DbHandler();
// fetch sellers
$result = $db->getProducts();
if ($result != NULL) {
// $response["error"] = false;
$response = $result;
echo ‘{"products":’ . $response ."}";
} else {
// $response["error"] = true;
$response = "not found";
echoRespnse(200, $response);
}
});
$app->get(‘/get-all-supermarket-products’, function() use ($app) {
$response = array();
$db = new DbHandler();
// fetch sellers
$result = $db->getSupermarketProducts();
if ($result != NULL) {
// $response["error"] = false;
$response = $result;
echo ‘{"supermarketProducts":’ . $response ."}";
} else {
// $response["error"] = true;
$response = "not found";
echoRespnse(200, $response);
}
});
$app->post(‘/get-sellers’, function() use ($app) {
$response = array();
$product_name = $app->request->post(‘product_name’);
$db = new DbHandler();
// fetch sellers
$result = $db->getSellers($product_name);
if ($result != NULL) {
// $response["error"] = false;
$response = $result;
echo ‘{"result":’ . $response ."}";
} else {
// $response["error"] = true;
$response = "not found";
echoRespnse(200, $response);
}
});
$app->post(‘/add-product’, function() use ($app) {
// check for required params
verifyRequiredParams(array(‘product_name’, ‘description’, ‘price’, ‘supermarket’));
$response = array();
// reading post params
$name = $app->request->post(‘product_name’);
$description = $app->request->post(‘description’);
$price = $app->request->post(‘price’);
$supermarket = $app->request->post(‘supermarket’);
$image = $app->request->post(‘image’);
$db = new DbHandler();
$res = $db->createProduct($name, $description, $price, $supermarket, $image);
if ($res == PRODUCT_CREATED_SUCCESSFULLY) {
$response["error"] = false;
$response["message"] = "Product added successfully";
} else if ($res == PRODUCT_CREATE_FAILED) {
$response["error"] = true;
$response["message"] = "Oops! An error occurred while adding product detail(s)";
} else if ($res == PRODUCT_ALREADY_EXISTED) {
$response["error"] = true;
$response["message"] = "Sorry, this product already existed";
}
// echo json response
echoRespnse(201, $response);
});
$app->post(‘/add-product-price’, function() use ($app) {
// check for required params
verifyRequiredParams(array(‘product_name’, ‘price’, ‘supermarket’));
$response = array();
// reading post params
$name = $app->request->post(‘product_name’);
$price = $app->request->post(‘price’);
$supermarket = $app->request->post(‘supermarket’);
$db = new DbHandler();
$res = $db->createProductPrice($name, $price, $supermarket);
if ($res == PRODUCT_CREATED_SUCCESSFULLY) {
$response["error"] = false;
$response["message"] = "Price added successfully";
} else if ($res == PRODUCT_CREATE_FAILED) {
$response["error"] = true;
$response["message"] = "Oops! An error occurred while adding product detail(s)";
}
// echo json response
echoRespnse(201, $response);
});
$app->get(‘/get-all-users’, function() use ($app) {
$response = array();
$db = new DbHandler();
// fetch sellers
$result = $db->getUsers();
if ($result != NULL) {
// $response["error"] = false;
$response = $result;
echo ‘{"user":’ . $response ."}";
} else {
// $response["error"] = true;
$response = "not found";
echoRespnse(200, $response);
}
});
$app->post(‘/delete-user’, function() use($app) {
verifyRequiredParams(array(‘id’));
// reading post params
$id = $app->request->post(‘id’);
$db = new DbHandler();
$response = array();
$result = $db->deleteUser($id);
if ($result) {
// User deleted successfully
$response["error"] = false;
$response["message"] = "User deleted succesfully";
} else {
// User failed to delete
$response["error"] = true;
$response["message"] = "User failed to delete. Please try again!";
}
echoRespnse(200, $response);
});
$app->post(‘/delete-product’, function() use($app) {
verifyRequiredParams(array(‘id’));
// reading post params
$id = $app->request->post(‘id’);
$db = new DbHandler();
$response = array();
$result = $db->deleteProduct($id);
if ($result) {
// User deleted successfully
$response["error"] = false;
$response["message"] = "Product deleted succesfully";
} else {
// User failed to delete
$response["error"] = true;
$response["message"] = "Product failed to delete. Please try again!";
}
echoRespnse(200, $response);
});
$app->post(‘/get-product’, function() use ($app) {
verifyRequiredParams(array(‘ProductName’));
$response = array();
// reading post params
$productId = $app->request->post(‘ProductName’);
$db = new DbHandler();
$result = $db->getProduct($productId);
$response["error"] = false;
$response["tasks"] = array();
// looping through result and preparing tasks array
while ($task = $result->fetch_assoc()) {
$tmp = array();
$tmp["ProductId"] = $task["ProductId"];
$tmp["ProductName"] = $task["ProductName"];
$tmp["image"] = $task["image"];
$tmp["InStock"] = $task["InStock"];
array_push($response["tasks"], $tmp);
}
echoRespnse(200, $response);
});
/**
* Verifying required params posted or not
*/
function verifyRequiredParams($required_fields) {
$error = false;
$error_fields = "";
$request_params = array();
$request_params = $_REQUEST;
// Handling PUT request params
if ($_SERVER[‘REQUEST_METHOD’] == ‘PUT’) {
$app = \Slim\Slim::getInstance();
parse_str($app->request()->getBody(), $request_params);
}
foreach ($required_fields as $field) {
if (!isset($request_params[$field]) || strlen(trim($request_params[$field])) <= 0) { $error = true; $error_fields .= $field . ‘, ‘; } } if ($error) { // Required field(s) are missing or empty // echo error json and stop the app $response = array(); $app = \Slim\Slim::getInstance(); $response["error"] = true; $response["message"] = ‘Required field(s) ‘ . substr($error_fields, 0, -2) . ‘ is missing or empty’; echoRespnse(400, $response); $app->stop();
}
}
/**
* Validating email address
*/
function validateEmail($email) {
$app = \Slim\Slim::getInstance();
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$response["error"] = true;
$response["message"] = ‘Email address is not valid’;
echoRespnse(400, $response);
$app->stop();
}
}
/**
* Echoing json response to client
* @param String $status_code Http response code
* @param Int $response Json response
*/
function echoRespnse($status_code, $response) {
$app = \Slim\Slim::getInstance();
// Http response code
$app->status($status_code);
// setting response content type to json
$app->contentType(‘application/json’);
echo json_encode($response);
}
$app->run();
?>[/php]
The following are the different types of JSON responses for registration and login endpoints.
Registration
URL: http://localhost/supermarket_api/v1/register
PARAMS: name, email, password, supermarket
Registration success response
[php]{
"error": false,
"message": "You are successfully registered"
}[/php]
Registration error – User Already Existed
[php]{
"error": true,
"message": "Sorry, this email already existed"
}[/php]
Registration error – parameter(s) missing
[php]{
"error": true,
"message": "Required field(s) supermarket is missing or empty"
}[/php]
Login
URL: http://localhost/supermarket_api/v1/login
PARAMS: email, password
Login success response
[php]{
"error": false,
"name": "benson",
"email": "ben@kedevelopers.com",
"supermarket": "Wallmart",
"createdAt": "2017-02-02 12:16:07"
}[/php]
Login Error
[php]{
"error": true,
"message": "Login failed. Incorrect credentials"
}[/php]
Now we have completed the PHP part. Let’s start the android part on the next page