How to create and use simple RESTful API using PHP and MySQL

RESTful API is widely used in web and mobile application now. The full form of REST is REpresentational State Transfer (REST). The data are managed over the Internet using this service. It refers to a resource and the representation of the resource is stateless. The full form of API is Application Programming Interface. The rules are defined by the API to communicate between the applications. Create, Read, Update, and Delete (CRUD) operations are included by the rules. REST API is beneficial to use when it is required to perform CRUD operations between different applications over the Internet or HTTP protocol. It provides output in different formats such as Extensible Mark-up Language (XML), JavaScript Object Notation (JSON), Command Separated Value (CSV), etc. How a simple RESTful API can be created and applied by using PHP are shown in this tutorial.

RESTful API Requests:

The four types of REST requests are used for handling Create, Read, Update and Delete operations. The following table shows which request is used for which operation.

RequestOperation
POSTIt is used for Create operation.
GETIt is used for Read operation.
PUTIt is used for Update operation.
DELETEIt is used for Delete operation.

Create REST API using PHP and MySQL:

In this section, a MySQL database will be created with two tables to create the RESTful API.

Create Database and tables:

Open phpMyAdmin from any browser and run the following SQL to create a database named ‘testapi’.

CREATE DATABASE testapi;

Run the following SQL commands to create two tables named ‘tsp’ and ‘students’


CREATE TABLE `tsp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(256) NOT NULL,
`email` varchar(50),
`address` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB;

CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(256) NOT NULL,
`email` varchar(50),
`round` int(11) NOT NULL,
`course` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
`tsp_id` int(11) NOT NULL,
 FOREIGN KEY (tsp_id) REFERENCES tsp(id)
 ON UPDATE CASCADE ON DELETE RESTRICT
)ENGINE=InnoDB;

Insert Data into the tables:

INSERT INTO tsp (`name`, `email`, `address`) VALUES
('CCSL', 'ccsl@gmail.com', 'Dhanmondi'),
('TCL', 'tcl@gmail.com', 'Palton'),
('Horizon', 'horizon@gmail.com', 'Elephant Road');

INSERT INTO students (`name`, `email`, `round`, `course`, `tsp_id`) VALUES
('Kabir Hossain', 'kabir@gmail.com', 41,'WPDF',1),
('Helal Uddin', 'helal@gmail.com', 41,'WPDF',1),
('Mahmuda Akter', 'mahmuda@gmail.com', 41,'WPDF',1),
('Habibur Rahman', 'habib@gmail.com', 41,'WPDF',1),
('Nusrat Jahan', 'nusrat@gmail.com', 41,'WPDF',1);

Create Database Connection:


<?php
//Create database connection
$db = new mysqli("localhost","root","","testapi");
if (!$db) {
	die("Connection error: " . mysqli_connect_error());
}
?>

Create API to read data:


<?php
header("Content-Type:application/json");
if (isset($_GET['tsp_id']) && $_GET['tsp_id']!="") {
	include('db.php');
	$tsp_id = $_GET['tsp_id'];
	$result = $db->query("SELECT students.id as stdid,students.name as stdname,round,course,tsp.name as tspname FROM students,tsp WHERE tsp_id=$tsp_id and tsp.id = $tsp_id");
	if(mysqli_num_rows($result)>0){
		$stdArr["data"] = array();
		while($row=$result->fetch_assoc()){
			array_push($stdArr["data"], $row);
		}
		echo json_encode($stdArr);
	}
}
?>

Create .htaccess file for user-friendly URL:


RewriteEngine On
RewriteRule ^readapi/([0-9]*)$ readapi.php?tsp_id=$1 [NC,L]

Using REST API using PHP:


<form action="" method="POST">
<label>Select TSP Name:</label><br />
<?php
include('db.php');
$result = $db->query("SELECT id, name FROM tsp order by name asc");
echo '<select name="tsp_id"><option>Select</option>';
while ($row = $result->fetch_array()) {
	echo '<option value='.$row[0].'>'.$row[1].'</option>';
}
echo '</select>';
?>
<br /><br />
<button type="submit" name="submit">Submit</button>
</form>
<?php
if (isset($_POST['tsp_id']) && $_POST['tsp_id']!="") {
	$tsp_id = $_POST['tsp_id'];
	$url = "http://localhost/phpAPI/readapi/".$tsp_id;
	$client = curl_init($url);
	curl_setopt($client,CURLOPT_RETURNTRANSFER,true);
	$response = curl_exec($client);
	$result = json_decode($response,true);
	if(isset($result['data']))
	{
		if(count($result['data']) > 0)
		{
			echo "<table><tr><th>ID</th><th>Name</th><th>Round</th><th>Course</th><th>TSP</th></tr>";
			$index = 0;
			while($index < count($result['data']))
			{
				echo "<tr><td>".$result['data'][$index]['stdid']."</td><td>"
				.$result['data'][$index]['stdname']."</td><td>"
				.$result['data'][$index]['round']."</td><td>"
				.$result['data'][$index]['course']."</td><td>"
				.$result['data'][$index]['tspname']."</td><td></tr>";
				$index++;
			}
			echo "</table>";
		}
	}
	else
	{
		echo "No record found";
	}
}
?>

Conclusion

The basic concept of RESTful API is explained by using PHP and MySQL database in this tutorial for helping the the readers to create their own API.

You Check the following tutorial to learn REST API CRUD using PHP and MySQL.
Create Simple REST API CRUD with PHP and MySQL
The steps of this tutorial are shown in the following video link: