How to Make PHP + MySQL CRUD REST API?

Creating a CRUD (Create, Read, Update, Delete) API using PHP and MySQL is a fundamental skill for web developers.

A CRUD API allows you to interact with a database to perform these essential operations, enabling you to build dynamic and data-driven web applications.

In this article, we’ll guide you through the process of creating a simple CRUD API using PHP and MySQL.

1. Start Your Apache & MySQL

Before we get started, I hope you have a local development environment where PHP, MySQL/MariaDB, and Apache/Nginx are installed. If you don’t have, setup your PHP development environment first.

If you already have, start your Apache and MySQL servers. I am using XAMPP on my local machine –

Start Apache and MySQL server on XAMPP

2. Create the MySQL Database and Table

Now, Create a Database (using phpMyAdmin) with the name you want, I named it php_api.

After that, we have to create a table called posts, and there will be 6 columns inside the table –

  • php_api (Database)
    • posts (Table)
      • id
      • title
      • content
      • author
      • created_at
      • updated_at.

Use the following SQL code to create the posts table and its columns. Read: How to Run SQL code on a Database (using phpMyAdmin) to create a Table.

CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `author` varchar(30) NOT NULL,
  `created_at` date NOT NULL DEFAULT current_timestamp(),
  `updated_at` date NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

3. Building the PHP CRUD REST API

Go inside your XAMPP htdocs folder or your server’s www directory, and create a folder called php-crud-api, this is our project folder.

Here given in the below image are the files and folders that we have to create to build this PHP CRUD API.

PHP CRUD API folder structure

3.1. Creating PHP Classes

Create a folder called classes at the root of the application folder, all classes will be in this folder.

