RESTful API CRUD using PHP and MySQL Database Tutorial

Submitted by oretnom23 on Wednesday, November 16, 2022 - 16:27.

Introduction

In this tutorial, you will learn how to create a RESTful API CRUD using PHP Language and MySQL Database. This tutorial aims to provide a reference or guide to the students and for those new to PHP Language about implementing a RESTful API on their projects. Here, snippets are provided and the source code zip file is free to download.

What is REST API?

REST API is also known as RESTful API. REST stands for REpresentational State Transfer. It is a REST architectural style-compliant application programming interface (API or web API) that enables communication with RESTful online services.

REST API is usually implemented in a project that allows the client to manage or retrieve data from the server for their own use. For example, Website 1 wants to dynamically retrieve data from Website 2 and display it on its own website. By creating a REST API on Website 2, Website 1 can create a request to the server of website 2 over HTTP and retrieve the return data.

Creating a Simple RESTful API in PHP

Here, we will create a Simple CRUD REST API using PHP and MySQL as the Database.

Getting Started

Since we will be using PHP Scripts and MySQL Database in this tutorial, kindly download and install a XAMPP or any equivalent software on your local machine to run PHP Scripts and for the local MySQL Database. For those using XAMPP, don't forget to start the Apache and MySQL servers at the XAMPP's Control Panel.

Database

First, we will create a database naming dummy_db. Next, copy the MySQL Schema below and run it into your newly created database SQL Page.

