REST API CRUD Example in PHP PDO MySQL

REST APIs

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

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

HTTP 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.

Prerequisites

PHP 7.4.27/8.2.7, MySQL 8.0.31, Apache HTTP Server (Optional)

Project Directory

It’s assumed that you have setup PHP in Windows system.

Now I will create a project root directory called php-pdo-mysql-rest-api-crud anywhere in the system.

I may not mention the project root directory in subsequent sections and I will assume that I am talking with respect to the project root directory.

MySQL Table And Data

Create below MySQL table department under roytuts database with the following structure.

CREATE TABLE `department` (
  `dept_id` INT unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `dept_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I am also dumping some data to the table for testing the application.

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

Database Configuration

Create below Db class under directory config in the file name 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.

Department Object Class

Create below Department class in the file object/Department.php. This class will represent the object for the corresponding department table.

<?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;
    }

    ...
}

Fetch Department Data

I will read or fetch all departments from the database table. So create a file department/read.php with the following source code.

<?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 record found for department.")
    );
}

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

Add implementation for read() method to department.php file.

// 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/php-pdo-mysql-rest-api-crud/department/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"
	}
]

Create New Department

Next I will create a new department to the database. So create a file department/create.php with the following source code:

<?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 I am creating a new department I am not initializing department id to the department object because id will be created automatically in the database.

Add implementation for 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/php-pdo-mysql-rest-api-crud/department/create.php in REST client using below body param, you see the following output:

Body

{
       "name": "FINANCIAL"
}

Output

message    "Department was created."

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

Update Department Information

Now I will update department to the database. So create below source code in the file 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 '}';
}

Add implementation for 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/php-pdo-mysql-rest-api-crud/department/update.php in REST client using the below body param, you see below output in the response:

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.

Delete Department Information

I will update department to the database table. So create source code as given below in the file 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 '}';
}

Add implementation for delete() method to department.php file.

// 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/php-pdo-mysql-rest-api-crud/department/delete.php?id=45 in REST client you see below output in the response:

message    "Department was deleted."

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

PHP REST Client

Now what if you do not have REST client tool, such as, Postman or Talend then you can write your own REST client to test the REST APIs.

Create a rest-client.php file with following source code that will handle different HTTP methods and return the result from the REST APIs.

<?php

function perform_http_request($method, $url, $data = false) {
    $curl = curl_init();

    switch ($method) {
        case "POST":
            curl_setopt($curl, CURLOPT_POST, 1);

            if ($data) {
                curl_setopt($curl, CURLOPT_POSTFIELDS, $data);
			}
			
            break;
        case "PUT":
			curl_setopt($curl, CURLOPT_CUSTOMREQUEST, "PUT");
			
			if ($data) {
                curl_setopt($curl, CURLOPT_POSTFIELDS, $data);
			}
			
            break;
        default:
            if ($data) {
                $url = sprintf("%s?%s", $url, http_build_query($data));
			}
    }

    curl_setopt($curl, CURLOPT_URL, $url);
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false); //If SSL Certificate Not Available, for example, I am calling from http://localhost URL

    $result = curl_exec($curl);
	
	if(!$result){
        $result = (json_encode(array(array("error" => curl_error($curl), "code" => curl_errno($curl)))));
    }

    curl_close($curl);

    return $result;
}

The following PHP file (crud-operations.php) will test the above REST APIs for CRUD operations:

<?php

require_once 'rest-client.php';

$rest_api_base_url = 'http://localhost';
		
echo "\n" . 'READ' . "\n";
echo '---------------------' . "\n";

//GET - list of departments
$get_endpoint = '/department/read.php';

$response = perform_http_request('GET', $rest_api_base_url . $get_endpoint);

echo 'List Of Departments:' . "\n";
echo $response . "\n";

//GET - single department - it has not been implemented in this example
/*$get_endpoint = '/api/users/2';

$response = perform_http_request('GET', $rest_api_base_url . $get_endpoint);

echo 'Single Department' . "\n";
echo $response . "\n";*/

echo "\n" . 'CREATE' . "\n";
echo '---------------------' . "\n";

//POST - create new department
$post_endpoint = '/department/create.php';

$request_data = json_encode(array("name" => "FINNCIAL"));

$response = perform_http_request('POST', $rest_api_base_url . $post_endpoint, $request_data);

echo $response . "\n";

//fetch all departments
$response = perform_http_request('GET', $rest_api_base_url . $get_endpoint);

echo 'List Of Departments:' . "\n";
echo $response . "\n";

echo "\n" . 'UPDATE' . "\n";
echo '---------------------' . "\n";

//PUT - update department
$put_endpoint = '/department/update.php';

$request_data = json_encode(array("id" => 41, "name" => "FINANCIAL"));

$response = perform_http_request('PUT', $rest_api_base_url . $put_endpoint, $request_data);

echo $response . "\n";

//fetch all departments
$response = perform_http_request('GET', $rest_api_base_url . $get_endpoint);

echo 'List Of Departments:' . "\n";
echo $response . "\n";

echo "\n" . 'DELETE' . "\n";
echo '---------------------' . "\n";

//DELETE - delete department
$delete_endpoint = '/department/delete.php?id=41';

$response = perform_http_request('DELETE', $rest_api_base_url . $delete_endpoint);

echo $response . "\n";

//fetch all departments
$response = perform_http_request('GET', $rest_api_base_url . $get_endpoint);

echo 'List Of Departments:' . "\n";
echo $response . "\n";

Running the REST Server Side

I am not using any external server so I will execute the following command on project’s root directory from command line tool to run the app:

> php -S localhost:80

The server side will be up at localhost and at port 80.

To run the client side PHP file just execute the following command on the command line tool:

> php crud-operations.php

The above file will give you the following output in the command line tool:

READ
---------------------
List Of Departments:
{"records":[{"id":10,"name":"ACCOUNTING"},{"id":20,"name":"RESEARCH"},{"id":30,"name":"SALES"},{"id":40,"name":"OPERATIONS"}]}

CREATE
---------------------
{"message": "Department was created."}
List Of Departments:
{"records":[{"id":10,"name":"ACCOUNTING"},{"id":20,"name":"RESEARCH"},{"id":30,"name":"SALES"},{"id":40,"name":"OPERATIONS"},{"id":41,"name":"FINNCIAL"}]}

UPDATE
---------------------
{"message": "Department was updated."}
List Of Departments:
{"records":[{"id":10,"name":"ACCOUNTING"},{"id":20,"name":"RESEARCH"},{"id":30,"name":"SALES"},{"id":40,"name":"OPERATIONS"},{"id":41,"name":"FINANCIAL"}]}

DELETE
---------------------
{"message": "Department was deleted."}
List Of Departments:
{"records":[{"id":10,"name":"ACCOUNTING"},{"id":20,"name":"RESEARCH"},{"id":30,"name":"SALES"},{"id":40,"name":"OPERATIONS"}]}

Hope you got an idea how to create REST APIs in PHP and how to create client app for REST APIs in PHP.

Source Code

Download

Roy Tutorials