There are Three Classes We have to Create:

  • Database.php

    Contains the code for database connection.

    <?php
    class Database
    {
        private $db_host = 'localhost';
        private $db_name = 'php_api';
        private $db_username = 'root';
        private $db_password = '';
        function __construct()
        {
            try {
                $dsn = "mysql:host={$this->db_host};dbname={$this->db_name};charset=utf8";
                $db_connection = new PDO($dsn, $this->db_username, $this->db_password);
                $db_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                return $db_connection;
            } catch (PDOException $e) {
                echo "Connection error " . $e->getMessage();
                exit;
            }
        }
    }
  • Main.php

    Contains three static methods – one for checking the request and the another one for sending the response.

    <?php
    class Main
    {
        // Checking the Request Method
        static function check($req)
        {
            if ($_SERVER["REQUEST_METHOD"] === $req) {
                return true;
            }
            static::json(0, 405, "Invalid Request Method. HTTP method should be $req");
        }
    
        // Returns the response in JSON format
        static function json(int $ok, $status, $msg, $key = false, $value = false)
        {
            $res = ["ok" => $ok];
            if ($status !== null){
                http_response_code($status);
                $res["status"] = $status;
            }
            if ($msg !== null) $res["message"] = $msg;
            if($value){
                if($key){
                    $res[$key] = $value;
                }
                else{
                    $res["data"] = $value;
                }
            }
            echo json_encode($res);
            exit;
        }
    
        // Returns the 404 Not found
        static function _404(){
            static::json(0,404,"Not Found!");
        }
    }
  • Posts.php

    This class contains the code for all CRUD operations.

    <?php
    require_once __DIR__ . "/Database.php";
    require_once __DIR__ . "/Main.php";
    
    use Main as Response;
    
    class Post extends Database
    {
        private $DB;
    
        function __construct()
        {
            $this->DB = Database::__construct();
        }
    
        private function filter($data)
        {
            return htmlspecialchars(trim(htmlspecialchars_decode($data)), ENT_NOQUOTES);
        }
    
        // Create a new post
        public function create(string $title, string $content, string $author)
        {
            $title = $this->filter($title);
            $content = $this->filter($content);
            $author = $this->filter($author);
    
            try {
                $sql = "INSERT INTO `posts` (`title`,`content`,`author`) VALUES (:title,:content,:author)";
                $stmt = $this->DB->prepare($sql);
    
                $stmt->bindParam(":title", $title, PDO::PARAM_STR);
                $stmt->bindParam(":content", $content, PDO::PARAM_STR);
                $stmt->bindParam(":author", $author, PDO::PARAM_STR);
    
                $stmt->execute();
    
                $last_id = $this->DB->lastInsertId();
                Response::json(1, 201, "Post has been created successfully", "post_id", $last_id);
    
            } catch (PDOException $e) {
                Response::json(0, 500, $e->getMessage());
            }
        }
    
        // Fetch all posts or Get a single post through the post ID
        public function read($id = false, $return = false)
        {
            try {
                $sql = "SELECT * FROM `posts`";
                // If post id is provided
                if ($id !== false) {
                    // Post id must be a number
                    if (is_numeric($id)) {
                        $sql = "SELECT * FROM `posts` WHERE `id`='$id'";
                    } else {
                        Response::_404();
                    }
                }
                $query = $this->DB->query($sql);
                if ($query->rowCount() > 0) {
                    $allPosts = $query->fetchAll(PDO::FETCH_ASSOC);
                    // If ID is Provided, send a single post.
                    if ($id !== false) {
                        // IF $return is true then return the single post
                        if ($return) return $allPosts[0];
                        Response::json(1, 200, null, "post", $allPosts[0]);
                    }
                    Response::json(1, 200, null, "posts", $allPosts);
                }
                // If the post id does not exist in the database
                if ($id !== false) {
                    Response::_404();
                }
                // If there are no posts in the database.
                Response::json(1, 200, "Please Insert Some posts...", "posts", []);
            } catch (PDOException $e) {
                Response::json(0, 500, $e->getMessage());
            }
        }
    
        // Update an existing post
        public function update(int $id, Object $data)
        {
            try {
                $sql = "SELECT * FROM `posts` WHERE `id`='$id'";
                $query = $this->DB->query($sql);
                if ($query->rowCount() > 0) {
                    $the_post = $query->fetch(PDO::FETCH_OBJ);
    
                    $title = (isset($data->title) && !empty(trim($data->title))) ? $this->filter($data->title) : $the_post->title;
                    $content = (isset($data->body) && !empty(trim($data->body))) ? $this->filter($data->body) : $the_post->content;
                    $author = (isset($data->author) && !empty(trim($data->author))) ? $this->filter($data->author) : $the_post->author;
    
                    $update_sql = "UPDATE `posts` SET `title`=:title,`content`=:content,`author`=:author,`updated_at`=NOW() WHERE `id`='$id'";
    
                    $stmt = $this->DB->prepare($update_sql);
                    $stmt->bindParam(":title", $title, PDO::PARAM_STR);
                    $stmt->bindParam(":content", $content, PDO::PARAM_STR);
                    $stmt->bindParam(":author", $author, PDO::PARAM_STR);
    
                    $stmt->execute();
    
                    Response::json(1, 200, "Post Updated Successfully", "post", $this->read($id, true));
                }
    
                Response::json(0, 404, "Invalid Post ID.");
    
            } catch (PDOException $e) {
                Response::json(0, 500, $e->getMessage());
            }
        }
    
        // Delete a Post
        public function delete(int $id)
        {
            try {
                $sql =  "DELETE FROM `posts` WHERE `id`='$id'";
                $query = $this->DB->query($sql);
                if ($query->rowCount() > 0) {
                    Response::json(1, 200, "Post has been deleted successfully.");
                }
                Response::json(0, 404, "Invalid Post ID.");
            } catch (PDOException $e) {
                Response::json(0, 500, $e->getMessage());
            }
        }
    }

3.2. Configuration file (config.php)

The config.php file contains the headers and here two classes (Main, Post) are imported (included).

<?php
// Default Method will be "GET" if the $allow_method not found
if(!isset($allow_method)) $allow_method = "GET";
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: access");
header("Access-Control-Allow-Methods: $allow_method");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, X-Requested-With");

require_once __DIR__ . "/classes/Main.php";
require_once __DIR__ . "/classes/Post.php";

