How to Create Simple REST API PHP and MySQL

Today, We will learn how to create a simple rest API in PHP and MySQL. Read: How to Live Search using PHP MySQL and Ajax

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

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 to create REST API

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 to create REST API

We will create a PHP file "emp/create.php” to insert employee records into the MySQL database. We will check for POST HTTP request and call method "insertEmployee()” to insert employee data into the 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 records 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 an HTTP POST request to “https://example.in/demo/api/emp/create/" to insert HTTP POST data into the database.

Step3: Simple REST API to Read Record to create REST API

We will create a PHP file “emp/read.php" to create employee records from the MySQL database table. We will check for GET HTTP request and call method “getEmployee()” to get employee records according to the 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 records 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 an HTTP GET request to “https://example.in/demo/api/emp/read/” to read employee records and display as JSON response.

Step4: Simple REST API to Update Record to Create REST API

We will create a PHP file “emp/update.php" to update employee records. We will check for “POST" HTTP request and call a method "updateEmployee()" to perform employee record updates.

<?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 records 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 an HTTP POST request to “https:/example.in/demo/api/emp/update/” to update employee records and display the status as JSON response.

Step5: Simple REST API to Delete Record to create REST API

We will create a PHP table “emp/delete.php” to perform employee record. We will check for HTTP "GET" requests and call a method "deleteEmployee()" to delete an employee record from the 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 records 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 an HTTP GET request to "https://example.in/demo/api/emp/delete/" to delete employee record and display the status as JSON response

Step6: Create .htaccess Rewrite Rule with PHP for Clean URLs to create REST API

We will also create “emp/.htaccess" file to write some rules to access the rest of API with pretty URLs. We will add the 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 to create REST API

We will create table “index.php" to consume REST API to read employee records. We will make an HTTP request to “https://examplein/demo/api/emp/read/ ” to get employee records. We will make an HTTP request with CURL to read employee records.

<?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);		
}
?>

Read Also: How to Live Search using PHP MySQL and Ajax

Leave a Reply