How To Make CRUD API In PHP + MySQL

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 step-by-step tutorial guide, your will learn how to create a simple CRUD API using PHP along with MySQL database.

Step 1: Setup Your Database & Its Table

  • Database name: php_api
  • Table name: posts
  • “posts” table columns:
    • id
    • title
    • content
    • author
    • created_at
    • updated_at

Use the following SQL code to create the posts table and its columns.

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;

Step 2: Create the Project Folder

Open your xampp htdocs folder or your localhost www directory and create a new folder called php-crud-api for this crud api project.

Here is the structure of the php-crud-api folder:

php crud api folder structure

Step 3: Create the Classes

In the project folder create a folder called classes/. All the classes will be inside the folder. Now we need to create three classes –

1. Database.php – A class with a constructor method that establishes a connection to a MySQL database using PDO (PHP Data Objects).

<?php
class Database
{
    // Replace the database details according to yours
    private $db_host = 'localhost';
    private $db_name = 'php_api';
    private $db_username = 'root';
    private $db_password = '';

    // Constructor to establish a database connection
    function __construct()
    {
        try {
            // Define Data Source Name (DSN)
            $dsn = "mysql:host={$this->db_host};dbname={$this->db_name};charset=utf8";

            // Create a new PDO instance for database connection
            $db_connection = new PDO($dsn, $this->db_username, $this->db_password);

            // Set PDO attributes for error mode
            $db_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            // Return the database connection
            return $db_connection;
        } catch (PDOException $e) {
            // If connection fails, catch and display the error message
            echo "Connection error " . $e->getMessage();
            exit; // Exit the script upon connection failure
        }
    }
}
?>

2. Main.php – This class contains several static methods for handling requests and generating JSON responses. Each method is commented to explain its purpose and functionality.

<?php
class Main
{
    // Checks the Request Method
    static function check($req)
    {
        // Check if the request method matches the provided method
        if ($_SERVER["REQUEST_METHOD"] === $req) {
            return true; // Return true if method matches
        }
        // If request method is not as expected, return error response
        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)
    {
        // Initialize response array
        $res = ["ok" => $ok];

        // Set HTTP status code if provided
        if ($status !== null) {
            http_response_code($status);
            $res["status"] = $status;
        }

        // Set message if provided
        if ($msg !== null) {
            $res["message"] = $msg;
        }

        // Set additional key-value pair if provided
        if ($value) {
            if ($key) {
                $res[$key] = $value;
            } else {
                $res["data"] = $value;
            }
        }

        // Output response as JSON and exit
        echo json_encode($res);
        exit;
    }

    // Returns the 404 Not found response
    static function _404()
    {
        static::json(0, 404, "Not Found!");
    }
}

3. Post.php – A class that performs CRUD operations on a database table named posts. Each method is thoroughly commented to explain its purpose and functionality.

<?php
// Include necessary files
require_once __DIR__ . "/Database.php";
require_once __DIR__ . "/Main.php";

use Main as Response;

class Post extends Database
{
    private $DB;

    // Constructor to initialize database connection
    function __construct()
    {
        $this->DB = Database::__construct();
    }

    // Function to filter input data
    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)
    {
        // Sanitize input data
        $title = $this->filter($title);
        $content = $this->filter($content);
        $author = $this->filter($author);

        try {
            // Prepare SQL statement
            $sql = "INSERT INTO `posts` (`title`,`content`,`author`) VALUES (:title,:content,:author)";
            $stmt = $this->DB->prepare($sql);

            // Bind parameters
            $stmt->bindParam(":title", $title, PDO::PARAM_STR);
            $stmt->bindParam(":content", $content, PDO::PARAM_STR);
            $stmt->bindParam(":author", $author, PDO::PARAM_STR);

            // Execute query
            $stmt->execute();

            // Get the last inserted ID
            $last_id = $this->DB->lastInsertId();
            
            // Send JSON response
            Response::json(1, 201, "Post has been created successfully", "post_id", $last_id);
        } catch (PDOException $e) {
            // Handle exception
            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 {
            // Construct SQL query
            $sql = "SELECT * FROM `posts`";

            // If post ID is provided
            if ($id !== false) {
                // Validate post ID
                if (is_numeric($id)) {
                    $sql = "SELECT * FROM `posts` WHERE `id`='$id'";
                } else {
                    // Return 404 response if invalid post ID
                    Response::_404();
                }
            }

            // Execute query
            $query = $this->DB->query($sql);

            // If query returned results
            if ($query->rowCount() > 0) {
                $allPosts = $query->fetchAll(PDO::FETCH_ASSOC);
                
                // If single post requested
                if ($id !== false) {
                    // Return single post if $return flag is set
                    if ($return) return $allPosts[0];
                    Response::json(1, 200, null, "post", $allPosts[0]);
                }
                // Return multiple posts
                Response::json(1, 200, null, "posts", $allPosts);
            }

            // If post ID does not exist
            if ($id !== false) {
                Response::_404();
            }

            // If no posts in the database
            Response::json(1, 200, "Please Insert Some posts...", "posts", []);
        } catch (PDOException $e) {
            // Handle exception
            Response::json(0, 500, $e->getMessage());
        }
    }

    // Update an existing post
    public function update(int $id, Object $data)
    {
        try {
            // Check if post exists
            $sql = "SELECT * FROM `posts` WHERE `id`='$id'";
            $query = $this->DB->query($sql);

            if ($query->rowCount() > 0) {
                $the_post = $query->fetch(PDO::FETCH_OBJ);

                // Sanitize and validate input data
                $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;

                // Prepare update SQL statement
                $update_sql = "UPDATE `posts` SET `title`=:title,`content`=:content,`author`=:author,`updated_at`=NOW() WHERE `id`='$id'";
                $stmt = $this->DB->prepare($update_sql);

                // Bind parameters
                $stmt->bindParam(":title", $title, PDO::PARAM_STR);
                $stmt->bindParam(":content", $content, PDO::PARAM_STR);
                $stmt->bindParam(":author", $author, PDO::PARAM_STR);

                // Execute update query
                $stmt->execute();

                // Send JSON response with updated post details
                Response::json(1, 200, "Post Updated Successfully", "post", $this->read($id, true));
            }

            // If post does not exist
            Response::json(0, 404, "Invalid Post ID.");

        } catch (PDOException $e) {
            // Handle exception
            Response::json(0, 500, $e->getMessage());
        }
    }

    // Delete a Post
    public function delete(int $id)
    {
        try {
            // Construct delete SQL query
            $sql =  "DELETE FROM `posts` WHERE `id`='$id'";
            $query = $this->DB->query($sql);

            // If deletion successful
            if ($query->rowCount() > 0) {
                // Send success response
                Response::json(1, 200, "Post has been deleted successfully.");
            }

            // If post ID does not exist
            Response::json(0, 404, "Invalid Post ID.");
        } catch (PDOException $e) {
            // Handle exception
            Response::json(0, 500, $e->getMessage());
        }
    }
}