3.3. Create API Endpoints

/create.php : For Inserting New Posts

POST: http://localhost/php-crud-api/create.php
Body (application/json):
{
    "title":"Post Title",
    "body":"Post Body",
    "author":"Author Name"
}
<?php
$allow_method = "POST";
require_once __DIR__ . "/config.php";

use Main as Request;
use Main as Response;

if (Request::check("POST")) {
    $data = json_decode(file_get_contents("php://input"));
    if (
        !isset($data->title) ||
        !isset($data->body) ||
        !isset($data->author)
    ) :
        $fields = [
            "title" => "Post title",
            "body" => "Post content",
            "author" => "Author name"
        ];
        Response::json(0, 400, "Please fill all the required fields", "fields", $fields);

    elseif (
        empty(trim($data->title)) ||
        empty(trim($data->body)) ||
        empty(trim($data->author))
    ) :
        $fields = [];
        foreach($data as $key => $val){
            if(empty(trim($val))) array_push($fields, $key); 
        }
        Response::json(0, 400, "Oops! empty field detected.","empty_fields", $fields);

    else :
        $Post = new Post();
        $Post->create($data->title, $data->body, $data->author);
    endif;
}
php create new post api test

/get.php : To Fetch Posts from the Database

The get.php will fetch all posts. Provide post id on id parameter to get a single post.

# Fetch all posts
GET: http://localhost/php-crud-api/get.php

# Fetch a single post
GET: http://localhost/php-crud-api/get.php?id={post_id}
<?php
require_once __DIR__ . "/config.php";

use Main as Request;

if (Request::check("GET")) {
    $Post = new Post();
    if (isset($_GET['id'])) $Post->read(trim($_GET['id']));
    $Post->read();
}
Testing of fetching posts from database using PHP API

/update.php : Update an existing post

PUT: http://localhost/php-crud-api/update.php
Body (application/json):
{
    "id": "post id",
    "field_name (title | body | author)" : "New Value"
}
<?php
$allow_method = "PUT";
require_once __DIR__ . "/config.php";

use Main as Request;
use Main as Response;

if (Request::check("PUT")) {
    $data = json_decode(file_get_contents("php://input"));

    $fields = [
        "id" => "Post ID (Required)",
        "title" => "Post title (Optional)",
        "body" => "Post content (Optional)",
        "author" => "Author name (Optional)"
    ];

    if (!isset($data->id) || !is_numeric($data->id)) :
        Response::json(0, 400, "Please provide the valid Post ID and at least one field.", "fields", $fields);
    endif;

    $isEmpty = true;
    $empty_fields =  [];

    foreach((array)$data as $key => $val){
        if (in_array($key, ["title","body","author"])){
            if(!empty(trim($val))){
                $isEmpty = false;
            }
            else{
                array_push($empty_fields, $key);
            }
        }
    }

    if($isEmpty){
        $has_empty_fields = count($empty_fields);
        Response::json(0, 400,
        $has_empty_fields ? "Oops! empty field detected." : "Please provide the Post ID and at least one field.",
        $has_empty_fields ? "empty_fields" : "fields",
        $has_empty_fields ? $empty_fields : $fields);
    }

    $Post = new Post();
    $Post->update($data->id, $data);
}
Testing of PHP CRUD API update post

/delete.php : Update an existing post

DELETE: http://localhost/php-crud-api/delete.php
Body (application/json):
{
    "id": "post id"
}
<?php
$allow_method = "DELETE";
require_once __DIR__ . "/config.php";

use Main as Request;
use Main as Response;

if (Request::check("DELETE")) {
    $data = json_decode(file_get_contents("php://input"));
    if (!isset($data->id) || !is_numeric($data->id)) :
        Response::json(0, 400, "Please provide the valid Post ID");
    endif;
    $Post = new Post();
    $Post->delete($data->id);
}
Testing of PHP crud api delete post

Congratulations! You have successfully created a basic CRUD API using PHP and MySQL. Download the PHP CRUD API source code from GitHub. Thank You.. 🙏🏻❤️❤️