Learn how to create Simple CRUD REST API in PHP PDO
In this tutorial, we are going to create REST API using PHP PDO.
Through this API we will perform simple CRUD Operation, such as we will able to create posts, read posts, update posts and delete posts.
REST
REST stands for Representational State Transfer, it is a software architectural style for handling information over the internet. Wiki
API
API stands for Application Programming Interface, it is a group of functions and processes that allow the creation of applications which access the features or data of an operating system, application, or other services.
Getting started
Database creation
Open your phpMyAdmin and create a database called php_api.
After that select the php_api database and then click on the SQL tab at the top.
Now copy the below SQL code and paste in the SQL textarea, and then click on go button.
This sql code create the `posts` table and the structure of that table inside the php_api database.
CREATE TABLE `posts` (
`id` int(10) UNSIGNED NOT NULL,
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`body` text COLLATE utf8mb4_unicode_ci NOT NULL,
`author` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `posts`
ADD PRIMARY KEY (`id`);
ALTER TABLE `posts`
MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
After complete the above step, now got to your www folder or Xampp htdocs folder.
Create Database connection
For making database connection we will create Database class.
To inset posts into the database we will create insert.php
insert.php
<?php
// SET HEADER
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: access");
header("Access-Control-Allow-Methods: POST");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
// INCLUDING DATABASE AND MAKING OBJECT
require 'database.php';
$db_connection = new Database();
$conn = $db_connection->dbConnection();
// GET DATA FORM REQUEST
$data = json_decode(file_get_contents("php://input"));
//CREATE MESSAGE ARRAY AND SET EMPTY
$msg['message'] = '';
// CHECK IF RECEIVED DATA FROM THE REQUEST
if(isset($data->title) && isset($data->body) && isset($data->author)){
// CHECK DATA VALUE IS EMPTY OR NOT
if(!empty($data->title) && !empty($data->body) && !empty($data->author)){
$insert_query = "INSERT INTO `posts`(title,body,author) VALUES(:title,:body,:author)";
$insert_stmt = $conn->prepare($insert_query);
// DATA BINDING
$insert_stmt->bindValue(':title', htmlspecialchars(strip_tags($data->title)),PDO::PARAM_STR);
$insert_stmt->bindValue(':body', htmlspecialchars(strip_tags($data->body)),PDO::PARAM_STR);
$insert_stmt->bindValue(':author', htmlspecialchars(strip_tags($data->author)),PDO::PARAM_STR);
if($insert_stmt->execute()){
$msg['message'] = 'Data Inserted Successfully';
}else{
$msg['message'] = 'Data not Inserted';
}
}else{
$msg['message'] = 'Oops! empty field detected. Please fill all the fields';
}
}
else{
$msg['message'] = 'Please fill all the fields | title, body, author';
}
//ECHO DATA IN JSON FORMAT
echo json_encode($msg);
?>
Now test our code
For testing i use Postman API Development tool.
http://localhost/api/insert.php
Read all posts
After inserting data, we will coding to read the inserted data.
read.php
<?php
// SET HEADER
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: access");
header("Access-Control-Allow-Methods: GET");
header("Access-Control-Allow-Credentials: true");
header("Content-Type: application/json; charset=UTF-8");
// INCLUDING DATABASE AND MAKING OBJECT
require 'database.php';
$db_connection = new Database();
$conn = $db_connection->dbConnection();
// CHECK GET ID PARAMETER OR NOT
if(isset($_GET['id']))
{
//IF HAS ID PARAMETER
$post_id = filter_var($_GET['id'], FILTER_VALIDATE_INT,[
'options' => [
'default' => 'all_posts',
'min_range' => 1
]
]);
}
else{
$post_id = 'all_posts';
}
// MAKE SQL QUERY
// IF GET POSTS ID, THEN SHOW POSTS BY ID OTHERWISE SHOW ALL POSTS
$sql = is_numeric($post_id) ? "SELECT * FROM `posts` WHERE id='$post_id'" : "SELECT * FROM `posts`";
$stmt = $conn->prepare($sql);
$stmt->execute();
//CHECK WHETHER THERE IS ANY POST IN OUR DATABASE
if($stmt->rowCount() > 0){
// CREATE POSTS ARRAY
$posts_array = [];
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$post_data = [
'id' => $row['id'],
'title' => $row['title'],
'body' => html_entity_decode($row['body']),
'author' => $row['author']
];
// PUSH POST DATA IN OUR $posts_array ARRAY
array_push($posts_array, $post_data);
}
//SHOW POST/POSTS IN JSON FORMAT
echo json_encode($posts_array);
}
else{
//IF THER IS NO POST IN OUR DATABASE
echo json_encode(['message'=>'No post found']);
}
?>
Test
Read all posts
http://localhost/api/read.php
Read a single post by id
http://localhost/api/read.php?id=1
Now time to update our data
To updating our data we will create update.php
update.php
<?php
// SET HEADER
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: access");
header("Access-Control-Allow-Methods: PUT");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
// INCLUDING DATABASE AND MAKING OBJECT
require 'database.php';
$db_connection = new Database();
$conn = $db_connection->dbConnection();
// GET DATA FORM REQUEST
$data = json_decode(file_get_contents("php://input"));
//CHECKING, IF ID AVAILABLE ON $data
if(isset($data->id)){
$msg['message'] = '';
$post_id = $data->id;
//GET POST BY ID FROM DATABASE
$get_post = "SELECT * FROM `posts` WHERE id=:post_id";
$get_stmt = $conn->prepare($get_post);
$get_stmt->bindValue(':post_id', $post_id,PDO::PARAM_INT);
$get_stmt->execute();
//CHECK WHETHER THERE IS ANY POST IN OUR DATABASE
if($get_stmt->rowCount() > 0){
// FETCH POST FROM DATBASE
$row = $get_stmt->fetch(PDO::FETCH_ASSOC);
// CHECK, IF NEW UPDATE REQUEST DATA IS AVAILABLE THEN SET IT OTHERWISE SET OLD DATA
$post_title = isset($data->title) ? $data->title : $row['title'];
$post_body = isset($data->body) ? $data->body : $row['body'];
$post_author = isset($data->author) ? $data->author : $row['author'];
$update_query = "UPDATE `posts` SET title = :title, body = :body, author = :author
WHERE id = :id";
$update_stmt = $conn->prepare($update_query);
// DATA BINDING AND REMOVE SPECIAL CHARS AND REMOVE TAGS
$update_stmt->bindValue(':title', htmlspecialchars(strip_tags($post_title)),PDO::PARAM_STR);
$update_stmt->bindValue(':body', htmlspecialchars(strip_tags($post_body)),PDO::PARAM_STR);
$update_stmt->bindValue(':author', htmlspecialchars(strip_tags($post_author)),PDO::PARAM_STR);
$update_stmt->bindValue(':id', $post_id,PDO::PARAM_INT);
if($update_stmt->execute()){
$msg['message'] = 'Data updated successfully';
}else{
$msg['message'] = 'data not updated';
}
}
else{
$msg['message'] = 'Invlid ID';
}
echo json_encode($msg);
}
?>
Testing
Type post id which you want to update and type the row name and value (whatever you want).
Here I only want to change the author’s name on Post id 1.
http://localhost/api/update.php
Read the updated data
After updating the data, now check the data updated or not.
http://localhost/api/read.php?id=1
Tip:- you can update multiple rows at once just add another row name and value.
At the end we will delete our data
To delete our data we create delete.php
delete.php
<?php
// SET HEADER
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: access");
header("Access-Control-Allow-Methods: DELETE");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
// INCLUDING DATABASE AND MAKING OBJECT
require 'database.php';
$db_connection = new Database();
$conn = $db_connection->dbConnection();
// GET DATA FORM REQUEST
$data = json_decode(file_get_contents("php://input"));
//CHECKING, IF ID AVAILABLE ON $data
if(isset($data->id)){
$msg['message'] = '';
$post_id = $data->id;
//GET POST BY ID FROM DATABASE
// YOU CAN REMOVE THIS QUERY AND PERFORM ONLY DELETE QUERY
$check_post = "SELECT * FROM `posts` WHERE id=:post_id";
$check_post_stmt = $conn->prepare($check_post);
$check_post_stmt->bindValue(':post_id', $post_id,PDO::PARAM_INT);
$check_post_stmt->execute();
//CHECK WHETHER THERE IS ANY POST IN OUR DATABASE
if($check_post_stmt->rowCount() > 0){
//DELETE POST BY ID FROM DATABASE
$delete_post = "DELETE FROM `posts` WHERE id=:post_id";
$delete_post_stmt = $conn->prepare($delete_post);
$delete_post_stmt->bindValue(':post_id', $post_id,PDO::PARAM_INT);
if($delete_post_stmt->execute()){
$msg['message'] = 'Post Deleted Successfully';
}else{
$msg['message'] = 'Post Not Deleted';
}
}else{
$msg['message'] = 'Invlid ID';
}
// ECHO MESSAGE IN JSON FORMAT
echo json_encode($msg);
}
?>
