REST API CRUD Example in PHP, MySQL

In this tutorial I am going to show you how we can work with RESTful webservice or REST webservice in PHP PDO, MySQL.

Introduction

The most important concept in REST is resources, which are identified by global IDs — typically using URIs. Client applications use HTTP methods (GET/ POST/ PUT/ DELETE) to manipulate the resource or collection of resources. A RESTful Web service is implemented using HTTP and the principles of REST. Typically, a RESTful Web service should define the following aspects:

The base/root URI for the Web service such as http://<host>/<appcontext/contextpath>/<url pattern>/<resources>.
The MIME type of the response data supported, which are JSON/XML/TEXT/HTML etc.
The set of operations supported by the service. (for example, POST, GET, PUT or DELETE).

You may like to read Codeigniter REST + ReactJS CRUD Example
Methods

HTTP methods are mapped to CRUD (create, read, update and delete) actions for a resource. Although you can make slight modifications such as making the PUT method to create or update, the basic patterns are listed as follows.

HTTP GET: Get/List/Retrieve an individual resource or a collection of resources.
HTTP POST: Create a new resource or resources.
HTTP PUT: Update an existing resource or collection of resources.
HTTP DELETE: Delete a resource or collection of resources.

Step 1. Create below MySQL table

USE `roytuts`;

/*Table structure for table `department` */

DROP TABLE IF EXISTS `department`;

