Create Simple REST API with PHP & MySQL

REST (Representational State Transfer) is a way to define the architectural style for creating web services. The REST API are created at the server side with GET, POST, PUT or DELETE HTTP requests to perform certain tasks. The HTTP requests like create, read, update or delete are made from the client side.

If you’re a PHP developer and looking for solution to create CRUD (create, read, update, delete) operation REST API, then you’re here at right place. In this tutorial you will learn to how create CRUD operation REST API with PHP and MySQL. You would also like to checkout User Management System with PHP & MySQL to create complete user management system.

We will cover this tutorial in easy steps with live demo to create simple REST API to perform read, create, update and delete records.

So let’s start creating simple REST API with PHP and MySQL. Before we begin, take a look on files structure. We need following files for this tutorial.

index.php:
create.php:
read.php:
update.php:
deletes.php:
Rest.php:

We will create api/emp/ directory and keep all the required files for this REST api. We will create REST API with PHP to play with employee data to create, read, update and delete employee data.

Step1: Create MySQL Database Table

As we will play with employee data create and consume REST API, so first we will create emp table.

CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `skills` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step2: Simple REST API to Create Record

We will create PHP file emp/create.php to insert employee records to MySQL database. We will check for POST HTTP request and call method insertEmployee() to insert employee data to MySQL database table.

<?php
$requestMethod = $_SERVER["REQUEST_METHOD"];
include('../class/Rest.php');
$api = new Rest();
switch($requestMethod) {
	case 'POST':	
		$api->insertEmployee($_POST);
		break;
	default:
	header("HTTP/1.0 405 Method Not Allowed");
	break;
}
?>

In method insertEmployee() from class Rest.php, we will insert record into emp table and return JSON response.

<?php
function insertEmployee($empData){ 		
	$empName=$empData["empName"];
	$empAge=$empData["empAge"];
	$empSkills=$empData["empSkills"];
	$empAddress=$empData["empAddress"];		
	$empDesignation=$empData["empDesignation"];
	$empQuery="
		INSERT INTO ".$this->empTable." 
		SET name='".$empName."', age='".$empAge."', skills='".$empSkills."', address='".$empAddress."', designation='".$empDesignation."'";
	if( mysqli_query($this->dbConnect, $empQuery)) {
		$messgae = "Employee created Successfully.";
		$status = 1;			
	} else {
		$messgae = "Employee creation failed.";
		$status = 0;			
	}
	$empResponse = array(
		'status' => $status,
		'status_message' => $messgae
	);
	header('Content-Type: application/json');
	echo json_encode($empResponse);
}
?>

Now you need to make HTTP POST request to http://webdamn.com/demo/api/emp/create/ to insert http POST data to database.

Step3: Simple REST API to Read Record

We will create PHP file emp/read.php to create employee records from MySQL database table. We will check for GET http request and call method getEmployee() to get employee record according to request to get a single record or all records.

<?php
$requestMethod = $_SERVER["REQUEST_METHOD"];
include('../class/Rest.php');
$api = new Rest();
switch($requestMethod) {
	case 'GET':
		$empId = '';	
		if($_GET['id']) {
			$empId = $_GET['id'];
		}
		$api->getEmployee($empId);
		break;
	default:
	header("HTTP/1.0 405 Method Not Allowed");
	break;
}
?>

In method getEmployee() from class Rest.php, we will get record from emp table and return as JSON response.

<?php
public function getEmployee($empId) {		
	$sqlQuery = '';
	if($empId) {
		$sqlQuery = "WHERE id = '".$empId."'";
	}	
	$empQuery = "
		SELECT id, name, skills, address, age 
		FROM ".$this->empTable." $sqlQuery
		ORDER BY id DESC";	
	$resultData = mysqli_query($this->dbConnect, $empQuery);
	$empData = array();
	while( $empRecord = mysqli_fetch_assoc($resultData) ) {
		$empData[] = $empRecord;
	}
	header('Content-Type: application/json');
	echo json_encode($empData);	
}
?>

Now you need to make HTTP GET request to http://webdamn.com/demo/api/emp/read/ to read employee records and display as JSON response.

Step4: Simple REST API to Update Record

