In this tutorial, You will learn to create the CRUD operation rest API using PHP & MySQL which will be very useful to integrate with frontend languages such as react js, angular js & more.
Step to Create Crud Restful API in PHP & MYSQL
Now, Let’s start coding with the given some simple steps –
Create a Folder Structure
First of all, Create the following folder structure in your system.
- database.php – This file is created for the database connection
- api.php – This file is created for the API requests such as post, put, get & delete.
- CrudApi.php – This file is created for writing code with a custom class for crud operation
crud-opration/
|_rest/
| |__database.php
| |__CrudApi.ph[
|__api.phpCreate Database & Table
Now, Create a database & Table in the PhpMyAdmin the the following given name –
Database Name – restapi
CREATE DATABASE restapi
Table Name – developers
CREATE TABLE `developers` ( `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT, `firstName` varchar(255) DEFAULT NULL, `lastName` varchar(255) DEFAULT NULL, `gender` varchar(10) DEFAULT NULL, `salary` decimal(10,2) DEFAULT NULL, )
Connect to Database
No, Connect your PHP to the MySQL database with this code.
File Name – database.php
<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "phprestapi";
$conn = new mysqli($host, $user, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>Create CRUD API Class
File Name – CrudApi.php
<?php
class CrudApi {
private $conn;
private $categoryTable;
public function __construct($conn) {
$this->conn = $conn;
$this->tableName = 'crud';
}
public function validate($firstName, $lastName, $gender, $salary) {
$error = false;
$errMsg = null;
if(empty($firstName)) {
$errMsg = "FirstName is empty";
$error = true;
}
if(empty($lastName)) {
$errMsg = "LastName is empty";
$error = true;
}
if(empty($gender)) {
$errMsg = "gender is empty";
$error = true;
}
if(empty($salary)) {
$errMsg = "salary is empty";
$error = true;
}
$errorInfo = [
"error" => $error,
"errMsg" => $errMsg
];
return $errorInfo;
}
public function create() {
$data = json_decode(file_get_contents("php://input"), true);
$firstName = $data['firstName'];
$lastName = $data['lastName'];
$gender = $data['gender'];
$salary = $data['salary'];
$validate = $this->validate($firstName, $lastName, $gender, $salary);
$success = false;
if (!$validate['error']){
$query = "INSERT INTO ";
$query .= $this->tableName;
$query .= " (firstName, lastName, gender, salary) ";
$query .= " VALUES (?,?,?,?)";
$stmt = $this->conn->prepare($query);
$stmt->bind_param("sssi", $firstName, $lastName, $gender, $salary);
if ($stmt->execute()) {
$status = 200;
$msg = "Data is created successfully";
} else{
$status = 400;
$msg = $this->conn->error;
}
} else {
$status = 401;
$msg = $validate['errMsg'];
}
$data = [
'status' => $status,
'msg' => $msg,
'data' => $data
];
return json_encode($data);
}
public function get() {
$data = [];
$query = "SELECT id, firstName, lastName, gender, salary FROM ";
$query .= $this->tableName;
$query .= " ORDER BY id DESC";
$result = $this->conn->query($query);
if ($result) {
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
$result->free();
}
if(empty($data)) {
$data = [
'status' => 404,
'data' => 'Recod not found'
];
} else {
$data = [
'status' => 200,
'data' => $data
];
}
return json_encode($data);
}
public function getById($id) {
$data = [];
$query = "SELECT id, firstName, lastName, gender, salary FROM ";
$query .= $this->tableName;
$query .= " WHERE id=?";
$stmt = $this->conn->prepare($query);
$stmt->bind_param("i", $id);
if ($stmt->execute()) {
$result = $stmt->get_result();
$rows= $result->fetch_assoc();
if($rows) {
$status = 200;
$data = $rows;
} else {
$status = 404;
$data = "Record not found";
}
}
$data = [
'status' => $status,
'data' => $data
];
return json_encode($data);
}
public function updateById($id) {
$data = json_decode(file_get_contents("php://input"), true);
$firstName = $data['firstName'];
$lastName = $data['lastName'];
$gender = $data['gender'];
$salary = $data['salary'];
$validate = $this->validate($firstName, $lastName, $gender, $salary);
$success = false;
if (!$validate['error']){
$query = "UPDATE ";
$query .= $this->tableName;
$query .= " SET firstName = ?, lastName = ?, gender = ?, salary = ? ";
$query .= " WHERE id = ?";
$stmt = $this->conn->prepare($query);
$stmt->bind_param("sssii", $firstName, $lastName, $gender, $salary, $id);
if ($stmt->execute()) {
$status = 200;
$msg = "Data is updated successfully";
} else{
$status = 400;
$msg = $this->conn->error;
}
} else {
$status = 401;
$msg = $validate['errMsg'];
}
$data = [
'status' => $status,
'msg' => $msg,
'data' => $data
];
return json_encode($data);
}
public function deleteById($id) {
$getData = $this->getById($id);
$rows = json_decode($getData, true);
if(404 == $rows['status']){
return $getData;
} else {
$query = "DELETE FROM ";
$query .= $this->tableName;
$query .= " WHERE id = ?";
$stmt = $this->conn->prepare($query);
$stmt->bind_param("i", $id);
echo "dddd";
if ($stmt->execute()) {
$data = [
'status' => 200,
'msg' => 'Record is deleted successfully',
'data' => $rows['data']
];
return json_encode($data);
}
}
}
}
?>
Create CRUD API Action
File Name – api.php
<?php
require_once 'database.php';
require_once 'CrudApi.php';
$crudApi = new CrudApi($conn);
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: POST, PUT, GET, DELETE, OPTIONS");
header("Access-Control-Allow-Headers: Content-Type");
header("Content-Type: application/json");
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
http_response_code(200);
exit();
}
// HTTP method handling
$method = $_SERVER['REQUEST_METHOD'];
switch ($method) {
case 'GET':
// Read data
$id = isset($_GET['id']) ? $_GET['id'] : null;
if($id){
echo $crudApi->getById($id);
} else {
echo $crudApi->get();
}
break;
case 'POST':
echo $crudApi->create();
break;
case 'PUT':
$id = isset($_GET['id']) ? $_GET['id'] : null;
if($id) {
echo $crudApi->updateById($id);
} else {
echo "Request URL not found";
}
break;
case 'DELETE':
// Delete data
$id = isset($_GET['id']) ? $_GET['id'] : null;
if($id) {
echo $crudApi->deleteById($id);
} else {
echo "Request URL not found";
}
break;
default:
header('HTTP/1.0 405 Method Not Allowed');
break;
}
?>
Run CRUD API With Postman
Now, open your Postman, and run the following API request
For creating records –
method – POST
URL
http://localhost/codingstatus/php/rest/crud-operations/api.php
Payload – JSON data
{
"firstName": "Jhon",
"lastName": "Doe",
"gender": "male",
"salary": "45"
}For getting records
method – GET
URL –
http://localhost/codingstatus/php/rest/crud-operations/api.php
For updating recored
method – PUT
URL –
http://localhost/codingstatus/php/rest/crud-operations/api.php?id=46
Payload – JSON Data
{
"firstName": "Amit",
"lastName": "Kumar",
"gender": "male",
"salary": "123"
}For Deleting Record –
Method – DELETE
URL –
http://localhost/codingstatus/php/rest/crud-operations/api.php?id=46