How to Make CRUD REST API in PHP with MySQL

This is article we are learn a step by step PHP & MySQL REST API tutorial, In this article I am going to share with you how to create a PHP CRUD (Create, Read, Update, Delete) application REST full API with MySQL database.

What is API (Application Programming Interface)?

API means Application Programming Interface, and It is a set of routines, protocols, and tools for creating software applications. An API interface makes communication possible between various software components.

In software development, API is a URL that handles the data for the web application through HTTP Requests GET, POST, UPDATE & DELETE, and manages the CRUD operations.

See Also What is a REST API

PHP 8 API Project File Structure

This is the file structure of our PHP 8 REST API project; we created api, class, and config folders that contain the API and MySQL database configuration files.

REST API CRUD IN PHP

You can run your PHP project through Xampp, MAMP or WAMP however we are using the command line tool to start the PHP project.

File NamePurpose
create.phpInsert data in database using API
read.phpView/display data from database using API
delete.phpDelete data from database using API
update.phpUpdate data from database using API
employees.phpContains the all CRUD method in this file .
database.phpContains the database connection
PHP REST API using PHP

Create Database and Table by SQL query

We need to create database and table, so here I created webscodex database and employees products table. employees table holds the records which will be API CRUD action. You can simply create table as following SQL query.

--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8,
  `emp_id` varchar(100) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `job_title` varchar(100) NOT NULL,
  `department` varchar(100) NOT NULL,
  `gender` varchar(100) NOT NULL,
  `age` varchar(100) NOT NULL,
  `hire_date` date NOT NULL,
  `annual_salary` varchar(100) NOT NULL,
  `bonus` varchar(100) NOT NULL,
  `city` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` (`id`, `emp_id`, `full_name`, `job_title`, `department`, `gender`, `age`, `hire_date`, `annual_salary`, `bonus`, `city`) VALUES
(2, '2550', 'Vinod', 'Designer', 'IT/ software', 'male', '28', '2022-12-02', '25000', '1500', 'delhi'),
(3, '2555', 'Manish', 'developer', 'IT/ software ', 'male', '28', '2022-12-01', '22000', '1500', 'delhi'),
(4, '2650', 'Manish pushpakar', 'Web Designer', 'IT/ software', 'male', '28', '2022-12-02', '35000', '15000', 'Delhi'),
(5, '2550', 'Vinod gupta', 'Designer', 'IT/ software', 'male', '28', '2022-12-02', '25000', '1500', 'delhi'),
(6, '1542', 'sunny singh', 'Developer', 'IT', 'Male', '35', '2018-08-15', '860000', '15000', 'haidarabad'),
(7, '1543', 'Sikhar Dhavan ', 'Developer', 'IT', 'Male', '35', '2018-08-15', '860000', '15000', 'haidarabad');

Create Database Configuration File and Make Database Connection

In this step, we require to create database configuration file, here we will set database name, username and password. So let’s create config.php file on your root directory and put bellow code:

<?php

class Database
{

	private $serverName = "localhost";
	private $username   = "root";
	private $password   = "";
	private $database   = "webscodex";

    public $con = null;

    public function getConnection(){
        try { 
            $this->con = new mysqli($this->serverName, $this->username, $this->password, $this->database);
            if ($this->con->connect_error) {
                throw new Exception("Could not connect to database.");
            }
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }  
        return $this->con;
    }
}

?>

Create PHP Class File

Now, we will create the PHP Class by the name of Employee. The PHP Classes are used to execute Object-Oriented Programming in PHP. PHP Classes encapsulate the values of the table for the Database. We will define the values that are stored in the variable form in the SQL table.

The SQL table properties are associated with the Connection class that is added via the constructor class.

Each object class contains the CRUD method that is being operated via the PHP functions to perform the CREATE, READ, UPDATE & DELETE operations for the defined table rows.

Create class/employees.php file and define the CRUD methods inside the Employee class.

employees.php

<?php

class Employees
{

    // Connection
    private $con;

    // Db connection
    public function __construct($db)
    {
        $this->con = $db;
    }

    // GET ALL
    public function fetchAllData($table)
    {

        $query  = "SELECT * FROM  $table";
        
        $result = $this->con->query($query);
        
        if ($result) {
            
            if ($result->num_rows > 0) {
                
                $rows = $result->fetch_all(MYSQLI_ASSOC);
                
                $data = [
                    'status' => 200,
                    'message' => 'Employee record fetch successfully',
                    'data'  => $rows,
                ];
                
                header("HTTP/1.0 20 OK");
            }else{
                $data = [
                    'status' => 400,
                    'message' => 'No Employee found',
                ];
                header("HTTP/1.0 400 No Employee found");
            }
        }else{
            $data = [
                'status' => 500,
                'message' => 'Internal server error',
            ];
            header("HTTP/1.0 500 Internal server error");
        }
        return json_encode($data);
    }


    public function selectDataById($table, $id)
    {
        try {
            if (!empty($id)) {
                $stmt   = "SELECT * FROM $table Where id = '$id'";
                $result = $this->con->query($stmt);
                if ($result->num_rows > 0) {
                    $row = $result->fetch_all(MYSQLI_ASSOC);
                    $data = [
                        'status' => 200,
                        'message' => 'Single record fetch successfully',
                        'data'  => $row,
                    ];
                    header("HTTP/1.0 200 OK");
                } else {
                    $data = [
                        'status' => 404,
                        'message' => 'No Employee found',
                    ];
                    header("HTTP/1.0 404 No Employee found");
                }
            }else{
                $data = [
                    'status' => 404,
                    'message' => 'Employee Id is required',
                ];
                header("HTTP/1.0 404 Employee Id is required");  
            }
            return json_encode($data);
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }



    public function insertEmpyoyeeData($table, $post)
    {

        if (!empty($post)) {

            $emp_id     = $post['emp_id'];
            $full_name  = $post['full_name'];
            $job_title  = $post['job_title'];
            $department = $post['department'];
            $gender     = $post['gender'];
            $age        = $post['age'];
            $hire_date  = date('Y-m-d', strtotime($post['hire_date']));
            $annual_salary = $post['annual_salary'];
            $bonus      = $post['bonus'];
            $city       = $post['city'];

            if (!empty($emp_id) && !empty($full_name) && !empty($job_title) && !empty($department) && !empty($gender) && !empty($age) && !empty($hire_date) && !empty($city)) {

                $query = "INSERT INTO $table (emp_id, full_name, job_title, department, gender, age, hire_date, annual_salary, bonus, city) VALUES('$emp_id', '$full_name', '$job_title', '$department', '$gender', '$age', '$hire_date', '$annual_salary', '$bonus', '$city')";

                $result = $this->con->query($query);

                if ($result) {
                    $data = [
                        'status' => 200,
                        'message' => 'Employee created successfully.',
                    ];
                    header("HTTP/1.0 200 created");
                } else {
                    $data = [
                        'status' => 500,
                        'message' => 'Internal server error',
                    ];
                    header("HTTP/1.0 500 Internal server error");
                }
            }else{
                $data = [
                    'status' => 422,
                    'message' => 'All fields are required',
                ];
                header("HTTP/1.0 404 unprocessable entity");
            }
            
        }else{
            $data = [
                'status' => 500,
                'message' => 'Something went wrong',
            ];
            header("HTTP/1.0 404 Something went wrong");
        }
        return json_encode($data); 
    }


    public function deleteEmployeeId($table, $id)
    {
        try {
            if (!empty($id)) {
                $query  = "DELETE FROM $table WHERE id = '$id' LIMIT 1";
                $result = $this->con->query($query);
                if ($result) {
                    $data = [
                        'status' => 200,
                        'message' => 'Record deleted successfully',
                    ];
                    header("HTTP/1.0 200 OK");
                } else {
                    $data = [
                        'status' => 500,
                        'message' => 'Internal server error',
                    ];
                    header("HTTP/1.0 500 Internal server error");
                }
            } else {
                $data = [
                    'status' => 404,
                    'message' => 'Employee Id is required',
                ];
                header("HTTP/1.0 404 Not found");
            }
            return json_encode($data);
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }


    public function updateEmpyoyeeData($table, $post, $getId){

        if (!empty($post)) {

            if (isset($getId) && !empty($getId)) {

                $id         = $getId['id'];

                $emp_id     = $post['emp_id'];
                $full_name  = $post['full_name'];
                $job_title  = $post['job_title'];
                $department = $post['department'];
                $gender     = $post['gender'];
                $age        = $post['age'];
                $hire_date  = date('Y-m-d', strtotime($post['hire_date']));
                $annual_salary = $post['annual_salary'];
                $bonus      = $post['bonus'];
                $city       = $post['city'];
                
                $query="UPDATE $table SET emp_id='$emp_id', full_name='$full_name', job_title='$job_title', department='$department',
                gender='$gender', age='$age', hire_date='$hire_date', annual_salary='$annual_salary', bonus='$bonus', city='$city' WHERE id='$id'";
          
                $result = $this->con->query($query);

                if ($result) {
                    $data = [
                        'status' => 200,
                        'message' => 'Employee updated successfully.',
                    ];
                    header("HTTP/1.0 200 success");
                } else {
                    $data = [
                        'status' => 404,
                        'message' => 'Employee not updated',
                    ];
                    header("HTTP/1.0 404 Employee not updated");
                }
            }else{
                $data = [
                    'status' => 404,
                    'message' => 'Employee Id is not found',
                ];
                header("HTTP/1.0 404 Not found");
            }
        } else {
            $data = [
                'status' => 404,
                'message' => 'Something went wrong',
            ];
            header("HTTP/1.0 404 Something went wrong");
        }
        return json_encode($data); 
    }
}
?>

The Employees Class manage the REST API CRUD Operations

Function NamePurpose
__construct( )Make the database connections
fetchAllData( )Get/Fetch all records from database
selectDataById( )Get/Fetch single record from database
insertEmpyoyeeData( )Create/Insert data in database
deleteEmployeeId( )Delete single record from database
updateEmpyoyeeData( )Update single record.
PHP REST API using PHP

Fetch MySQL Records using PHP REST API

The following code retrieves all the records from MySQL table. So create read.php file in the API folder and place the following code.

read.php

<?php 

header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header("Access-Control-Allow-Methods: GET");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");


include_once '../inc/database.php';
include_once '../class/employees.php';


$database = new Database();
$db = $database->getConnection();
$empObj = new Employees($db);

$employeeTable = 'employees';

$requertMethod = $_SERVER['REQUEST_METHOD'];

if ($requertMethod == 'GET') {
    if (isset($_GET['id'])) {
        $empId = $_GET['id'];
        $getEmployeDetails = $empObj->selectDataById($employeeTable, $empId);
    } else {
        $getEmployeDetails = $empObj->fetchAllData($employeeTable);
    }
    echo $getEmployeDetails;
}else{
    $data = [
        'status' => 405,
        'message' => $requertMethod. ' Method now allowed',
    ];
    header("HTTP/1.0 405 Method now allowed");
    echo json_encode($data);
}

?>

Let us test the PHP API using Postman, open the Postman and use the following URL and click on the Send button to check the output.

METHODPOSTMAN URL
GEThttp://localhost/BlogPost/Create-PHP-CRUD-REST-API-with-MySQL/api/read.php
REST API CRUD IN PHP

Insert Single Record in MySQL Table

In this step, we will create PHP REST API endpoints to insert a record to MySQL table. Create a create.php file in the api directory and add the following code.

read.php

<?php
error_reporting(0);
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");


include_once '../inc/database.php';
include_once '../class/employees.php';


$database = new Database();
$db = $database->getConnection();
$empObj = new Employees($db);

$employeeTable = 'employees';

$requertMethod = $_SERVER['REQUEST_METHOD'];

if ($requertMethod == 'POST') {
    $formData = json_decode(file_get_contents("php://input"), true);
    if (empty($formData)) {
        $insertEmployeeRecord = $empObj->insertEmpyoyeeData($employeeTable, $_POST);
    }else{
        $insertEmployeeRecord = $empObj->insertEmpyoyeeData($employeeTable, $formData);
    }
    echo $insertEmployeeRecord;
} else {
    $data = [
        'status' => 405,
        'message' => $requertMethod . ' Method now allowed',
    ];
    header("HTTP/1.0 405 Method now allowed");
    echo json_encode($data);
}
?>
METHOD URL NAME
POSThttp://localhost/BlogPost/Create-PHP-CRUD-REST-API-with-MySQL/api/create.php
REST API CRUD IN PHP

Edit/Update MySQL Record using PHP API

In this step we are explain to you how to Update or Edit the data for specific record. We can use the PHP RESTful API to make the necessary update in the data that is stored in the MySQL database.

Create update.php file in the api folder and place the following code.

update.php

<?php
error_reporting(0);
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header("Access-Control-Allow-Methods: PUT");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");


include_once '../inc/database.php';
include_once '../class/employees.php';


$database = new Database();
$db = $database->getConnection();
$empObj = new Employees($db);

$employeeTable = 'employees';

$requertMethod = $_SERVER['REQUEST_METHOD'];

if ($requertMethod == 'PUT') {
    $formData = json_decode(file_get_contents("php://input"), true);
    $updateEmployeeRecord = $empObj->updateEmpyoyeeData($employeeTable, $formData, $_GET);
    echo $updateEmployeeRecord;
} else {
    $data = [
        'status' => 405,
        'message' => $requertMethod . ' Method now allowed',
    ];
    header("HTTP/1.0 405 Method now allowed");
    echo json_encode($data);
}
?>
METHOD URL NAME
PUThttp://localhost/BlogPost/Create-PHP-CRUD-REST-API-with-MySQL/api/update.php?id=5
REST API CRUD IN PHP

Remove/Delete Single Mysql Record using PHP API

Create file delete.php in api folder in this file we will write connection to delete or delete an employee record from MySQL data table using PHP 8 RESTful API. We’ll make an API call using the deleteEmployee() method.

delete.php

<?php

header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header("Access-Control-Allow-Methods: DELETE");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");


include_once '../inc/database.php';
include_once '../class/employees.php';


$database = new Database();
$db = $database->getConnection();
$empObj = new Employees($db);

$employeeTable = 'employees';

$requertMethod = $_SERVER['REQUEST_METHOD'];

if ($requertMethod == 'DELETE') {
    if (isset($_GET['id'])) {
        $empId = $_GET['id'];
        $deleteEmployee = $empObj->deleteEmployeeId($employeeTable, $empId);
        echo $deleteEmployee;
    }
} else {
    $data = [
        'status' => 405,
        'message' => $requertMethod . ' Method now allowed',
    ];
    header("HTTP/1.0 405 Method now allowed");
    echo json_encode($data);
}
?>
METHOD URL NAME
PUThttp://localhost/BlogPost/Create-PHP-CRUD-REST-API-with-MySQL/api/update.php?id=5
REST API CRUD IN PHP

Conclusion

That’s it, in this tutorial we learned how to create a simple CRUD RESTful API with PHP and MySQL. You can download the entire project file from.

You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions or problems about this tutorial, please comment on the  form below.😊

How to Make CRUD REST API in PHP with MySQL

Leave a Reply

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