CREATE TABLE `programming_languages` (
    `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `abbrv` varchar(250) NOT NULL,
    `name` text NOT NULL,
    `short_description` text NOT NULL,
    `created_at` datetime NOT NULL DEFAULT current_timestamp(),
    `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
CREATE TABLE `token_list` (
    `id` int(30) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `token` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
      

Executing the MySQL Schema above to your created database will result in creating 2 new tables which are programming_languages and token_list. The token_list is where the valid tokens will be stored and programming_languages is the storage of the sample data that will manage using REST API.

Database Connection

Next, we'll create the database base connection script. In your source code folder, create a new PHP File named db-connect.php and open it with your preferred text editor. Then, write your DB Connection Script on it. The below snippet is the one I am using on my end.

<?php
$host = "localhost";
$username = "root";
$pw = "";
$db_name = "dummy_db";
 
$conn = new mysqli($host, $username, $pw, $db_name);
 
if(!$conn){
    die("Database Connection Failed");
}

Generate Sample Tokens

Next, we'll create a simple script for generating valid tokens. The tokens will be used for validating the request in REST API as a simple security of the data using the said API. Create a new PHP File and save it as generate-tokens.php. Use the following snippet for this file.

<?php
require_once('db-connect.php');
 
/**
    * truncate all tokens
    */
 
$truncate_sql = "TRUNCATE `token_list`";
$truncate_tbl = $conn->query($truncate_sql);
if($truncate_tbl){
    print("Token Table has been truncated successfully.<br>");
}else{
    print("Truncating Table Failed due to some reasons. Error:". $conn->error);
}
 
$tokens = [];
 
for($i = 0; $i < 5; $i++){
    while(true){
        $key = mt_rand(111, mt_getrandmax());
        $token = md5($key);
        if(!in_array($token, $tokens)){
            $tokens[] = addslashes($conn->real_escape_string($token));
            break;
        }
    }
}
 
/**
    * Inserting Tokens to database
    */
 
$insert_sql = "INSERT INTO `token_list` (`token`) VALUES";
$insert_sql .= "('". (implode("'), ('", $tokens)) ."')"; 
$insert = $conn->query($insert_sql);
if($insert_sql){
    print("Tokens has been generated.<br>");
    echo "<pre>";
    print_r(json_encode($tokens, JSON_PRETTY_PRINT));
    echo "</pre>";
}else{
    print("Inserting Tokens Failed. Error:". $conn->error);
}
$conn->close();

After creating the file, browse the script on your web browser i.e. http://localhost/php-restful-api/generate-tokens.php. Then, on your database, check if the generated tokens are successfully inserted on the `token_list` table.

Creating the PHP CRUD REST API

Next, we will now create the PHP Script that serves as the API on our server. Create a new PHP File and save it as api.php. The below snippet is PHP Script I created using the Object-oriented programming (OOP) approach. It contains all the CRUD processes on the database.

<?php 
header("Content-Type:application/json");
require_once('db-connect.php');
 
class API {
    public $db;
    function __construct(){
        global $conn;
        $this->db = $conn;
    }
 
    /**
        * Verify Token
        */
    
    function verify_token($token=""){
        $validity = false;
        if(!empty($token)){
            $check_token = $this->db->query("SELECT id FROM `token_list` where `token` = '{$token}'")->num_rows;
            if($check_token > 0)
            return true;
        }
    }
 
    /**
        * POST Request
        * - insert data to database
        */
 
    function new_language(){
        if(isset($_SERVER['REQUEST_METHOD']) == "POST"){
            $data  = "";
            foreach($_POST as $k => $v){
                if(!is_numeric($v))
                $v = addslashes($this->db->real_escape_string($v));
 
                if(!empty($data)) $data .= ", ";
                $data .= " `{$k}` = '{$v}'";
            }
 
            $insert_sql = "INSERT INTO `programming_languages` set {$data}";
            // return ['sql' => $insert_sql, 'POST' => $_REQUEST];
            $insert = $this->db->query($insert_sql);
            if($insert){
                $resp['status'] = 'success';
                $resp['message'] = 'New Data has been saved successfully.';
                $id = $this->db->insert_id;
                $get = $this->db->query("SELECT * FROM `programming_languages` where `id` = '{$id}'")->fetch_assoc();
                $resp['result'] = $get;
            }else{
                $resp['status'] = 'failed';
                $resp['error'] = $this->db->error;
            }
            return $resp;
 
        }else{
            $resp['status'] = 'failed';
            $resp['error'] = "This API Method must contain valid POST Data";
            return $resp;
        }
    }
 
    /**
        * POST Request
        * - List all Table data to database
        */
 
    function get_languages(){
        $sql = "SELECT * FROM `programming_languages`";
        $query = $this->db->query($sql);
        $resp['status'] = 'success';
        $resp['num_rows'] = $query->num_rows;
        $resp['result'] = $query->fetch_all(MYSQLI_ASSOC);
        return $resp;
    }
    /**
        * POST Request
        * - List all Table data to database
        */
 
    function get_language_by_id(){
        extract($_GET);
        if(!isset($id)){
            $resp['status'] = 'failed';
            $resp['error'] = "This API Method requires an ID Parameter";
        }else{
            if(!is_numeric($id)){
                $resp['status'] = 'failed';
                $resp['error'] = "ID must be an integer.";
            }else{
                $sql = "SELECT * FROM `programming_languages` where id = {$id}";
                $query = $this->db->query($sql);
                if($query->num_rows > 0){
                    $resp['status'] = 'success';
                    $resp['result'] = $query->fetch_assoc();
                }else{
                    $resp['status'] = 'failed';
                    $resp['error'] = "Invalid given ID.";
                }
            }
            
        }
        return $resp;
 
    }
 
    /**
        * POST Request
        * - Update data from database
        */
 
    function update_language(){
        if(isset($_SERVER['REQUEST_METHOD']) == "POST"){
            $id = $_POST['id'];
            if(!isset($id)){
                $resp['status'] = 'failed';
                $resp['error'] = "This API Method requires an ID Parameter";
            }else{
 
                $data  = "";
                foreach($_POST as $k => $v){
                    if($k == 'id')
                    continue;
                    if(!is_numeric($v))
                    $v = addslashes($this->db->real_escape_string($v));
 
                    if(!empty($data)) $data .= ", ";
                    $data .= " `{$k}` = '{$v}'";
                }
 
                $update_sql = "UPDATE `programming_languages` set {$data} where id = '{$id}'";
                // return ['sql' => $update_sql, 'POST' => $_REQUEST];
                $update = $this->db->query($update_sql);
                if($update){
                    $resp['status'] = 'success';
                    $resp['message'] = 'Data has been saved successfully.';
                    $get = $this->db->query("SELECT * FROM `programming_languages` where `id` = '{$id}'")->fetch_assoc();
                    $resp['result'] = $get;
                }else{
                    $resp['status'] = 'failed';
                    $resp['error'] = $this->db->error;
                }
            }
        }else{
            $resp['status'] = 'failed';
            $resp['error'] = "This API Method must contain valid POST Data";
        }
        return $resp;
    }
 
    /**
        * POST Request
        * - Delete data from database
        */
 
    function delete_language(){
        if($_SERVER['REQUEST_METHOD'] == "DELETE"){
        if(!isset($_GET['id'])){
            $resp['status'] = "error";
            $resp['error'] = "The request must contain an ID Paramater.";
        }else{
            $id = $_GET['id'];
            if(!is_numeric($id)){
                $resp['status'] = "error";
                $resp['error'] = "The ID Parameter must be an integer.";
            }else{
                $delete = $this->db->query("DELETE FROM `programming_languages` where `id` = {$id}");
                if($delete){
                    $resp['status'] = 'success';
                    $resp['message'] = "Programming Language with the #{$id} ID has been deleted successfully";
                }else{
                    $resp['status'] = "error";
                    $resp['error'] = "Deleting Data from Database Failed! Error:". $this->db->error;
                }
            }
        }
        }else{
        $resp['status'] = "error";
        $resp['error'] = "The request method is invalid.";
        }
        return $resp;
    }
    
    function __destruct(){
        $this->db->close();
    }
}
$api = new API();
$action = isset($_GET['action']) ? $_GET['action'] : '';
 
/**
    * Get headers
    */
$headers = null;
if (isset($_SERVER['Authorization'])) {
    $headers = trim($_SERVER["Authorization"]);
}
else if (isset($_SERVER['HTTP_AUTHORIZATION'])) { //Nginx or fast CGI
    $headers = trim($_SERVER["HTTP_AUTHORIZATION"]);
} elseif (function_exists('apache_request_headers')) {
    $requestHeaders = apache_request_headers();
    // Server-side fix for bug in old Android versions (a nice side-effect of this fix means we don't care about capitalization for Authorization)
    $requestHeaders = array_combine(array_map('ucwords', array_keys($requestHeaders)), array_values($requestHeaders));
    //print_r($requestHeaders);
    if (isset($requestHeaders['Authorization'])) {
        $headers = trim($requestHeaders['Authorization']);
    }
}
 
/**
    * Check if token has provided
    */
$api_key = null;
if(preg_match('/Bearer/i', $headers)){
    $bearer_count = preg_match_all('/Bearer\s(.*)/i',$headers, $bearer_matches);
    if(isset($bearer_matches[1][0]))
    $api_key = trim($bearer_matches[1][0]);
}
if(!is_null($api_key) && !empty($api_key)){
    $verify_api = $api->verify_token($api_key);
    if(!$verify_api){
        echo json_encode([
            'status' => 'failed',
            'error' => "API token is Invalid."
        ]);
        exit;
    }
}else{
    echo json_encode([
        'status' => 'failed',
        'error' => "API Token is Required."
    ]);
    exit;
}
if(method_exists($api, $action)){
    $exec = $api->$action();
    echo json_encode($exec);
}else{
    echo json_encode([
        'status' => 'failed',
        'error' => "API [{$action}] Method does not exists."
    ]);
}
?>

Example RESTful API Execution using CURL

Here is the sample syntax for executing the CRUD Operation of the API.

Insert New Data (new_language())

In this operation, the client must execute their request using the POST method. The request must contain abbvr, name, and short_description POST data.

    curl -X POST "http://localhost/php-restful-api/api.php?action=new_language" \
    -H "Authorization: Bearer 568asd548as" \
    -H "Accept: application/json"
    --data "abbrv=PHP&name=Hypertext Preprocessor&short_description=Sample Description Only."

Result

Update Existing Data (update_language())

In this operation, the client must execute their request using the POST method. The request must contain id, abbvr, name, and short_description POST data.

    curl -X POST "http://localhost/php-restful-api/api.php?action=new_language" \
    -H "Authorization: Bearer 568asd548as" \
    -H "Accept: application/json"
    --data "id=4abbrv=VB.NET&name=Visual Basic .NET&short_description=VB.NET - updated."

Result

Get All Data (get_languages())

In this operation, client must execute their request using the GET method.

    curl -X POST "http://localhost/php-restful-api/api.php?action=get_languages" \
    -H "Authorization: Bearer 568asd548as" \
    -H "Accept: application/json"

Result

Get Single Data using ID (get_language_by_id())

In this operation, the client must execute their request using the GET method. The request must contain an ID parameter.

    curl -X POST "http://localhost/php-restful-api/api.php?action=get_language_by_id&id=1" \
    -H "Authorization: Bearer 568asd548as" \
    -H "Accept: application/json"

Result

Delete Data (delete_language())

In this operation, the client must execute their request using the GET method. The request must contain an ID parameter.

    curl -X POST "http://localhost/php-restful-api/api.php?action=delete_language&id=1" \
    -H "Authorization: Bearer 568asd548as" \
    -H "Accept: application/json"

Result

DEMO VIDEO

That's it! Now you can test the PHP CRUD RESTful API we created on your end and see if it works properly. You also test it using some online REST API Tester sites. I have also provided the complete source code file I created for this tutorial. You can download it for free by clicking the Download Button below this article.

That's the end of this tutorial! I hope this PHP CRUD RESTful API using PHP and MySQL Tutorial will help you with what you are looking for and that you'll find this useful for your current and future PHP Projects.

Happy Coding :)

Add new comment

4458 views