Reading Time: 12 minutes

4,008 Views

Inside this article we will see the concept of CRUD RESTFul API Development in PHP MySQLi. We will use the concept of MySQLi prepare methods to secure user inputs as well.

CRUD stands for Create, Read, Update and Delete.

REpresentational State Transfer (REST) is an architectural style that defines a set of constraints to be used for creating web services. REST API is a way of accessing web services in a simple and flexible way without having any processing.

Here, we will consider an items table and will create these following APIs in PHP & MySQLi –

Create Item API
List Item API
List Item Single Details API
Update Item
Delete Item API

Learn More –

Let’s get started.

Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE php_applications;

Inside this database, we need to create a table.

Next,

Table: items

CREATE TABLE `items` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `description` text,
 `price` int(5) NOT NULL,
 `category_id` int(5) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is items table by help of which we will create CRUD APIs.

Application Folder Structure

You need to follow a folder structure to develop your CRUD based APIs in PHP and MySQLi. Have a look the files and folders inside this application –

Create a folder named as rest-api inside your localhost directory. Inside this application folder create class, config and items folder.

Folder – class: This folder contains the class file for CRUD operation which uses prepare methods. Files – Items.php
Folder – config: This folder contains a file i.e database configuration file which connects you with database and it’s table. Files – Database.php
Folder – items: This is basically your module folder. Here, item is the part of CRUD operation. Files – create.php, delete.php, read.php, update.php
.htaccess file which makes your URLs SEO friendly. It removes .php extension from URL.

Database Configuration

Open Database.php file from /config folder. Add these lines of code into it.

<?php
class Database{
	
	private $host  = "localhost";
    private $user  = "admin";
    private $password   = "Admin@123";
    private $database  = "php_applications"; 
    
    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;
		}
    }
}

This is for database connectivity. We will created an instance of this class when it will be used.

API Class with CRUD Methods

Open Items.php file from /class folder. Add these lines of codes into it.

<?php
class Items{   
    
    private $itemsTable = "items";      
    public $id;
    public $name;
    public $description;
    public $price;
    public $category_id;   
    private $conn;
	
    public function __construct($db){
        $this->conn = $db;
    }	
	
	function read(){	
		if($this->id) {
			$stmt = $this->conn->prepare("SELECT * FROM ".$this->itemsTable." WHERE id = ?");
			$stmt->bind_param("i", $this->id);					
		} else {
			$stmt = $this->conn->prepare("SELECT * FROM ".$this->itemsTable);		
		}		
		$stmt->execute();			
		$result = $stmt->get_result();		
		return $result;	
	}
	
