Build and Test PHP 7 CRUD REST API with MySQL

In this tutorial we will learn how to build CRUD REST API with PHP 7, Also, we will learn how to test PHP 7 REST API using Postman tool.

We will create REST API from scratch using PHP. We will also see how to sanitize the input values provided by users.

In subsequent steps, we will understand the usage of HTTP GET, POST, PUT, DELETE, and how to write SQL queries to interact with the database. All and all, we will create a complete CRUD REST API example.

Application Programming Interface – API

An application programming interface (API) is a computing interface which defines interactions between multiple software intermediaries. It defines the kinds of calls or requests that can be made, how to make them, the data formats that should be used, the conventions to follow, etc.
– wikipedia

What is REST API?

Representational state transfer (REST) is a software architectural style that defines a set of constraints to be used for creating Web services. Web services that conform to the REST architectural style, called RESTful Web services
source: wikipedia

Create PHP 7 REST API Project

Create a folder using following command:

mkdir php-crud-rest-api

Navigate to the project:

cd php-crud-rest-api

Create following folders and files inside the PHP REST API project folder:

# PHP CRUD REST API
# - api
# --- read.php
# --- create.php
# --- update.php
# --- delete.php
# --- single_user.php
# - class
# --- users.php
# - config
# --- database.php

You can MAMP or XAMPP to create the local server, go to MAMP/XAMPP > htdocs > php-crud-rest-api and keep your PHP REST API project folder in there.

Setting Up Database

Head over to PhpMyAdmin/SQL and create a database to handle data for CRUD operations.

CREATE DATABASE demo;

Get inside the database and execute the SQL command to generate the User table with columns.

CREATE TABLE IF NOT EXISTS `User` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `email` varchar(50),
  `age` int(11) NOT NULL,
  `profile` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=19;

We need to add some dummy records in the User table. So, head over to User/SQL tab and run the following command:

INSERT INTO `User` (`id`, `name`, `email`, `age`, `profile`, `created`) VALUES 
(1, 'James', '[email protected]', 22, 'Musician', '2013-07-02 03:15:40'),
(2, 'Harper', '[email protected]', 28, 'Programmer', '2014-04-02 03:25:20'),
(3, 'Mason', '[email protected]', 26, 'Cook', '2015-08-10 02:20:12'),
(4, 'Evelyn', '[email protected]', 32, 'Sales', '2015-09-12 03:21:22'),
(5, 'Ella', '[email protected]', 33, 'Farmer', '2017-02-03 04:25:20');

Create Database Connection with PHP Application

To establish a MySQL database connection in this project, we are using the PHP PDO approach. It stands for PHP Data Objects, and it is a lightweight, consistent, and robust approach.

Add the following code in config/database.php file to make the PHP and MySQL connection:

