Skip to main content

Create REST API with PHP & MySQL

Updated on: coderszine team PHP, Tutorial

In our previous tutorial, we have explained how to create REST API with Node.js, Express & MongoDB. In this tutorial, we will explain how to create REST API with PHP & MySQL.

REST stand for “Representational State Transfer” is a concept of managing data on internet. The REST API’s are created and consumed by all kinds of applications to use data and perform operations.

Currently REST APIs are used in most of applications because it’s light weight as its mostly represented by JSON to perform CRUD (create, read, update, delete) operations by different applications.

So if you’re looking for best solution to create REST API, then you’re here right place. In this tutorial you will learn how to create simple RESTful API with CRUD operation using PHP and MySQL.

Also, read:

We will cover this tutorial step by step with live examples to perform CRUD operations on product data to create, read, update and delete product records.

So let’s create RESTful API with CRUD operations using PHP & MySQL. The major files are:

  • Database.php: Class that hold database connection method.
  • Product.php: A class that hold methods related to products.
  • read.php: This file will output JSON data based from “products” database table records.
  • create.php: This file will accept posted product data to be saved to database table.
  • update.php: This file will accept a product ID and product details to update record into database.
  • delete.php: This file will accept a product ID to delete a database record.

Step1: Create MySQL Database Table

As we will create REST API and handle CRUD operations, so first we will create MySQL database table products to store product details and perform CRUD operations. 

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sku_id` varchar(50) NOT NULL,
  `category_id` int(11) NOT NULL,
  `product_name` varchar(300) NOT NULL,
  `price` int(11) NOT NULL,
  `brand` varchar(100) NOT NULL,
  `material` varchar(100) NOT NULL,
  `size` varchar(20) NOT NULL,
  `qty` int(11) NOT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will also store some dump data for the product table.

INSERT INTO `products` (`id`, `sku_id`, `category_id`, `product_name`, `price`, `brand`, `material`, `size`, `qty`, `created_date`, `updated_date`) VALUES
(1, 'Cocp21_Beige_ Free Size', 1, 'Viva Women\'s Salwar Suit Dress Material', 800, 'Divastri', 'Cotton', '30', 6, '2019-10-03 00:00:00', '2019-10-08 18:23:25'),
(2, 'sr_1_2', 1, 'Viva N Diva Cotton Blend Printed Salwar Suit Material', 700, 'Divastri', 'Cotton', '36', 5, '2019-10-03 00:00:00', '2019-10-08 18:23:34'),
(3, 'V1667', 1, 'Woven Unstitched Printed Synthetic Crepe Suit Dupatta', 1100, 'Woven', 'Synthetic', '32', 1, '2019-10-03 00:00:00', '2019-10-08 18:23:40'),
(4, 'V1669', 1, 'Woven Prints Orange Unstitched Printed Synthetic Crepe Patiyala Suit with Mangalgiri Border', 450, 'Woven', 'Synthetic', '28', 6, '2016-10-03 00:00:00', '2019-10-08 18:20:58'),
(5, 'JM8956', 1, 'Shonaya Women\'s Pink Net Semi Stitched Dress', 1500, 'Shonaya', 'Fabric', '28', 5, '2019-10-03 00:00:00', '2019-10-08 18:23:48'),
(6, 'PAS245', 1, 'Floral Patiala Salwar Embroidered Cotton Salwar Kameez Suit', 1200, 'Floral', 'Cotton', '34', 8, '2019-10-03 00:00:00', '2019-10-17 15:40:00'),
(7, 'Sut658', 1, 'Applique Georgette Semi-Stitched salwar suits', 1100, 'Applique', 'Fabric', '38', 3, '2019-10-03 00:00:00', '2019-10-08 18:24:47'),
(8, 'sky_245_8', 1, 'Paisley Green Georgette Unstitched Salwar Suit?', 2400, 'Paisley', 'Fabric', '36', 8, '2019-10-03 00:00:00', '2019-10-08 18:26:06'),
(9, 'JNE0937-BLUE-DR-COPPER', 1, 'Janasya Women\'s Blue Net Semi Stiched Dress', 8578, 'Janasya', 'Synthetic', '38', 1, '2016-10-03 00:00:00', '2016-10-05 15:12:49'),
(10, 'sky_85_cotton', 1, 'Polka Printed Unstitched Regular Wear Salwar Suit Dress Material', 1300, 'Polka', 'Cotton', '32', 8, '2019-10-03 00:00:00', '2019-10-08 18:26:41'),
(11, 'sku_12h67_ty', 1, 'Ratnavati Cotton Blend Embellished Salwar Suit Material ', 1100, 'Ratnavati', 'Cotton ', '36', 10, '2019-10-09 00:00:00', '2019-10-08 19:04:36'),
(12, 'sku_12h67', 1, 'shonaya Cotton Blend Embellished Salwar Suit Material ', 1500, 'shonaya', 'Cotton ', '36', 10, '2019-10-09 00:23:56', '2019-10-08 18:55:11'),
(13, 'sku_347j', 1, 'Viva N Diva Cotton Blend Embroidered Salwar Suit Material', 1200, 'Viva ', 'cotton', '34', 8, '2019-10-09 00:00:00', '2019-10-08 18:57:20'),
(14, 'sku_uuru', 1, 'Viva N Diva Polycotton Embroidered Salwar Suit Material', 1600, 'Viva', 'Polycotton', '36', 12, '2019-10-09 00:00:00', '2019-10-08 19:03:28'),
(15, '1234567ui', 3, 'Camera', 0, 'Sony', 'Steel', '30', 12, '2019-10-27 12:13:10', '2019-10-27 11:13:10');

Step2: Make Database Connection

We will create Database.php class and create method getConnection() to make connection to MySQL database.

<?php
class Database{
	
	private $host  = 'localhost';
    private $user  = 'root';
    private $password   = "";
    private $database  = "coderszine_demo"; 
    
    public function getConnection(){		
		$conn = new mysqli($this->host, $this->user, $this->password, $this->database);
		if($conn->connect_error){
			die("Error failed to connect to MySQL: " . $conn->connect_error);
		} else {
			return $conn;
		}
    }
}
?>

Step3: Read Products

We will create read.php file to read products from MySQL database table products. We will call method read() and return all products records and also one product if product id passed. We will return products records as JSON response.

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

include_once '../config/Database.php';
include_once '../class/Product.php';

$database = new Database();
$db = $database->getConnection();
 
$product = new Product($db);

$product->id = (isset($_GET['id']) && $_GET['id']) ? $_GET['id'] : '0';

$result = $product->read();

if($result->num_rows > 0){    
    $products_arr=array();
    $products_arr["records"]=array(); 
	while ($row = $result->fetch_assoc()) { 	
        extract($row); 
        $product_item=array(
            "id" => $id,
            "sku_id" => $sku_id,
            "category_id" => $category_id,
			"product_name" => $product_name,
            "price" => $price,
            "brand" => $brand,
            "material" => $material,
			"size" => $size,
            "quantity" => $qty,
			"created_date" => $created_date,
            "updated_date" => $updated_date			
        ); 
       array_push($products_arr["records"], $product_item);
    }    
    http_response_code(200);     
    echo json_encode($products_arr);
}else{     
    http_response_code(404);     
    echo json_encode(
        array("message" => "No products found.")
    );
}

We will create class Product.phpabd create method read() in class Product.php to get product records from MySQL database table products.

function read(){	
	if($this->id) {
		$stmt = $this->conn->prepare("
			SELECT * FROM ".$this->productTable." 
			WHERE id = ?");
		$stmt->bind_param("i", $this->id);					
	} else {
		$stmt = $this->conn->prepare("SELECT * FROM ".$this->productTable);		
	}		
	$stmt->execute();			
	$result = $stmt->get_result();		
	return $result;	
}

Here we will use POSTMAN to test our REST API. We will test the read URL functionality to read the products.

https://coderszine.com/demo/api/product/read.php

Step4: Create Products

We will create create.php file to create new product to save into MySQL database table. We will call method create() and handle POST data and insert new records into products table. We will return response as JSON data.

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
 
include_once '../config/Database.php';
include_once '../class/Product.php';
 
$database = new Database();
$db = $database->getConnection();
 
$product = new Product($db);
 
$data = json_decode(file_get_contents("php://input"));

if(!empty($data->sku_id) && !empty($data->category_id) &&
!empty($data->product_name) && !empty($data->price) &&
!empty($data->brand) &&	!empty($data->material) &&
!empty($data->size) &&!empty($data->qty)){    

    $product->sku_id = $data->sku_id;
    $product->category_id = $data->category_id;
    $product->product_name = $data->product_name;
    $product->price = $data->price;
	$product->brand = $data->brand;
	$product->material = $data->material;
	$product->size = $data->size;
	$product->qty = $data->qty;
    $product->created_date = date('Y-m-d H:i:s'); 
    
    if($product->create()){         
        http_response_code(201);         
        echo json_encode(array("message" => "Product was created."));
    } else{         
        http_response_code(503);        
        echo json_encode(array("message" => "Unable to create product."));
    }
}else{    
    http_response_code(400);    
    echo json_encode(array("message" => "Unable to create product. Data is incomplete."));
}
?>

We will create method create() in class Product.php to save product record into MySQL database table.

function create(){
		
	$stmt = $this->conn->prepare("
		INSERT INTO ".$this->productTable."(sku_id, category_id, product_name, price, brand, material, size, qty, created_date)
		VALUES(?,?,?,?,?,?,?,?,?)");
	
	$this->sku_id = htmlspecialchars(strip_tags($this->sku_id));
	$this->category_id = htmlspecialchars(strip_tags($this->category_id));
	$this->product_name = htmlspecialchars(strip_tags($this->product_name));
	$this->price = htmlspecialchars(strip_tags($this->price));
	$this->brand = htmlspecialchars(strip_tags($this->brand));
	$this->material = htmlspecialchars(strip_tags($this->material));
	$this->size = htmlspecialchars(strip_tags($this->size));
	$this->qty = htmlspecialchars(strip_tags($this->qty));
	$this->created_date = htmlspecialchars(strip_tags($this->created_date));
	
	$stmt->bind_param("sisssssis", $this->sku_id, $this->category_id, $this->product_name, $this->price, $this->brand, $this->material, $this->size, $this->qty, $this->created_date);
	
	if($stmt->execute()){
		return true;
	}
 
	return false;		 
}

Here we will use POSTMAN to test the create products functionality.

https://coderszine.com/demo/api/product/create.php

Step5: Update Product

We will create update.php file to update product records. We will call method update() from class Product.php and handle product update functionality through post data.

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
 
include_once '../config/Database.php';
include_once '../class/Product.php';
 
$database = new Database();
$db = $database->getConnection();
 
$product = new Product($db);
 
$data = json_decode(file_get_contents("php://input"));

if(!empty($data->id) && !empty($data->sku_id) && 
!empty($data->category_id) && !empty($data->product_name) && 
!empty($data->price) && !empty($data->brand) &&	
!empty($data->material) && !empty($data->size) &&!empty($data->qty)){ 
	$product->id = $data->id; 
	$product->sku_id = $data->sku_id;
	$product->category_id = $data->category_id;
	$product->product_name = $data->product_name;
	$product->price = $data->price;
	$product->brand = $data->brand;
	$product->material = $data->material;
	$product->size = $data->size;
	$product->qty = $data->qty;
	$product->created_date = date('Y-m-d H:i:s'); 
	
	if($product->update()){     
		http_response_code(200);   
		echo json_encode(array("message" => "Product was updated."));
	}else{    
		http_response_code(503);     
		echo json_encode(array("message" => "Unable to update product."));
	}
	
} else {
	http_response_code(400);    
    echo json_encode(array("message" => "Unable to update product. Data is incomplete."));
}
?>

We will create method update() in class Product.php to update product record to the MySQL database table products.

function update(){
	 
	$stmt = $this->conn->prepare("
		UPDATE ".$this->productTable." 
		SET sku_id= ?, category_id = ?, product_name = ?, price = ?, brand = ?, material = ?, size = ?, qty = ?, created_date = ? 
		WHERE id = ?");
 
	$this->id = htmlspecialchars(strip_tags($this->id));
	$this->sku_id = htmlspecialchars(strip_tags($this->sku_id));
	$this->category_id = htmlspecialchars(strip_tags($this->category_id));
	$this->product_name = htmlspecialchars(strip_tags($this->product_name));
	$this->price = htmlspecialchars(strip_tags($this->price));
	$this->brand = htmlspecialchars(strip_tags($this->brand));
	$this->material = htmlspecialchars(strip_tags($this->material));
	$this->size = htmlspecialchars(strip_tags($this->size));
	$this->qty = htmlspecialchars(strip_tags($this->qty));
	$this->created_date = htmlspecialchars(strip_tags($this->created_date));
 
	$stmt->bind_param("sisssssisi", $this->sku_id, $this->category_id, $this->product_name, $this->price, $this->brand, $this->material, $this->size, $this->qty, $this->created_date, $this->id);
	
	if($stmt->execute()){
		return true;
	}
 
	return false;
}

Here we will use POSTMAN to test the update products functionality.

https://coderszine.com/demo/api/product/update.php

Step6: Delete Product

We will create delete.php file to handle product delete functionality. We will call method delete() from class Product.php and handle product delete functionality.

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
 
include_once '../config/Database.php';
include_once '../class/Product.php';
 
$database = new Database();
$db = $database->getConnection();
 
$product = new Product($db);
 
$data = json_decode(file_get_contents("php://input"));

if(!empty($data->id)) {
	$product->id = $data->id;
	if($product->delete()){    
		http_response_code(200); 
		echo json_encode(array("message" => "Product was deleted."));
	} else {    
		http_response_code(503);   
		echo json_encode(array("message" => "Unable to delete product."));
	}
} else {
	http_response_code(400);    
    echo json_encode(array("message" => "Unable to delete product. Data is incomplete."));
}
?>

We will create method delete() in class Product.php to delete product record from MySQL database table.

function delete(){
	
	$stmt = $this->conn->prepare("
		DELETE FROM ".$this->productTable." 
		WHERE id = ?");
		
	$this->id = htmlspecialchars(strip_tags($this->id));
 
	$stmt->bind_param("i", $this->id);
 
	if($stmt->execute()){
		return true;
	}
 
	return false;		 
}

Here we will use POSTMAN to test the delete products functionality.

https://coderszine.com/demo/api/product/delete.php

You may also like:

You can view the live demo from the Demo link and can download the source from the Download link below.

Demo Download