We will create PHP file emp/update.php to update employee record. We will check for POST http request and call method updateEmployee() to perform employee record update.

<?php
$requestMethod = $_SERVER["REQUEST_METHOD"];
include('../class/Rest.php');
$api = new Rest();
switch($requestMethod) {	
	case 'POST':
	print_r($_POST);
		$api->updateEmployee($_POST);
		break;
	default:
	header("HTTP/1.0 405 Method Not Allowed");
	break;
}
?>

In method updateEmployee() from class Rest.php, we will update record into emp table and return status as JSON response.

<?php
function updateEmployee($empData){ 		
	if($empData["id"]) {
		$empName=$empData["empName"];
		$empAge=$empData["empAge"];
		$empSkills=$empData["empSkills"];
		$empAddress=$empData["empAddress"];		
		$empDesignation=$empData["empDesignation"];
		$empQuery="
			UPDATE ".$this->empTable." 
			SET name='".$empName."', age='".$empAge."', skills='".$empSkills."', address='".$empAddress."', designation='".$empDesignation."' 
			WHERE id = '".$empData["id"]."'";
			echo $empQuery;
		if( mysqli_query($this->dbConnect, $empQuery)) {
			$messgae = "Employee updated successfully.";
			$status = 1;			
		} else {
			$messgae = "Employee update failed.";
			$status = 0;			
		}
	} else {
		$messgae = "Invalid request.";
		$status = 0;
	}
	$empResponse = array(
		'status' => $status,
		'status_message' => $messgae
	);
	header('Content-Type: application/json');
	echo json_encode($empResponse);
}
?>

Now you need to make HTTP POST request to http://webdamn.com/demo/api/emp/update/ to update employee records and display status as JSON response.

Step5: Simple REST API to Delete Record

We will create PHP table emp/delete.php to perform employee record. We will check for http GET request and call method deleteEmployee() to delete employee record from database.

<?php
$requestMethod = $_SERVER["REQUEST_METHOD"];
include('../class/Rest.php');
$api = new Rest();
switch($requestMethod) {
	case 'GET':
		$empId = '';	
		if($_GET['id']) {
			$empId = $_GET['id'];
		}
		$api->deleteEmployee($empId);
		break;
	default:
	header("HTTP/1.0 405 Method Not Allowed");
	break;
}
?>

In method deleteEmployee() from class Rest.php, we will delete record from emp table and return status as JSON response.

<?php
public function deleteEmployee($empId) {		
	if($empId) {			
		$empQuery = "
			DELETE FROM ".$this->empTable." 
			WHERE id = '".$empId."'	ORDER BY id DESC";	
		if( mysqli_query($this->dbConnect, $empQuery)) {
			$messgae = "Employee delete Successfully.";
			$status = 1;			
		} else {
			$messgae = "Employee delete failed.";
			$status = 0;			
		}		
	} else {
		$messgae = "Invalid request.";
		$status = 0;
	}
	$empResponse = array(
		'status' => $status,
		'status_message' => $messgae
	);
	header('Content-Type: application/json');
	echo json_encode($empResponse);	
}
?>

Now you need to make HTTP GET request to http://webdamn.com/demo/api/emp/delete/ to delete employee record and display status as JSON response

Step6: Create .htaccess Rewrite Rule with PHP for Clean URLs

We will also create emp/.htaccess file to write some rule to access rest api with pretty URLs. We will add following rules.

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

Step7: Consume Simple REST API with PHP

We will create table index.php to consume REST API to read employee records. We will make HTTP request to http://webdamn.com/demo/api/emp/read/ to get employee records. We will make HTTP request with CURL to read employee records. you can check this in live demo.

<?php
if(isset($_POST['submit']))	{
	$url = $_POST['url'];				
	$client = curl_init($url);
	curl_setopt($client,CURLOPT_RETURNTRANSFER,true);
	$response = curl_exec($client);		
	$result = json_decode($response);	
	print_r($result);		
}
?>

Here we have handled CRUD (create, read, update, delete) REST API operations to perform with employee data. You can further implement this in your project with enhancement related to security to restrict access etc.

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

Download

Web developer

Search This Blog