CRUD Operation Rest API using PHP & MYSQL

by

In this tutorial, You will learn to create the CRUD operation rest API using PHP & MySQL which will be very useful to integrate with frontend languages such as react js, angular js & more.

Step to Create Crud Restful API in PHP & MYSQL

Now, Let’s start coding with the given some simple steps –

Create a Folder Structure

First of all, Create the following folder structure in your system.

  • database.php – This file is created for the database connection
  • api.php – This file is created for the API requests such as post, put, get & delete.
  • CrudApi.php – This file is created for writing code with a custom class for crud operation
crud-opration/
     |_rest/
     |  |__database.php
     |  |__CrudApi.ph[
        |__api.php

Create Database & Table

Now, Create a database & Table in the PhpMyAdmin the the following given name –

Database Name – restapi

CREATE DATABASE restapi

Table Name – developers

CREATE TABLE `developers` (
  `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `firstName` varchar(255) DEFAULT NULL,
  `lastName` varchar(255) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
)

Connect to Database

No, Connect your PHP to the MySQL database with this code.

File Name – database.php

<?php

$host = "localhost";
$user = "root";
$password = "";
$database = "phprestapi";

$conn = new mysqli($host, $user, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Create CRUD API Class

File Name – CrudApi.php

<?php
class CrudApi {

    private $conn;
    private $categoryTable;

    public function __construct($conn) {
        $this->conn = $conn;
        $this->tableName = 'crud';
    }

    public function validate($firstName, $lastName, $gender, $salary) {

        $error = false;
        $errMsg = null;

        if(empty($firstName)) {
            $errMsg = "FirstName is empty";
            $error = true;
        }
        if(empty($lastName)) {
            $errMsg = "LastName is empty";
            $error = true;
        } 
        if(empty($gender)) {
            $errMsg = "gender is empty";
            $error = true;
        } 
        if(empty($salary)) {
            $errMsg = "salary is empty";
            $error = true;
        } 

        $errorInfo = [
            "error" => $error,
            "errMsg" => $errMsg
        ];
        
        return $errorInfo;
    }

    public function create() {

        $data = json_decode(file_get_contents("php://input"), true);
       
        $firstName = $data['firstName'];
        $lastName = $data['lastName'];
        $gender   = $data['gender'];
        $salary   = $data['salary'];
        
        $validate = $this->validate($firstName, $lastName, $gender, $salary);
        $success = false;

        if (!$validate['error']){

            $query = "INSERT INTO ";
            $query .= $this->tableName; 
            $query .= " (firstName, lastName, gender, salary) ";
            $query .= " VALUES (?,?,?,?)";

            $stmt = $this->conn->prepare($query);
            $stmt->bind_param("sssi", $firstName, $lastName, $gender, $salary);
            
            if ($stmt->execute()) {
                $status = 200;
                $msg = "Data is created successfully";
            } else{
                $status = 400;
                $msg = $this->conn->error;
            }

        } else {
          $status = 401;
          $msg = $validate['errMsg'];
        }
         
         $data = [
            'status' => $status,
            'msg' => $msg,
            'data' => $data
         ];

         return json_encode($data);
    }

    public function get() {

        $data = [];
        $query = "SELECT id, firstName, lastName, gender, salary FROM ";
        $query .= $this->tableName;
        $query .= " ORDER BY id DESC";
        
        $result = $this->conn->query($query);

        if ($result) {
            while ($row = $result->fetch_assoc()) {
                $data[] = $row;
            }
            $result->free();
        }

        if(empty($data)) {

            $data = [
                'status' => 404,
                'data' => 'Recod not found'
             ];
        } else {
            $data = [
                'status' => 200,
                'data' => $data
             ];
        }


         return json_encode($data);
        
    }

    public function getById($id) {

        $data = [];
    
        $query = "SELECT id, firstName, lastName, gender, salary  FROM ";
        $query .= $this->tableName; 
        $query .= " WHERE id=?";

        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("i", $id);
       
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $rows= $result->fetch_assoc();
            if($rows) {
                $status = 200;
                $data = $rows;

            } else {
                $status = 404;
                $data = "Record not found";
            }
        } 

        $data = [
            'status' => $status,
            'data' => $data
         ];

         return json_encode($data);
    }

    public function updateById($id) {

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

        $firstName = $data['firstName'];
        $lastName = $data['lastName'];
        $gender   = $data['gender'];
        $salary   = $data['salary'];
        $validate = $this->validate($firstName, $lastName, $gender, $salary);
        $success = false;

        if (!$validate['error']){

            $query = "UPDATE ";
            $query .= $this->tableName; 
            $query .= " SET firstName = ?, lastName = ?, gender = ?, salary = ? ";
            $query .= " WHERE id = ?";

            $stmt = $this->conn->prepare($query);
            $stmt->bind_param("sssii", $firstName, $lastName, $gender, $salary, $id);
            
            if ($stmt->execute()) {
                $status = 200;
                $msg = "Data is updated successfully";
            } else{
                $status = 400;
                $msg = $this->conn->error;
            }

        } else {
          $status = 401;
          $msg = $validate['errMsg'];
        }
         
         $data = [
            'status' => $status,
            'msg' => $msg,
            'data' => $data
         ];

         return json_encode($data);
    }

    public function deleteById($id) {

            $getData = $this->getById($id);
            $rows = json_decode($getData, true);
         
            if(404 == $rows['status']){
               return $getData;
            } else {

                $query = "DELETE FROM ";
                $query .= $this->tableName; 
                $query .= " WHERE id = ?";
                
                $stmt = $this->conn->prepare($query);
                $stmt->bind_param("i", $id);
                echo "dddd";
                if ($stmt->execute()) {

                $data = [
                    'status' => 200,
                    'msg'   => 'Record is deleted successfully',
                    'data' => $rows['data']
                 ];

                 return json_encode($data);
              }
          
        } 

        
    }
    
}



?>

 

Create CRUD API Action

File Name – api.php

<?php
require_once 'database.php';
require_once 'CrudApi.php';

$crudApi = new CrudApi($conn);

header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: POST, PUT, GET, DELETE, OPTIONS");
header("Access-Control-Allow-Headers: Content-Type");
header("Content-Type: application/json");

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit();
}

// HTTP method handling
$method = $_SERVER['REQUEST_METHOD'];

switch ($method) {

    case 'GET':
        // Read data
        $id = isset($_GET['id']) ? $_GET['id'] : null;
        if($id){
            echo $crudApi->getById($id);
        } else {
            echo $crudApi->get();
        }
        
        break;
    case 'POST':
        echo $crudApi->create();
        break;
    case 'PUT':
        $id = isset($_GET['id']) ? $_GET['id'] : null;
        if($id) {
            echo $crudApi->updateById($id);
        } else {
            echo "Request URL not found";
        }
        break;

    case 'DELETE':
        // Delete data
        $id = isset($_GET['id']) ? $_GET['id'] : null;
        
        if($id) {
          
            echo $crudApi->deleteById($id);
        } else {
            echo "Request URL not found";
        }
        break;
    default:
        header('HTTP/1.0 405 Method Not Allowed');
        break;
}


?>

 

Run CRUD API With Postman

Now, open your Postman, and run the following API request

For creating records –

method – POST

URL

http://localhost/codingstatus/php/rest/crud-operations/api.php

Payload – JSON data

{
    "firstName": "Jhon",
    "lastName": "Doe",
    "gender": "male",
    "salary": "45"
}

For getting records

method – GET

URL –

http://localhost/codingstatus/php/rest/crud-operations/api.php

For updating recored

method – PUT

URL –

http://localhost/codingstatus/php/rest/crud-operations/api.php?id=46

Payload – JSON Data

{
    "firstName": "Amit",
    "lastName": "Kumar",
    "gender": "male",
    "salary": "123"
}

For Deleting Record –

Method – DELETE

URL –

http://localhost/codingstatus/php/rest/crud-operations/api.php?id=46