Build Simple PHP CRUD RESTful API In Easy Way

In this post, you will learn the simple way to build a simple CRUD RESTful API using PHP PDO and MySQL Database.

Through this API we can perform the CRUD operation on Post, such as create, insert, read, edit, delete the post.

Follow the below steps to create this PHP CRUD API:

1. Database Setup.

First, we have to set up a database for this CRUD API application.

So, open your MySQL database and create a new database called php_crud_api.

After creating the database, use the following SQL code to create the post table and the structure of this table.

CREATE TABLE `posts` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `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,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2. Creation of the PHP CRUD API

Now, go inside the htdocs folder and create a new folder called php-crud-api. This is the application root folder. It is not mandatory to give the same name, you can name it whatever you want.

📂 Structure of the php-crud-api Folder.

php-crud-api/
├─ Database.php
├─ insert.php
├─ read.php
├─ update.php
├─ delete.php

2.1. Database.php for database connection

<?php
class Database
{

    private $db_host = 'localhost';
    private $db_name = 'php_api';
    private $db_username = 'root';
    private $db_password = '';


    public function dbConnection()
    {

        try {
            $conn = new PDO('mysql:host=' . $this->db_host . ';dbname=' . $this->db_name, $this->db_username, $this->db_password);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return $conn;
        } catch (PDOException $e) {
            echo "Connection error " . $e->getMessage();
            exit;
        }
    }
}

2.2. insert.php

<?php
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");


if ($_SERVER['REQUEST_METHOD'] !== 'POST') :
    http_response_code(405);
    echo json_encode([
        'success' => 0,
        'message' => 'Invalid Request Method. HTTP method should be POST',
    ]);
    exit;
endif;

require 'database.php';
$database = new Database();
$conn = $database->dbConnection();

$data = json_decode(file_get_contents("php://input"));

if (!isset($data->title) || !isset($data->body) || !isset($data->author)) :

    echo json_encode([
        'success' => 0,
        'message' => 'Please fill all the fields | title, body, author.',
    ]);
    exit;

elseif (empty(trim($data->title)) || empty(trim($data->body)) || empty(trim($data->author))) :

    echo json_encode([
        'success' => 0,
        'message' => 'Oops! empty field detected. Please fill all the fields.',
    ]);
    exit;

endif;

try {

    $title = htmlspecialchars(trim($data->title));
    $body = htmlspecialchars(trim($data->body));
    $author = htmlspecialchars(trim($data->author));

    $query = "INSERT INTO `posts`(title,body,author) VALUES(:title,:body,:author)";

    $stmt = $conn->prepare($query);

    $stmt->bindValue(':title', $title, PDO::PARAM_STR);
    $stmt->bindValue(':body', $body, PDO::PARAM_STR);
    $stmt->bindValue(':author', $author, PDO::PARAM_STR);

    if ($stmt->execute()) {

        http_response_code(201);
        echo json_encode([
            'success' => 1,
            'message' => 'Data Inserted Successfully.'
        ]);
        exit;
    }
    
    echo json_encode([
        'success' => 0,
        'message' => 'Data not Inserted.'
    ]);
    exit;

} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode([
        'success' => 0,
        'message' => $e->getMessage()
    ]);
    exit;
}

POST - http://localhost/php-crud-api/insert.php
Payload:
{
    "title":"Post Title",
    "body":"Post Body",
    "author":"Author Name"
}

2.3. read.php

<?php
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");

if ($_SERVER['REQUEST_METHOD'] !== 'GET') :
    http_response_code(405);
    echo json_encode([
        'success' => 0,
        'message' => 'Invalid Request Method. HTTP method should be GET',
    ]);
    exit;
endif;

require 'database.php';
$database = new Database();
$conn = $database->dbConnection();
$post_id = null;

if (isset($_GET['id'])) {
    $post_id = filter_var($_GET['id'], FILTER_VALIDATE_INT, [
        'options' => [
            'default' => 'all_posts',
            'min_range' => 1
        ]
    ]);
}

try {

    $sql = is_numeric($post_id) ? "SELECT * FROM `posts` WHERE id='$post_id'" : "SELECT * FROM `posts`";

    $stmt = $conn->prepare($sql);

    $stmt->execute();

    if ($stmt->rowCount() > 0) :

        $data = null;
        if (is_numeric($post_id)) {
            $data = $stmt->fetch(PDO::FETCH_ASSOC);
        } else {
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        echo json_encode([
            'success' => 1,
            'data' => $data,
        ]);

    else :
        echo json_encode([
            'success' => 0,
            'message' => 'No Result Found!',
        ]);
    endif;
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode([
        'success' => 0,
        'message' => $e->getMessage()
    ]);
    exit;
}

Read All Posts API test

GET - http://localhost/php-crud-api/read.php

Read Single Post By Post ID API test

GET - http://localhost/php-crud-api/read.php?id={post_id}

2.4. update.php

<?php
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");

if ($_SERVER['REQUEST_METHOD'] !== 'PUT') :
    http_response_code(405);
    echo json_encode([
        'success' => 0,
        'message' => 'Invalid Request Method. HTTP method should be PUT',
    ]);
    exit;
endif;

require 'database.php';
$database = new Database();
$conn = $database->dbConnection();

$data = json_decode(file_get_contents("php://input"));

if (!isset($data->id)) {
    echo json_encode(['success' => 0, 'message' => 'Please provide the post ID.']);
    exit;
}

try {

    $fetch_post = "SELECT * FROM `posts` WHERE id=:post_id";
    $fetch_stmt = $conn->prepare($fetch_post);
    $fetch_stmt->bindValue(':post_id', $data->id, PDO::PARAM_INT);
    $fetch_stmt->execute();

    if ($fetch_stmt->rowCount() > 0) :

        $row = $fetch_stmt->fetch(PDO::FETCH_ASSOC);
        $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);

        $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', $data->id, PDO::PARAM_INT);


        if ($update_stmt->execute()) {

            echo json_encode([
                'success' => 1,
                'message' => 'Post updated successfully'
            ]);
            exit;
        }

        echo json_encode([
            'success' => 0,
            'message' => 'Post Not updated. Something is going wrong.'
        ]);
        exit;

    else :
        echo json_encode(['success' => 0, 'message' => 'Invalid ID. No posts found by the ID.']);
        exit;
    endif;
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode([
        'success' => 0,
        'message' => $e->getMessage()
    ]);
    exit;
}

In the payload, add only those fields which you want to update.

PUT - http://localhost/php-crud-api/update.php
Payload:
{
    "id":"post_id",
    "field_name (title, author, body)":"New value"
}

2.5. delete.php

<?php
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");

if ($_SERVER['REQUEST_METHOD'] !== 'DELETE') :
    http_response_code(405);
    echo json_encode([
        'success' => 0,
        'message' => 'Invalid Request Method. HTTP method should be DELETE',
    ]);
    exit;
endif;

require 'database.php';
$database = new Database();
$conn = $database->dbConnection();

$data = json_decode(file_get_contents("php://input"));

if (!isset($data->id)) {
    echo json_encode(['success' => 0, 'message' => 'Please provide the post ID.']);
    exit;
}

try {

    $fetch_post = "SELECT * FROM `posts` WHERE id=:post_id";
    $fetch_stmt = $conn->prepare($fetch_post);
    $fetch_stmt->bindValue(':post_id', $data->id, PDO::PARAM_INT);
    $fetch_stmt->execute();

    if ($fetch_stmt->rowCount() > 0) :

        $delete_post = "DELETE FROM `posts` WHERE id=:post_id";
        $delete_post_stmt = $conn->prepare($delete_post);
        $delete_post_stmt->bindValue(':post_id', $data->id,PDO::PARAM_INT);

        if ($delete_post_stmt->execute()) {

            echo json_encode([
                'success' => 1,
                'message' => 'Post Deleted successfully'
            ]);
            exit;
        }

        echo json_encode([
            'success' => 0,
            'message' => 'Post Not Deleted. Something is going wrong.'
        ]);
        exit;

    else :
        echo json_encode(['success' => 0, 'message' => 'Invalid ID. No posts found by the ID.']);
        exit;
    endif;

} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode([
        'success' => 0,
        'message' => $e->getMessage()
    ]);
    exit;
}

DELETE - http://localhost/php-crud-api/delete.php

9 Comments

Excelent tutorial, how send a image file??

Send Image File

Response

index.php

<?php
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");

if(isset($_FILES['image'])){
    echo json_encode($_FILES);
}
else{
    echo json_encode(array("msg"=>"404 Page Not Found!"));
}

Hello, would it be possible to create an example of how the ajax call would be to do an insert?

Yes Dani, Here it is –

<script>
    fetch('http://localhost/php-crud-api/insert.php',{
        method: 'POST',
        headers:{
            'Content-Type': 'application/json',
        },
        body:JSON.stringify({
            title:"This is a test title",
            body:"This is a test description",
            author:"John Doe"
        })
    })
    .then((response) => response.json())
    .then((data) => {
        console.log(data);
    })
    .catch((error) => {
        console.log(error);
    });
</script>

Dani

May 13, 2020 / 9:40 PM

Thank you!

Hameem

December 6, 2020 / 11:34 AM Reply

Hi, is there a way to secure this API with a username and password?
- Chandan Tudu
  
  December 7, 2020 / 5:59 PM
  
  Yes, Check out this – PHP Login and Registration RESTful API
tarik

November 17, 2021 / 6:01 AM Reply

hello i appreciate the work you’ve done , i have a noob question what’s the tool u use to send requests to ur DB (the tool on the GIF image)
- Chandan Tudu
  
  November 19, 2021 / 6:17 PM
  
  Postman

How to Create PHP CRUD RESTful API in Easy Way?

Follow the below steps to create this PHP CRUD API:

1. Database Setup.

2. Creation of the PHP CRUD API

2.1. Database.php for database connection

2.2. insert.php

2.3. read.php

Read All Posts API test

Read Single Post By Post ID API test

2.4. update.php

2.5. delete.php

9 Comments

Leave a ReplyCancel Reply

Follow the below steps to create this PHP CRUD API:

1. Database Setup.

2. Creation of the PHP CRUD API

2.1. Database.php for database connection

2.2. insert.php

2.3. read.php

Read All Posts API test

Read Single Post By Post ID API test

2.4. update.php

2.5. delete.php

Related Posts

How to sort PHP arrays in various ways?

Easy way to check PHP array length

Easy way to search value in a PHP array

9 Comments

Leave a ReplyCancel Reply