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
Nice article, got this to work quite easily. Thanks