REST meaning of “REpresentational State Transfer”. It is a concept or an architecture for transfer information over the internet with unique and standard structure specially designed for it. REST concepts/architectures are referred as resources. A representation of a resource must be stateless and it is usually represented in JSON format.
API meaning of “Application Programming Interface”. It is a set of rules/methods that allows one piece of software application to talk to another with specially defined rules/methods with its own structure. Those “rules/methods” can include for create, read, update and delete operations.
Here i have explained, How to make a REST API for a CRUD (CREATE, READ, UPDATE, DELETE) application. For example, i am going to explain here;
I am going to use a test_database with the users table.
CREATE TABLE `users` ( `id` int(11) NOT NULL, `first_name` varchar(255) NOT NULL, `last_name` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `username` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `status` int(11) NOT NULL DEFAULT '1' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `users` ADD PRIMARY KEY (`id`); ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
Here are some test data to the table, for the testing purpose.
INSERT INTO `users` (`id`, `first_name`, `last_name`, `email`, `username`, `password`, `status`) VALUES (1, 'John', 'Doe', 'jd@gmail.com', 'john', '2829fc16ad8ca5a79da932f910afad1c', 1), (2, 'Peter', 'Alex', 'peter@gmail.com', 'peter', '827ccb0eea8a706c4c34a16891f84e7b', 1);
Let’s start to make a simple REST API step by step using PHP/MySQL according to the following;
How to create an user in the database ?
How to read user’s information from the database ?
How to update user’s information in the database ?
How to delete an user from the database ?
Additionally,
How to get all users list from the database ?
How to search users from the database ?
For the database connection i am using my database class discussed in a previous article. https://blogs.webservice.lk/2020/02/28/phpmysql-database-class/
How to create an user in the database ?
To create a user, we need to pass data to the create_user.php file. Here we only allow POST method to transfer data between the API and the application. API will return response in JSON format.
In this JSON response i have used;
response – to get the status success/unsuccess
message – to describe the status
count – to get the row count in the data result (When fetching rows)
data – to get the fetched data. (Sometimes, insert id for insert operations)
create_user.php
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
require('class.database.php');
$arr = [];
if($_SERVER['REQUEST_METHOD']=="POST"){
if(isset($_POST['first_name'])) $first_name = $database->escape($_POST['first_name']);
if(isset($_POST['last_name'])) $last_name = $database->escape($_POST['last_name']);
if(isset($_POST['username'])) $username = $database->escape($_POST['username']);
if(isset($_POST['password'])) $password = $database->escape($_POST['password']);
if(isset($_POST['email'])) $email = $database->escape($_POST['email']);
if(isset($first_name) && !empty($first_name)){
if(isset($last_name) && !empty($last_name)){
if(isset($username) && !empty($username)){
if(isset($password) && !empty($password)){
if(isset($email) && filter_var($email,FILTER_VALIDATE_EMAIL)){
$sql = "INSERT INTO `users`
(
`first_name`,`last_name`,`username`,`password`,`email`
)
VALUES (
'$first_name','$last_name','$username','".md5($password)."','$email'
)";
$insert_id = $database->insert($sql);
if($insert_id>0){
$arr = ["response"=>"success","message"=>"User successfuly created.","id"=>$insert_id];
}
else $arr = ["response"=>"unsuccess","message"=>"Create user failed. ".$database->error()];
}
else $arr = ["response"=>"unsuccess","message"=>"Valid email required !"];
}
else $arr = ["response"=>"unsuccess","message"=>"Password required !"];
}
else $arr = ["response"=>"unsuccess","message"=>"Username required !"];
}
else $arr = ["response"=>"unsuccess","message"=>"Last name required !"];
}
else $arr = ["response"=>"unsuccess","message"=>"First name equired !"];
}
else $arr = ["response"=>"unsuccess","message"=>"Bad request !"];
echo json_encode($arr);
?>
We can test this create_user.php using POSTMAN or any REST API testing tool like RESTer extension installed in Firefox web browser. According to this example, we need to pass First Name, Last Name, Username, Password and the Email to create a new user as below.
How to read user’s information from the database ?
To read information for an user, we need to pass user’s id to get_user.php file and get the result related to the sent user id. Data can be sent only via POST method.
get_user.php
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
require('class.database.php');
$arr = [];
if($_SERVER['REQUEST_METHOD']=="POST"){
if(isset($_POST['id'])) $id = $database->escape($_POST['id']);
if(isset($id) && !is_null($id) && $id>0){
$sql = "SELECT * FROM `users` WHERE `id`='$id'";
$results = $database->select($sql);
$count = $database->num_rows($sql);
if($count>0){
$arr = ["response"=>"success","data"=>$results[0]];
}
else $arr = ["response"=>"unsuccess","message"=>"User not found !".$database->error()];
}
else $arr = ["response"=>"unsuccess","message"=>"Id required !"];
}
else $arr = ["response"=>"unsuccess","message"=>"Bad request !"];
echo json_encode($arr);
?>
API will return response in JSON format. Here is the test result for sent id as 1. This will show the result for user id 1.
How to update user’s information in the database ?
To update an user, we need to send the id of the user we need to update and the required data to update in the database.
update_user.php
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
require('class.database.php');
$arr = [];
if($_SERVER['REQUEST_METHOD']=="POST"){
if(isset($_POST['id'])) $id = $database->escape($_POST['id']);
if(isset($_POST['first_name'])) $first_name = $database->escape($_POST['first_name']);
if(isset($_POST['last_name'])) $last_name = $database->escape($_POST['last_name']);
if(isset($_POST['username'])) $username = $database->escape($_POST['username']);
if(isset($_POST['password'])) $password = $database->escape($_POST['password']);
if(isset($_POST['email'])) $email = $database->escape($_POST['email']);
if(isset($id) && !is_null($id) && $id>0){
if(isset($first_name) && !empty($first_name)){
if(isset($last_name) && !empty($last_name)){
if(isset($username) && !empty($username)){
if(isset($password) && !empty($password)){
if(isset($email) && filter_var($email,FILTER_VALIDATE_EMAIL)){
$sql = "UPDATE `users`SET
`first_name`='$first_name',`last_name`='$last_name',`username`='$username',
`password`='".md5($password)."',`email`='$email'
WHERE id='$id'";
if($database->query($sql)===true){
$arr = ["response"=>"success","message"=>"User successfuly updated."];
}
else $arr = ["response"=>"unsuccess","message"=>"Update user failed. ".$database->error()];
}
else $arr = ["response"=>"unsuccess","message"=>"Valid email required !"];
}
else $arr = ["response"=>"unsuccess","message"=>"Password required !"];
}
else $arr = ["response"=>"unsuccess","message"=>"Username required !"];
}
else $arr = ["response"=>"unsuccess","message"=>"Last name required !"];
}
else $arr = ["response"=>"unsuccess","message"=>"First name equired !"];
}
else $arr = ["response"=>"unsuccess","message"=>"Id required !"];
}
else $arr = ["response"=>"unsuccess","message"=>"Bad request !"];
echo json_encode($arr);
?>
How to delete an user from the database ?
To delete an user from the database, we need to pass the id of the user in the database to the delete_user.php file. Here i am not going to delete the user information from the database and used a status field to update the user’s status to the value zero. So status=0 rows are deleted users. [We should not delete important data permanently in our real applications, because these records will very important in one day to audit some database operations.]
delete_user.php
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
require('class.database.php');
$arr = [];
if($_SERVER['REQUEST_METHOD']=="POST"){
if(isset($_POST['id'])) $id = $database->escape($_POST['id']);
if(isset($id) && !is_null($id) && $id>0){
$sql = "UPDATE `users` SET `status`=0 WHERE `id`='$id'";
if($database->query($sql)===true){
$arr = ["response"=>"success","message"=>"User successfuly deleted."];
}
else $arr = ["response"=>"unsuccess","message"=>"Delete user failed. ".$database->error()];
}
else $arr = ["response"=>"unsuccess","message"=>"Id required !"];
}
else $arr = ["response"=>"unsuccess","message"=>"Bad request !"];
echo json_encode($arr);
?>
How to get all users list from the database ?
To get all users we can sent request using POST method without any parameters since we don’t want to add any filter to the fetching result. Here i have only returned the rows with status=1. Because status=0 are the deleted rows and we don’t need to show them when fetching users from the database.
get_users.php
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
require('class.database.php');
$arr = [];
if($_SERVER['REQUEST_METHOD']=="POST"){
$sql = "SELECT * FROM `users` WHERE `status`=1 ";
$results = $database->select($sql);
$count = $database->num_rows($sql);
if($count>0){
$arr = ["response"=>"success","count"=>$count,"data"=>$results];
}
else $arr = ["response"=>"unsuccess","message"=>"No users found. ".$database->error()];
}
else $arr = ["response"=>"unsuccess","message"=>"Bad request !"];
echo json_encode($arr);
?>
Here is the test result using RESTer extension installed in the Firefox web browser.
How to search users from the database ?
To search users from the database, we can send search parameters to the serach_user.php file. According to this example, we can search users using their First Name, Last Name, Username and Email.
search_user.php
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
require('class.database.php');
$arr = [];
if($_SERVER['REQUEST_METHOD']=="POST"){
if(isset($_POST['first_name'])) $first_name = $database->escape($_POST['first_name']);
if(isset($_POST['last_name'])) $last_name = $database->escape($_POST['last_name']);
if(isset($_POST['username'])) $username = $database->escape($_POST['username']);
if(isset($_POST['email'])) $email = $database->escape($_POST['email']);
$sql = "SELECT * FROM `users` WHERE `status`=1 ";
if(isset($first_name) && !empty($first_name)){
$sql .= " AND `first_name` LIKE '%".$first_name."%' ";
}
if(isset($last_name) && !empty($last_name)){
$sql .= " AND `last_name` LIKE '%".$first_name."%' ";
}
if(isset($username) && !empty($username)){
$sql .= " AND `username` LIKE '%".$first_name."%' ";
}
if(isset($email) && !empty($email)){
$sql .= " AND `email` LIKE '%".$first_name."%' ";
}
$results = $database->select($sql);
$count = $database->num_rows($sql);
if($count>0){
$arr = ["response"=>"success","count"=>$count,"data"=>$results];
}
else $arr = ["response"=>"unsuccess","message"=>"No users found for the search. ".$database->error()];
}
else $arr = ["response"=>"unsuccess","message"=>"Bad request !"];
echo json_encode($arr);
?>
Matched search response will returned the status, row count and the result in JSON format.
Likewise, we can create a simple REST API using PHP/MySQL for our cross-platform applications to communicate between the database and the application. This is just an example only. To use this in a production application, we need to think about it’s security and many more things.
You can download this source files from my Github: https://github.com/lahirutm/REST-API-php-mysql