<?php 
    class DB {
        private $host = "localhost";
        private $db = "demo";
        private $username = "test";
        private $password = "4Mu99BhzK8dr4vF1";

        public $conn;

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

Create PHP Class

We need to create User class and define the CREATE, READ, UPDATE and DLETE methods. So, add the following code in class/users.php file:

<?php
    class User{

        // conn
        private $conn;

        // table
        private $dbTable = "User";

        // col
        public $id;
        public $name;
        public $email;
        public $age;
        public $profile;
        public $created;

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

        // GET Users
        public function getUser(){
            $sqlQuery = "SELECT id, name, email, age, profile, created FROM " . $this->dbTable . "";
            $stmt = $this->conn->prepare($sqlQuery);
            $stmt->execute();
            return $stmt;
        }

        // CREATE User
        public function createUser(){
            $sqlQuery = "INSERT INTO
                        ". $this->dbTable ."
                    SET
                        name = :name, 
                        email = :email, 
                        age = :age, 
                        profile = :profile, 
                        created = :created";
        
            $stmt = $this->conn->prepare($sqlQuery);
        
            // sanitize
            $this->name=htmlspecialchars(strip_tags($this->name));
            $this->email=htmlspecialchars(strip_tags($this->email));
            $this->age=htmlspecialchars(strip_tags($this->age));
            $this->profile=htmlspecialchars(strip_tags($this->profile));
            $this->created=htmlspecialchars(strip_tags($this->created));
        
            // bind data
            $stmt->bindParam(":name", $this->name);
            $stmt->bindParam(":email", $this->email);
            $stmt->bindParam(":age", $this->age);
            $stmt->bindParam(":profile", $this->profile);
            $stmt->bindParam(":created", $this->created);
        
            if($stmt->execute()){
               return true;
            }
            return false;
        }

        // GET User
        public function getSingleUser(){
            $sqlQuery = "SELECT
                        id, 
                        name, 
                        email, 
                        age, 
                        profile, 
                        created
                      FROM
                        ". $this->dbTable ."
                    WHERE 
                       id = ?
                    LIMIT 0,1";

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

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

            $stmt->execute();

            $dataRow = $stmt->fetch(PDO::FETCH_ASSOC);
            
            $this->name = $dataRow['name'];
            $this->email = $dataRow['email'];
            $this->age = $dataRow['age'];
            $this->profile = $dataRow['profile'];
            $this->created = $dataRow['created'];
        }        

        // UPDATE User
        public function updateUser(){
            $sqlQuery = "UPDATE
                        ". $this->dbTable ."
                    SET
                        name = :name, 
                        email = :email, 
                        age = :age, 
                        profile = :profile, 
                        created = :created
                    WHERE 
                        id = :id";
        
            $stmt = $this->conn->prepare($sqlQuery);
        
            $this->name=htmlspecialchars(strip_tags($this->name));
            $this->email=htmlspecialchars(strip_tags($this->email));
            $this->age=htmlspecialchars(strip_tags($this->age));
            $this->profile=htmlspecialchars(strip_tags($this->profile));
            $this->created=htmlspecialchars(strip_tags($this->created));
            $this->id=htmlspecialchars(strip_tags($this->id));
        
            // bind data
            $stmt->bindParam(":name", $this->name);
            $stmt->bindParam(":email", $this->email);
            $stmt->bindParam(":age", $this->age);
            $stmt->bindParam(":profile", $this->profile);
            $stmt->bindParam(":created", $this->created);
            $stmt->bindParam(":id", $this->id);
        
            if($stmt->execute()){
               return true;
            }
            return false;
        }

        // DELETE User
        function deleteUser(){
            $sqlQuery = "DELETE FROM " . $this->dbTable . " 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;
        }

    }
?>

GET User Records

To get all the user records from the MySQL database, add the given below code in the api/read.php file.

<!-- api/read.php -->

<?php
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");
    
    include_once '../config/database.php';
    include_once '../class/users.php';

    $database = new DB();
    $db = $database->getConnection();

    $items = new User($db);

    $stmt = $items->getUsers();
    $itemCount = $stmt->rowCount();


    echo json_encode($itemCount);

    if($itemCount > 0){
        
        $userArr = array();
        $userArr["body"] = array();
        $userArr["itemCount"] = $itemCount;

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            extract($row);
            $e = array(
                "id" => $id,
                "name" => $name,
                "email" => $email,
                "age" => $age,
                "profile" => $profile,
                "created" => $created
            );

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

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

Get Single Record

To get a single user record from the database, we need to place the following code in the api/single_user.php file.

<?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/database.php';
    include_once '../class/users.php';

    $database = new DB();
    $db = $database->getConnection();

    $item = new User($db);

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

    if($item->name != null){
        $user_Arr = array(
            "id" =>  $item->id,
            "name" => $item->name,
            "email" => $item->email,
            "age" => $item->age,
            "profile" => $item->profile,
            "created" => $item->created
        );
      
        http_response_code(200);
        echo json_encode($user_Arr);
    }
      
    else{
        http_response_code(404);
        echo json_encode("User record not found.");
    }
?>

Create Record with PHP 7 REST API

To create a user record in the database, we need to use PHP REST API. So, add the following code in the api/create.php file.

<?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/database.php';
    include_once '../class/users.php';

    $database = new DB();
    $db = $database->getConnection();

    $item = new User($db);

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

    $item->name = $data->name;
    $item->email = $data->email;
    $item->age = $data->age;
    $item->profile = $data->profile;
    $item->created = date('Y-m-d H:i:s');
    
    if($item->createUser()){
        echo 'User created.';
    } else{
        echo 'User was not created.';
    }
?>

Update Record in Database

We have to insert the given below code in the api/update.php file to update a user record in the MySQL database.

<?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/database.php';
    include_once '../class/users.php';
    
    $database = new DB();
    $db = $database->getConnection();
    
    $item = new User($db);
    
    $data = json_decode(file_get_contents("php://input"));
    
    $item->id = $data->id;
    
    // employee values
    $item->name = $data->name;
    $item->email = $data->email;
    $item->age = $data->age;
    $item->profile = $data->profile;
    $item->created = date('Y-m-d H:i:s');
    
    if($item->updateEmployee()){
        echo json_encode("User record updated.");
    } else{
        echo json_encode("User record could not be updated.");
    }
?>

Delete Record using RESTful API

In order to delete a user record from the database, we have to place the following code in api/delete.php file.

<?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/database.php';
    include_once '../class/users.php';
    
    $database = new DB();
    $db = $database->getConnection();
    
    $item = new User($db);
    
    $data = json_decode(file_get_contents("php://input"));
    
    $item->id = $data->id;
    
    if($item->deleteUser()){
        echo json_encode("User deleted.");
    } else{
        echo json_encode("Not deleted");
    }
?>

TEST PHP 7 CRUD REST API with Postman

We need to test the PHP REST APIs we have developed so far, so for testing API we are using Postman API testing tool.

Fetch User Records

To get the records from database, use the following endpoint:

Fetch User Records

Get Single Record

To get the single user record from database, use the following API:

Get Single Record

Add Record in Database

To add the user record in database, use the following endpoint:

Add Record in Database

Delete Record

Let’s test out how to delete the user record from MySQL database, use the following endpoint:

Delete Record

Conclusion

Finally, PHP 7 CRUD REST API tutorial is over. This tutorial walked us through on how to build simple RESTful API using PHP 7, we have also learned how to create a PHP REST API example project from scratch.

Download the full code from GitHub.