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

by
create rest api php mysql

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.

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.

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.

Fetch MySQL Table Records using PHP REST API Endpoint

api -> read.php

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

Get Single Row from MySQL Database via PHP API

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

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

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.

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

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.

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.

Related posts:

  1. Simple Crud operations in php using mysql Example
  2. How to create Rest API in codeigniter?
  3. crud operations in php using mysqli – PHP Crud Operations – Select, Insert, Update, Delete – PHP Tutorial For Beginners
  4. mysql delete duplicate rows – 3 ways to Delete Duplicate Rows in MySQL
  5. mysql left join multiple tables – MySQL LEFT JOIN 3 tables Best 5 Examples
  6. mysql last inserted id – 3 ways to get last inserted id of a MySQL table

Leave a Comment