CREATE TABLE `department` (
  `dept_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert  into `department`(`dept_id`,`dept_name`) values 
(10,'ACCOUNTING'),
(20,'RESEARCH'),
(30,'SALES'),
(40,'OPERATIONS');

Step 2. Create below Db class under directory <project root directory>/config/Db.php

<?php

class Db {

    private $host = "localhost";
    private $db_name = "roytuts";
    private $username = "root";
    private $password = "root";
    public $conn;

    // get the database connection
    public function getConnection() {
        $this->conn = null;

        try {
            $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
            $this->conn->exec("set names utf8");
        } catch (PDOException $exception) {
            echo "Database connection error: " . $exception->getMessage();
        }

        return $this->conn;
    }

}

?>

Please do not forget to change the database credentials as per your database. The above class will return connection object for the MySQL database.

Step 3. Create below Department class under <project root directory>/object/Department.php

<?php

/**
 * Description of Department
 *
 * @author https://roytuts.com
 */
class Department {

    // database connection and table name
    private $conn;
    private $table_name = "department";
    // object properties
    public $id;
    public $name;

    // constructor with $db as database connection
    public function __construct($db) {
        $this->conn = $db;
    }
}

The above class represents the department object for department table.

Step 4. Now we will read all departments from the database. So create below file under <project root directory>/department/read.php

<?php

// required headers
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

// include database and object files
include_once '../config/Db.php';
include_once '../object/Department.php';

// instantiate database and department object
$database = new Db();
$db = $database->getConnection();

// initialize object
$department = new Department($db);

// query department
$stmt = $department->read();
$num = $stmt->rowCount();

// check if more than 0 record found
if ($num > 0) {
    // department array
    $department_arr = array();
    $department_arr["records"] = array();

    // retrieve table contents
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        // extract row
        extract($row);
        $department_item = array(
            "id" => $row['dept_id'],
            "name" => $row['dept_name']
        );
        array_push($department_arr["records"], $department_item);
    }
    echo json_encode($department_arr);
} else {
    echo json_encode(
            array("message" => "No products found.")
    );
}
?>

In the above file you see, we have called read() method of department object, so let’s create read() method inside Department class.

Step 5. Add below read() method to Department.php

// read departments
    function read() {
        // query to select all
        $query = "SELECT d.dept_id, d.dept_name
            FROM
                " . $this->table_name . " d
            ORDER BY
                d.dept_id";
        // prepare query statement
        $stmt = $this->conn->prepare($query);
        // execute query
        $stmt->execute();
        return $stmt;
    }

When you hit the URL GET http://localhost/<project root directory>/read.php in REST client you see below output in the browser

[
	{
		"id": "10",
		"name": "ACCOUNTING"
	},
	{
		"id": "20",
		"name": "RESEARCH"
	},
	{
		"id": "30",
		"name": "SALES"
	},
	{
		"id": "40",
		"name": "OPERATIONS"
	}
]

Step 6. Now we will create a new department to the database. So create below file under <project root directory>/department/create.php

<?php

// required headers
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 database and object files
include_once '../config/Db.php';
include_once '../object/Department.php';

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

// initialize object
$department = new Department($db);

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

// set department property value
$department->name = $data->name;

// create the department
if ($department->create()) {
    echo '{';
    echo '"message": "Department was created."';
    echo '}';
}

// if unable to create the department, tell the user
else {
    echo '{';
    echo '"message": "Unable to create department."';
    echo '}';
}

Notice in the above file while we are creating a new department we are not initializing department id to the department object because id will be created automatically in the database.

Step 7. Add below create() method to Department.php

// create department
    function create() {
        // query to insert record
        $query = "INSERT INTO
                " . $this->table_name . "
            SET
                dept_name=:name";
        // prepare query
        $stmt = $this->conn->prepare($query);
        // sanitize
        $this->name = htmlspecialchars(strip_tags($this->name));

        // bind values
        $stmt->bindParam(":name", $this->name);

        // execute query
        if ($stmt->execute()) {
            return true;
        } else {
            return false;
        }
    }

When you hit the URL POST http://localhost/<project root directory>/create.php in REST client using below body param, you see below output in the browser

Body

{
       "name": "FINANCIAL"
}

Output

message    "Department was created."

Please check the table to ensure that department has been created for the given department name.

Step 8. Now we will update department to the database. So create below file under <project root directory>/department/update.php

<?php

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

// include database and object files
include_once '../config/Db.php';
include_once '../object/Department.php';

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

// initialize object
$department = new Department($db);

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

// set ID property of department to be updated
$department->id = $data->id;
// set department property value
$department->name = $data->name;

// update the department
if ($department->update()) {
    echo '{';
    echo '"message": "Department was updated."';
    echo '}';
}

// if unable to update the department, tell the user
else {
    echo '{';
    echo '"message": "Unable to update department."';
    echo '}';
}

Step 9. Add below update() method to Department.php

// update the department
    function update() {
        // update query
        $query = "UPDATE
                " . $this->table_name . "
            SET
                dept_name = :name
            WHERE
                dept_id = :id";

        // prepare query statement
        $stmt = $this->conn->prepare($query);

        // sanitize
        $this->name = htmlspecialchars(strip_tags($this->name));
        $this->id = htmlspecialchars(strip_tags($this->id));

        // bind new values
        $stmt->bindParam(':name', $this->name);
        $stmt->bindParam(':id', $this->id);

        // execute the query
        if ($stmt->execute()) {
            return true;
        } else {
            return false;
        }
    }

When you hit the URL PUT http://localhost/<project root directory>/update.php in REST client using the below body parame, you see below output in the browser

Body

{
	"id": 45,
	"name": "FINANCE"
}

Output

message    "Department was updated."

Please check the table to ensure that department has been updated for the given department id and name.

Step 10. Now we will update department to the database. So create below file under <project root directory>/department/delete.php

<?php

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


// include database and object files
include_once '../config/Db.php';
include_once '../object/Department.php';

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

// initialize object
$department = new Department($db);

// set ID property of department to be deleted
$department->id = filter_input(INPUT_GET, 'id');

// delete the department
if ($department->delete()) {
    echo '{';
    echo '"message": "Department was deleted."';
    echo '}';
}

// if unable to delete the department
else {
    echo '{';
    echo '"message": "Unable to delete department."';
    echo '}';
}
?>

Step 11. Add below delete() method to Department.php

// delete the department
    function delete() {
        // delete query
        $query = "DELETE FROM " . $this->table_name . " WHERE dept_id = ?";

        // prepare query
        $stmt = $this->conn->prepare($query);

        // sanitize
        $this->id = htmlspecialchars(strip_tags($this->id));

        // bind id of record to delete
        $stmt->bindParam(1, $this->id);

        // execute query
        if ($stmt->execute()) {
            return true;
        }

        return false;
    }

When you hit the URL DELETE http://localhost/<project root directory>/delete.php?id=45 in REST client you see below output in the browser

message    "Department was deleted."

Please check the table to ensure that department has been deleted for the given department id.

You may like to read Codeigniter REST + ReactJS CRUD Example

Thanks for reading