Step 4: Create Rest of the PHP Files

After creating the classes, we need to create rest of the PHP files – config.php, create.php, get.php, update.php, delete.php. Let’s create one-by-one:

1. “config.php” the Configuration File

<?php
// Set default method to "GET" if $allow_method is not defined
if (!isset($allow_method)) $allow_method = "GET";

// Set headers for CORS (Cross-Origin Resource Sharing)
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");

// Include required files
require_once __DIR__ . "/classes/Main.php";
require_once __DIR__ . "/classes/Post.php";

2. “create.php” For Inserting New Posts

<?php
// Set the allowed method to "POST"
$allow_method = "POST";

// Include configuration file
require_once __DIR__ . "/config.php";

// Import necessary classes
use Main as Request;
use Main as Response;

// Check if the request method matches "POST"
if (Request::check("POST")) {
    // Get JSON data from request body
    $data = json_decode(file_get_contents("php://input"));

    // Check if required fields are present
    if (
        !isset($data->title) ||
        !isset($data->body) ||
        !isset($data->author)
    ) :
        // Define required fields
        $fields = [
            "title" => "Post title",
            "body" => "Post content",
            "author" => "Author name"
        ];
        // Send error response if required fields are missing
        Response::json(0, 400, "Please fill all the required fields", "fields", $fields);

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

    else :
        // Create a new Post object and call the create method
        $Post = new Post();
        $Post->create($data->title, $data->body, $data->author);
    endif;
}

POST: http://localhost/php-crud-api/create.php
Body (application/json):
{
    "title":"Post Title",
    "body":"Post Body",
    "author":"Author Name"
}
php create new post api test

3. “get.php” Fetch Posts from the Database

<?php
// Include configuration file
require_once __DIR__ . "/config.php";

// Import necessary classes
use Main as Request;

// Check if the request method is "GET"
if (Request::check("GET")) {
    // Create a new Post object
    $Post = new Post();

    // If an ID parameter is provided, fetch a single post
    if (isset($_GET['id'])) 
        $Post->read(trim($_GET['id']));
    
    // Fetch all posts
    $Post->read();
}

# 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}
Testing of fetching posts from database using PHP API

4. “update.php” For Update an Existing Post

<?php
// Set the allowed method to "PUT"
$allow_method = "PUT";

// Include configuration file
require_once __DIR__ . "/config.php";

// Import necessary classes
use Main as Request;
use Main as Response;

// Check if the request method is "PUT"
if (Request::check("PUT")) {
    // Get JSON data from request body
    $data = json_decode(file_get_contents("php://input"));

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

    // Check if the ID field is provided and valid
    if (!isset($data->id) || !is_numeric($data->id)) :
        // Send error response if ID is missing or invalid
        Response::json(0, 400, "Please provide the valid Post ID and at least one field.", "fields", $fields);
    endif;

    // Check for empty fields
    $isEmpty = true;
    $empty_fields =  [];

    // Loop through data to check for 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 all fields are empty or only the ID is provided, send error response
    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);
    }

    // Create a new Post object and call the update method
    $Post = new Post();
    $Post->update($data->id, $data);
}

PUT: http://localhost/php-crud-api/update.php
Body (application/json):
{
    "id": "post id",
    "field_name (title | body | author)" : "New Value"
}
Testing of PHP CRUD API update post

5. “delete.php” Delete an Existing Post

<?php
// Set the allowed method to "DELETE"
$allow_method = "DELETE";

// Include configuration file
require_once __DIR__ . "/config.php";

// Import necessary classes
use Main as Request;
use Main as Response;

// Check if the request method is "DELETE"
if (Request::check("DELETE")) {
    // Get JSON data from request body
    $data = json_decode(file_get_contents("php://input"));

    // Check if the ID field is provided and valid
    if (!isset($data->id) || !is_numeric($data->id)) :
        // Send error response if ID is missing or invalid
        Response::json(0, 400, "Please provide the valid Post ID");
    endif;

    // Create a new Post object and call the delete method
    $Post = new Post();
    $Post->delete($data->id);
}

DELETE: http://localhost/php-crud-api/delete.php
Body (application/json):
{
    "id": "post 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.. 🙏🏻❤️❤️

Leave a Reply

Your email address will not be published. Required fields are marked *

We use cookies to ensure that we give you the best experience on our website. Privacy Policy