	function create(){
		
		$stmt = $this->conn->prepare("
			INSERT INTO ".$this->itemsTable."(name, description, price, category_id)
			VALUES(?,?,?,?)");
		
		$this->name = htmlspecialchars(strip_tags($this->name));
		$this->description = htmlspecialchars(strip_tags($this->description));
		$this->price = htmlspecialchars(strip_tags($this->price));
		$this->category_id = htmlspecialchars(strip_tags($this->category_id));
	
		$stmt->bind_param("ssii", $this->name, $this->description, $this->price, $this->category_id);
		
		if($stmt->execute()){
			return true;
		}
	 
		return false;		 
	}
		
	function update(){
	 
		$stmt = $this->conn->prepare("
			UPDATE ".$this->itemsTable." 
			SET name= ?, description = ?, price = ?, category_id = ?
			WHERE id = ?");
	 
		$this->id = htmlspecialchars(strip_tags($this->id));
		$this->name = htmlspecialchars(strip_tags($this->name));
		$this->description = htmlspecialchars(strip_tags($this->description));
		$this->price = htmlspecialchars(strip_tags($this->price));
		$this->category_id = htmlspecialchars(strip_tags($this->category_id));
	 
		$stmt->bind_param("ssiii", $this->name, $this->description, $this->price, $this->category_id, $this->id);
		
		if($stmt->execute()){
			return true;
		}
	 
		return false;
	}
	
	function delete(){
		
		$stmt = $this->conn->prepare("
			DELETE FROM ".$this->itemsTable." 
			WHERE id = ?");
			
		$this->id = htmlspecialchars(strip_tags($this->id));
	 
		$stmt->bind_param("i", $this->id);
	 
		if($stmt->execute()){
			return true;
		}
	 
		return false;		 
	}
}

Inside this API class, we have all methods of CRUD operation.

Setup – Create API

Open create.php file from /items folder. Open this file and write this code into it.

<?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/Items.php';
 
$database = new Database();
$db = $database->getConnection();
 
$items = new Items($db);
 
$data = json_decode(file_get_contents("php://input"));

if(!empty($data->name) && !empty($data->description) &&
!empty($data->price) && !empty($data->category_id)){    

    $items->name = $data->name;
    $items->description = $data->description;
    $items->price = $data->price;
    $items->category_id = $data->category_id;	
    
    if($items->create()){         
        http_response_code(201);         
        echo json_encode(array("message" => "Item was created."));
    } else{         
        http_response_code(503);        
        echo json_encode(array("message" => "Unable to create item."));
    }
}else{    
    http_response_code(400);    
    echo json_encode(array("message" => "Unable to create item. Data is incomplete."));
}

This API file used to create Items inside database table.

Setup – Read API

Open read.php file from /items folder. Open this file and write this code into it.

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

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

$database = new Database();
$db = $database->getConnection();
 
$items = new Items($db);

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

$result = $items->read();

if($result->num_rows > 0){    
    $itemRecords=array();
    $itemRecords["items"]=array(); 
	while ($item = $result->fetch_assoc()) { 	
        extract($item); 
        $itemDetails=array(
            "id" => $id,
            "name" => $name,
            "description" => $description,
			"price" => $price,
            "category_id" => $category_id		
        ); 
       array_push($itemRecords["items"], $itemDetails);
    }    
    http_response_code(200);     
    echo json_encode($itemRecords);
}else{     
    http_response_code(404);     
    echo json_encode(
        array("message" => "No item found.")
    );
}

This API file used to read all Items as well as a single item detail from database table.

Setup – Update API

Open update.php file from /items folder. Open this file and write this code into it.

<?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/Items.php';
 
$database = new Database();
$db = $database->getConnection();
 
$items = new Items($db);
 
$data = json_decode(file_get_contents("php://input"));

if(!empty($data->id) && !empty($data->name) && 
!empty($data->description) && !empty($data->price) && 
!empty($data->category_id)){ 
	
	$items->id = $data->id; 
	$items->name = $data->name;
    $items->description = $data->description;
    $items->price = $data->price;
    $items->category_id = $data->category_id;
	
	if($items->update()){     
		http_response_code(200);   
		echo json_encode(array("message" => "Item was updated."));
	}else{    
		http_response_code(503);     
		echo json_encode(array("message" => "Unable to update items."));
	}
	
} else {
	http_response_code(400);    
    echo json_encode(array("message" => "Unable to update items. Data is incomplete."));
}

This API file used to update Item details on the basis of item it to database table.

Setup – Delete API

Open delete.php file from /items folder. Open this file and write this code into it.

<?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/Items.php';
 
$database = new Database();
$db = $database->getConnection();
 
$items = new Items($db);
 
$data = json_decode(file_get_contents("php://input"));

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

This API file used to delete Item on the basis of item from database table.

Setup – .htaccess (SEO Friendly URLs)

Open .htaccess file from /items folder. Open this file and write this code into it.

RewriteEngine On    # Turn on the rewriting engine
RewriteRule ^read$ read.php [NC,L]
RewriteRule ^read/([0-9_-]*)$ read.php?id=$1 [NC,L]
RewriteRule ^create$ create.php [NC,L]
RewriteRule ^update$ update.php [NC,L]
RewriteRule ^delete$ delete.php [NC,L]

By the help of this code we are removing .php extension from file names from URLs. This helps to generate SEO friendly URLs.

Application Testing

Now,

We will test CRUD APIs. For testing we will use POSTMAN tool for it.

Create Item API

URL – http://localhost/rest-api/items/create

Method – POST

Header –

Content-Type:application/json
Accept:application/json

Body –

{
   "name": "Toshiba Sewing Machine",
   "description": "Its best & affordable machine",
   "price": 20000,
   "category_id": 5
}

Read All Item API

URL – http://localhost/rest-api/items/read

Method – GET

Read Single Item API

URL – http://localhost/rest-api/items/read?id=1

Method – GET

Update Item API

URL – http://localhost/rest-api/items/update

Method – POST

Header –

Content-Type:application/json
Accept:application/json

Body –

{
   "id": 3,
   "name": "Product 3 - Update",
   "description": "Sample Product 3 - Update",
   "price": 5000,
   "category_id": 3
}

Delete Item API

URL – http://localhost/rest-api/items/delete

Method – POST

Header –

Content-Type:application/json
Accept:application/json

Body –

{
   "id": 3
}

We hope this article helped you to learn about CRUD RESTFul API Development in PHP MySQLi Tutorial in a very detailed way.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.