In this tutorial I'm going to show how to create a REST API CRUD Crate read update and delete using PHP Class and PDO Mysql
HTTP methods
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.
Crate Database Table
CREATE TABLE `tbluser` (
`id` int(11) NOT NULL,
`name` varchar(150) NOT NULL,
`username` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `tbluser` (`id`, `name`, `username`) VALUES
(1, 'Cairocoders Ednalan', 'cairocoders'),
(2, 'tutorial101', 'clded25');
ALTER TABLE `tbluser`
ADD PRIMARY KEY (`id`);
ALTER TABLE `tbluser`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
Testing Rest API
Install the Advanced Rest Client
1. Go to Google Chrome's Web Store
2. Search for "Advanced Rest Client" https://chrome.google.com/webstore/detail/advanced-rest-client/hgmloofddffdnphfgcellkdfbfbjeloo and Install the extension
//DB.php
<?php
class Db {
private $host = "localhost";
private $db_name = "testingdb";
private $username = "root";
private $password = "";
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;
}
}
?>
User.php
//User.php
<?php
class User {
// database connection and table name
private $conn;
private $table_name = "tbluser";
// object properties
public $id;
public $name;
public $username;
// constructor with $db as database connection
public function __construct($db) {
$this->conn = $db;
}
// read user
function read() {
// query to select all
$query = "SELECT *
FROM
" . $this->table_name . "
ORDER BY
id";
// prepare query statement
$stmt = $this->conn->prepare($query);
// execute query
$stmt->execute();
return $stmt;
}
// create user
function create() {
// query to insert record
$query = "INSERT INTO
" . $this->table_name . "
SET
name=:name, username=:username";
// prepare query
$stmt = $this->conn->prepare($query);
// sanitize
$this->name = htmlspecialchars(strip_tags($this->name));
$this->username = htmlspecialchars(strip_tags($this->username));
// bind values
$stmt->bindParam(":name", $this->name);
$stmt->bindParam(":username", $this->username);
// execute query
if ($stmt->execute()) {
return true;
} else {
return false;
}
}
// update the user
function update() {
// update query
$query = "UPDATE
" . $this->table_name . "
SET
name = :name, username = :username
WHERE
id = :id";
// prepare query statement
$stmt = $this->conn->prepare($query);
// sanitize
$this->name = htmlspecialchars(strip_tags($this->name));
$this->username = htmlspecialchars(strip_tags($this->username));
$this->id = htmlspecialchars(strip_tags($this->id));
// bind new values
$stmt->bindParam(':username', $this->username);
$stmt->bindParam(':name', $this->name);
$stmt->bindParam(':id', $this->id);
// execute the query
if ($stmt->execute()) {
return true;
} else {
return false;
}
}
// delete the user
function delete() {
// delete query
$query = "DELETE FROM " . $this->table_name . " WHERE 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;
}
}
list.php
//list.php
<?php
// required headers
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
//database and object files
include_once 'Db.php';
include_once 'User.php';
// instantiate database and user object
$database = new Db();
$db = $database->getConnection();
// initialize object
$user = new user($db);
// query user
$stmt = $user->read();
$num = $stmt->rowCount();
// check if more than 0 record found
if ($num > 0) {
// user array
$user_arr = array();
$user_arr["records"] = array();
// retrieve table contents
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// extract row
extract($row);
$user_item = array(
"id" => $row['id'],
"name" => $row['name'],
"username" => $row['username']
);
array_push($user_arr["records"], $user_item);
}
echo json_encode($user_arr);
} else {
echo json_encode(
array("message" => "No products found.")
);
}
?>
create.php
//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");
// database and object files
include_once 'Db.php';
include_once 'User.php';
$database = new Db();
$db = $database->getConnection();
// initialize object
$user = new User($db);
// get posted data
$data = json_decode(file_get_contents("php://input", true));
// set user property value
$user->name = $data->name;
$user->username = $data->username;
// create the user
if ($user->create()) {
echo '{';
echo '"message": "user was created."';
echo '}';
}
// if unable to create the user, tell the user
else {
echo '{';
echo '"message": "Unable to create user."';
echo '}';
}
?>
update.php
//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");
// database and object files
include_once 'Db.php';
include_once 'User.php';
$database = new Db();
$db = $database->getConnection();
// initialize object
$user = new User($db);
// get posted data
$data = json_decode(file_get_contents("php://input", true));
// set ID property of user to be updated
$user->id = $data->id;
// set user property value
$user->name = $data->name;
$user->username = $data->username;
// update the user
if ($user->update()) {
echo '{';
echo '"message": "user was updated."';
echo '}';
}
// if unable to update the user, tell the user
else {
echo '{';
echo '"message": "Unable to update user."';
echo '}';
}
?>
delete.php
//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");
// database and object files
include_once 'Db.php';
include_once 'User.php';
$database = new Db();
$db = $database->getConnection();
// initialize object
$user = new User($db);
// set ID property of user to be deleted
$user->id = filter_input(INPUT_GET, 'id');
// delete the user
if ($user->delete()) {
echo '{';
echo '"message": "user was deleted."';
echo '}';
}
// if unable to delete the user
else {
echo '{';
echo '"message": "Unable to delete user."';
echo '}';
}
?>
