create rest api php mysql – PHP 8 CRUD REST API with MySQL & PHP PDO Example

create rest api php mysql – Create Simple PHP 8 CRUD REST API with MySQL & PHP PDO – Configure a Database for Your PHP REST API. I will use MySQL to power our simple API.

create rest api php mysql

I have already learned how can we do Create, Read, Update and delete CRUD operation by using PHP PDO, Mysql and Ajax.

In this article you will learn how to create a REST API for an Android app using PHP and MySQL.

Step By Step create rest api php mysql with source code.

MySQL Database Configuration

database creation

CREATE TABLE IF NOT EXISTS `Member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_name` varchar(256) NOT NULL,
  `email_address` varchar(50),
  `point` int(11) NOT NULL,
  `serviceable` varchar(255) NOT NULL,
  `joined_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=19;

`Member` table in Database.

Populate data into the `Member` table.

INSERT INTO `Member` (`id`, `member_name`, `email_address`, `point`, `serviceable`, `joined_at`) VALUES 
(1, 'Kajal agraval', '[email protected]', 32, 'Manager', '2029-06-01 02:12:30'),
(2, 'Meera Sidhpura', '[email protected]', 29, 'keeper', '2023-03-03 01:20:10'),
(3, 'Sejal Ramani', '[email protected]', 36, 'devloper', '2028-09-20 03:10:25'),
(4, 'Bhavika Pethani', '[email protected]', 42, 'designer', '2027-04-11 04:11:12'),
(5, 'Jalpa dave', '[email protected]', 48, 'main hod', '2026-01-04 05:20:30'),
(6, 'ankita Sagpariya', '[email protected]', 37, 'CEO', '2022-01-10 06:40:10'),
(7, 'Monika chovatiya', '[email protected]', 44, 'Manager', '2022-05-02 02:20:30'),
(8, 'Virat Kohali', '[email protected]', 49, 'Engineer', '2025-01-04 05:15:35'),
(9, 'vishal Pandya', '[email protected]', 51, 'Devloper', '2026-01-02 02:20:30'),
(10, 'Sagar Dave', '[email protected]', 45, 'Manager OLD Teacher', '2028-02-01 06:22:50');

Make Database Connection

config/db_connection.php
place the following source code for create rest api php mysql.

<?php 
    class Database {
        private $host = "127.0.0.1";
        private $database_name = "pakainfo_v1";
        private $username = "root";
        private $password = "[email protected]";

        public $conn;

        public function getConnection(){
            $this->conn = null;
            try{
                $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->database_name, $this->username, $this->password);
                $this->conn->exec("set names utf8");
            }catch(PDOException $exception){
                echo "Database could not be connected: " . $exception->getMessage();
            }
            return $this->conn;
        }
    }  
?>

Create PHP Class

Now, I will make the PHP Class by the member_name of Member.
class/members.php
define the CRUD methods inside the Member class.

<?php
//create rest api php mysql
    class Member{

        private $conn;

        private $db_table = "Member";

        public $id;
        public $member_name;
        public $email_address;
        public $point;
        public $serviceable;
        public $joined_at;

        public function __construct($db){
            $this->conn = $db;
        }

        public function getMembers(){
            $sqlQuery = "SELECT id, member_name, email_address, point, serviceable, joined_at FROM " . $this->db_table . "";
            $stmt = $this->conn->prepare($sqlQuery);
            $stmt->execute();
            return $stmt;
        }

        public function createMember(){
            $sqlQuery = "INSERT INTO
                        ". $this->db_table ."
                    SET
                        member_name = :member_name, 
                        email_address = :email_address, 
                        point = :point, 
                        serviceable = :serviceable, 
                        joined_at = :joined_at";
        
            $stmt = $this->conn->prepare($sqlQuery);
        
            $this->member_name=htmlspecialchars(strip_tags($this->member_name));
            $this->email_address=htmlspecialchars(strip_tags($this->email_address));
            $this->point=htmlspecialchars(strip_tags($this->point));
            $this->serviceable=htmlspecialchars(strip_tags($this->serviceable));
            $this->joined_at=htmlspecialchars(strip_tags($this->joined_at));
        
            $stmt->bindParam(":member_name", $this->member_name);
            $stmt->bindParam(":email_address", $this->email_address);
            $stmt->bindParam(":point", $this->point);
            $stmt->bindParam(":serviceable", $this->serviceable);
            $stmt->bindParam(":joined_at", $this->joined_at);
        
            if($stmt->execute()){
               return true;
            }
            return false;
        }

        public function getSingleMember(){
            $sqlQuery = "SELECT
                        id, 
                        member_name, 
                        email_address, 
                        point, 
                        serviceable, 
                        joined_at
                      FROM
                        ". $this->db_table ."
                    WHERE 
                       id = ?
                    LIMIT 0,1";

            $stmt = $this->conn->prepare($sqlQuery);

            $stmt->bindParam(1, $this->id);

            $stmt->execute();

            $dataRow = $stmt->fetch(PDO::FETCH_ASSOC);
            
            $this->member_name = $dataRow['member_name'];
            $this->email_address = $dataRow['email_address'];
            $this->point = $dataRow['point'];
            $this->serviceable = $dataRow['serviceable'];
            $this->joined_at = $dataRow['joined_at'];
        }        

        public function updateMember(){
            $sqlQuery = "UPDATE
                        ". $this->db_table ."
                    SET
                        member_name = :member_name, 
                        email_address = :email_address, 
                        point = :point, 
                        serviceable = :serviceable, 
                        joined_at = :joined_at
                    WHERE 
                        id = :id";
        
            $stmt = $this->conn->prepare($sqlQuery);
        
            $this->member_name=htmlspecialchars(strip_tags($this->member_name));
            $this->email_address=htmlspecialchars(strip_tags($this->email_address));
            $this->point=htmlspecialchars(strip_tags($this->point));
            $this->serviceable=htmlspecialchars(strip_tags($this->serviceable));
            $this->joined_at=htmlspecialchars(strip_tags($this->joined_at));
            $this->id=htmlspecialchars(strip_tags($this->id));
        
            $stmt->bindParam(":member_name", $this->member_name);
            $stmt->bindParam(":email_address", $this->email_address);
            $stmt->bindParam(":point", $this->point);
            $stmt->bindParam(":serviceable", $this->serviceable);
            $stmt->bindParam(":joined_at", $this->joined_at);
            $stmt->bindParam(":id", $this->id);
        
            if($stmt->execute()){
               return true;
            }
            return false;
        }

        function deleteMember(){
            $sqlQuery = "DELETE FROM " . $this->db_table . " WHERE id = ?";
            $stmt = $this->conn->prepare($sqlQuery);
        
            $this->id=htmlspecialchars(strip_tags($this->id));
        
            $stmt->bindParam(1, $this->id);
        
            if($stmt->execute()){
                return true;
            }
            return false;
        }

    }
?>

The Member class manages the CRUD operation

  • __construct() — Makes the database connection ready.
  • getMembers() — Get all records.
  • getSingleMember() — Get single records.
  • createMember() — Create record.
  • updateMember() — Update record.
  • deleteMember() — Fetch single record.
Read Also:  How to Open URL in New Tab using KnockoutJS

Fetch MySQL Table Records using PHP REST API Endpoint

api -> read.php

<?php
//create rest api php mysql
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");
    
    include_once '../config/db_connection.php';
    include_once '../class/members.php';

    $db_connection = new Database();
    $db = $db_connection->getConnection();

    $member_rows = new Member($db);

    $stmt = $member_rows->getMembers();
    $member_rowCount = $stmt->rowCount();


    echo json_encode($member_rowCount);

    if($member_rowCount > 0){
        
        $memberArr = array();
        $memberArr["body"] = array();
        $memberArr["member_rowCount"] = $member_rowCount;

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            extract($row);
            $e = array(
                "id" => $id,
                "member_name" => $member_name,
                "email_address" => $email_address,
                "point" => $point,
                "serviceable" => $serviceable,
                "joined_at" => $joined_at
            );

            array_push($memberArr["body"], $e);
        }
        echo json_encode($memberArr);
    }

    else{
        http_response_code(404);
        echo json_encode(
            array("message" => "No record found.")
        );
    }
?>

Method Endpoint
GET https://www.pakainfo.com/api/read.php
Read Also:  PHP Compare Two Arrays Values

Get Single Row from MySQL Database via PHP API

api -> single_read.php
we are selecting the Member values from the MySQL table.

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

    include_once '../config/db_connection.php';
    include_once '../class/members.php';

    $db_connection = new Database();
    $db = $db_connection->getConnection();

    $member_row = new Member($db);

    $member_row->id = isset($_GET['id']) ? $_GET['id'] : die();
  
    $member_row->getSingleMember();

    if($member_row->member_name != null){
        // make array
        $emp_arr = array(
            "id" =>  $member_row->id,
            "member_name" => $member_row->member_name,
            "email_address" => $member_row->email_address,
            "point" => $member_row->point,
            "serviceable" => $member_row->serviceable,
            "joined_at" => $member_row->joined_at
        );
      
        http_response_code(200);
        echo json_encode($emp_arr);
    }
      
    else{
        http_response_code(404);
        echo json_encode("Member not found.");
    }
?>

Method Endpoint
GET https://www.pakainfo.com/api/single_read.php/?id=2

Insert or Add Single Record in MySQL Table

api -> create.php

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

    include_once '../config/db_connection.php';
    include_once '../class/members.php';

    $db_connection = new Database();
    $db = $db_connection->getConnection();

    $member_row = new Member($db);

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

    $member_row->member_name = $data->member_name;
    $member_row->email_address = $data->email_address;
    $member_row->point = $data->point;
    $member_row->serviceable = $data->serviceable;
    $member_row->joined_at = date('Y-m-d H:i:s');
    
    if($member_row->createMember()){
        echo 'Member joined_at successfully.';
    } else{
        echo 'Member could not be joined_at.';
    }
?>

Method Endpoint
POST https://www.pakainfo.com/api/create.php

Edit/Update MySQL Table

api -> update.php
place the following code.

<?php
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");
    header("Access-Control-Allow-Methods: POST");
    header("Access-Control-Max-Age: 3600");
    header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
    
    include_once '../config/db_connection.php';
    include_once '../class/members.php';
    
    $db_connection = new Database();
    $db = $db_connection->getConnection();
    
    $member_row = new Member($db);
    
    $data = json_decode(file_get_contents("php://input"));
    
    $member_row->id = $data->id;
    
    // member values
    $member_row->member_name = $data->member_name;
    $member_row->email_address = $data->email_address;
    $member_row->point = $data->point;
    $member_row->serviceable = $data->serviceable;
    $member_row->joined_at = date('Y-m-d H:i:s');
    
    if($member_row->updateMember()){
        echo json_encode("Member data updated.");
    } else{
        echo json_encode("Data could not be updated");
    }
?>

Method Endpoint
POST https://www.pakainfo.com/api/update.php
Read Also:  Multiple image slider in html source code

Remove/Delete Single Mysql Record using PHP API

api -> delete.php file
i will write the login to delete or remove the single member record from MySQL data table using PHP 8 RESTful API.

I will make the API call using the deletedMember() method.

<?php
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");
    header("Access-Control-Allow-Methods: POST");
    header("Access-Control-Max-Age: 3600");
    header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
    
    include_once '../config/db_connection.php';
    include_once '../class/members.php';
    
    $db_connection = new Database();
    $db = $db_connection->getConnection();
    
    $member_row = new Member($db);
    
    $data = json_decode(file_get_contents("php://input"));
    
    $member_row->id = $data->id;
    
    if($member_row->deleteMember()){
        echo json_encode("Member deleted.");
    } else{
        echo json_encode("Data could not be deleted");
    }
?>

Method Endpoint
DELETE https://www.pakainfo.com/api/delete.php

Don’t Miss : CRUD operations in PHP with Bootstrap


I hope you get an idea about create rest api php mysql.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.