This is article we are learn a step by step PHP & MySQL REST API tutorial, In this article I am going to share with you how to create a PHP CRUD (Create, Read, Update, Delete) application REST ful API with MySQL database.
What is API (Application Programming Interface)?
API means Application Programming Interface, and It is a set of routines, protocols, and tools for creating software applications. An API interface makes communication possible between various software components.
In software development, API is a URL that handles the data for the web application through HTTP Requests GET, POST, UPDATE & DELETE, and manages the CRUD operations.
PHP 8 API Project File Structure
This is the file structure of our PHP 8 REST API project; we created api, class, and config folders that contain the API and MySQL database configuration files.
You can run your PHP project through Xampp, MAMP or WAMP however we are using the command line tool to start the PHP project.
| File Name | Purpose |
|---|---|
| create.php | Insert data in database using API |
| read.php | View/display data from database using API |
| delete.php | Delete data from database using API |
| update.php | Update data from database using API |
| employees.php | Contains the all CRUD method in this file . |
| database.php | Contains the database connection |
Create Database and Table by SQL query
We need to create database and table, so here I created webscodex database and employees products table. employees table holds the records which will be API CRUD action. You can simply create table as following SQL query.
-- -- Table structure for table `employees` -- CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8, `emp_id` varchar(100) NOT NULL, `full_name` varchar(100) NOT NULL, `job_title` varchar(100) NOT NULL, `department` varchar(100) NOT NULL, `gender` varchar(100) NOT NULL, `age` varchar(100) NOT NULL, `hire_date` date NOT NULL, `annual_salary` varchar(100) NOT NULL, `bonus` varchar(100) NOT NULL, `city` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `employees` -- INSERT INTO `employees` (`id`, `emp_id`, `full_name`, `job_title`, `department`, `gender`, `age`, `hire_date`, `annual_salary`, `bonus`, `city`) VALUES (2, '2550', 'Vinod', 'Designer', 'IT/ software', 'male', '28', '2022-12-02', '25000', '1500', 'delhi'), (3, '2555', 'Manish', 'developer', 'IT/ software ', 'male', '28', '2022-12-01', '22000', '1500', 'delhi'), (4, '2650', 'Manish pushpakar', 'Web Designer', 'IT/ software', 'male', '28', '2022-12-02', '35000', '15000', 'Delhi'), (5, '2550', 'Vinod gupta', 'Designer', 'IT/ software', 'male', '28', '2022-12-02', '25000', '1500', 'delhi'), (6, '1542', 'sunny singh', 'Developer', 'IT', 'Male', '35', '2018-08-15', '860000', '15000', 'haidarabad'), (7, '1543', 'Sikhar Dhavan ', 'Developer', 'IT', 'Male', '35', '2018-08-15', '860000', '15000', 'haidarabad');
Create Database Configuration File and Make Database Connection
In this step, we require to create database configuration file, here we will set database name, username and password. So let’s create config.php file on your root directory and put bellow code:
<?php
class Database
{
private $serverName = "localhost";
private $username = "root";
private $password = "";
private $database = "webscodex";
public $con = null;
public function getConnection(){
try {
$this->con = new mysqli($this->serverName, $this->username, $this->password, $this->database);
if ($this->con->connect_error) {
throw new Exception("Could not connect to database.");
}
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
return $this->con;
}
}
?>Create PHP Class File
Now, we will create the PHP Class by the name of Employee. The PHP Classes are used to execute Object-Oriented Programming in PHP. PHP Classes encapsulate the values of the table for the Database. We will define the values that are stored in the variable form in the SQL table.
The SQL table properties are associated with the Connection class that is added via the constructor class.
Each object class contains the CRUD method that is being operated via the PHP functions to perform the CREATE, READ, UPDATE & DELETE operations for the defined table rows.
Create class/employees.php file and define the CRUD methods inside the Employee class.
employees.php
<?php
class Employees
{
// Connection
private $con;
// Db connection
public function __construct($db)
{
$this->con = $db;
}
// GET ALL
public function fetchAllData($table)
{
$query = "SELECT * FROM $table";
$result = $this->con->query($query);
if ($result) {
if ($result->num_rows > 0) {
$rows = $result->fetch_all(MYSQLI_ASSOC);
$data = [
'status' => 200,
'message' => 'Employee record fetch successfully',
'data' => $rows,
];
header("HTTP/1.0 20 OK");
}else{
$data = [
'status' => 400,
'message' => 'No Employee found',
];
header("HTTP/1.0 400 No Employee found");
}
}else{
$data = [
'status' => 500,
'message' => 'Internal server error',
];
header("HTTP/1.0 500 Internal server error");
}
return json_encode($data);
}
public function selectDataById($table, $id)
{
try {
if (!empty($id)) {
$stmt = "SELECT * FROM $table Where id = '$id'";
$result = $this->con->query($stmt);
if ($result->num_rows > 0) {
$row = $result->fetch_all(MYSQLI_ASSOC);
$data = [
'status' => 200,
'message' => 'Single record fetch successfully',
'data' => $row,
];
header("HTTP/1.0 200 OK");
} else {
$data = [
'status' => 404,
'message' => 'No Employee found',
];
header("HTTP/1.0 404 No Employee found");
}
}else{
$data = [
'status' => 404,
'message' => 'Employee Id is required',
];
header("HTTP/1.0 404 Employee Id is required");
}
return json_encode($data);
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
}
public function insertEmpyoyeeData($table, $post)
{
if (!empty($post)) {
$emp_id = $post['emp_id'];
$full_name = $post['full_name'];
$job_title = $post['job_title'];
$department = $post['department'];
$gender = $post['gender'];
$age = $post['age'];
$hire_date = date('Y-m-d', strtotime($post['hire_date']));
$annual_salary = $post['annual_salary'];
$bonus = $post['bonus'];
$city = $post['city'];
if (!empty($emp_id) && !empty($full_name) && !empty($job_title) && !empty($department) && !empty($gender) && !empty($age) && !empty($hire_date) && !empty($city)) {
$query = "INSERT INTO $table (emp_id, full_name, job_title, department, gender, age, hire_date, annual_salary, bonus, city) VALUES('$emp_id', '$full_name', '$job_title', '$department', '$gender', '$age', '$hire_date', '$annual_salary', '$bonus', '$city')";
$result = $this->con->query($query);
if ($result) {
$data = [
'status' => 200,
'message' => 'Employee created successfully.',
];
header("HTTP/1.0 200 created");
} else {
$data = [
'status' => 500,
'message' => 'Internal server error',
];
header("HTTP/1.0 500 Internal server error");
}
}else{
$data = [
'status' => 422,
'message' => 'All fields are required',
];
header("HTTP/1.0 404 unprocessable entity");
}
}else{
$data = [
'status' => 500,
'message' => 'Something went wrong',
];
header("HTTP/1.0 404 Something went wrong");
}
return json_encode($data);
}
public function deleteEmployeeId($table, $id)
{
try {
if (!empty($id)) {
$query = "DELETE FROM $table WHERE id = '$id' LIMIT 1";
$result = $this->con->query($query);
if ($result) {
$data = [
'status' => 200,
'message' => 'Record deleted successfully',
];
header("HTTP/1.0 200 OK");
} else {
$data = [
'status' => 500,
'message' => 'Internal server error',
];
header("HTTP/1.0 500 Internal server error");
}
} else {
$data = [
'status' => 404,
'message' => 'Employee Id is required',
];
header("HTTP/1.0 404 Not found");
}
return json_encode($data);
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
}
public function updateEmpyoyeeData($table, $post, $getId){
if (!empty($post)) {
if (isset($getId) && !empty($getId)) {
$id = $getId['id'];
$emp_id = $post['emp_id'];
$full_name = $post['full_name'];
$job_title = $post['job_title'];
$department = $post['department'];
$gender = $post['gender'];
$age = $post['age'];
$hire_date = date('Y-m-d', strtotime($post['hire_date']));
$annual_salary = $post['annual_salary'];
$bonus = $post['bonus'];
$city = $post['city'];
$query="UPDATE $table SET emp_id='$emp_id', full_name='$full_name', job_title='$job_title', department='$department',
gender='$gender', age='$age', hire_date='$hire_date', annual_salary='$annual_salary', bonus='$bonus', city='$city' WHERE id='$id'";
$result = $this->con->query($query);
if ($result) {
$data = [
'status' => 200,
'message' => 'Employee updated successfully.',
];
header("HTTP/1.0 200 success");
} else {
$data = [
'status' => 404,
'message' => 'Employee not updated',
];
header("HTTP/1.0 404 Employee not updated");
}
}else{
$data = [
'status' => 404,
'message' => 'Employee Id is not found',
];
header("HTTP/1.0 404 Not found");
}
} else {
$data = [
'status' => 404,
'message' => 'Something went wrong',
];
header("HTTP/1.0 404 Something went wrong");
}
return json_encode($data);
}
}
?>The Employees Class manage the REST API CRUD Operations
| Function Name | Purpose |
|---|---|
| __construct( ) | Make the database connections |
| fetchAllData( ) | Get/Fetch all records from database |
| selectDataById( ) | Get/Fetch single record from database |
| insertEmpyoyeeData( ) | Create/Insert data in database |
| deleteEmployeeId( ) | Delete single record from database |
| updateEmpyoyeeData( ) | Update single record. |
Fetch MySQL Records using PHP REST API
The following code retrieves all the records from MySQL table. So create read.php file in the API folder and place the following code.
read.php
<?php
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header("Access-Control-Allow-Methods: GET");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
include_once '../inc/database.php';
include_once '../class/employees.php';
$database = new Database();
$db = $database->getConnection();
$empObj = new Employees($db);
$employeeTable = 'employees';
$requertMethod = $_SERVER['REQUEST_METHOD'];
if ($requertMethod == 'GET') {
if (isset($_GET['id'])) {
$empId = $_GET['id'];
$getEmployeDetails = $empObj->selectDataById($employeeTable, $empId);
} else {
$getEmployeDetails = $empObj->fetchAllData($employeeTable);
}
echo $getEmployeDetails;
}else{
$data = [
'status' => 405,
'message' => $requertMethod. ' Method now allowed',
];
header("HTTP/1.0 405 Method now allowed");
echo json_encode($data);
}
?>Let us test the PHP API using Postman, open the Postman and use the following URL and click on the Send button to check the output.
| METHOD | POSTMAN URL |
| GET | http://localhost/BlogPost/Create-PHP-CRUD-REST-API-with-MySQL/api/read.php |
Insert Single Record in MySQL Table
In this step, we will create PHP REST API endpoints to insert a record to MySQL table. Create a create.php file in the api directory and add the following code.
read.php
<?php
error_reporting(0);
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
include_once '../inc/database.php';
include_once '../class/employees.php';
$database = new Database();
$db = $database->getConnection();
$empObj = new Employees($db);
$employeeTable = 'employees';
$requertMethod = $_SERVER['REQUEST_METHOD'];
if ($requertMethod == 'POST') {
$formData = json_decode(file_get_contents("php://input"), true);
if (empty($formData)) {
$insertEmployeeRecord = $empObj->insertEmpyoyeeData($employeeTable, $_POST);
}else{
$insertEmployeeRecord = $empObj->insertEmpyoyeeData($employeeTable, $formData);
}
echo $insertEmployeeRecord;
} else {
$data = [
'status' => 405,
'message' => $requertMethod . ' Method now allowed',
];
header("HTTP/1.0 405 Method now allowed");
echo json_encode($data);
}
?>| METHOD | URL NAME |
| POST | http://localhost/BlogPost/Create-PHP-CRUD-REST-API-with-MySQL/api/create.php |
Edit/Update MySQL Record using PHP API
In this step we are explain to you how to Update or Edit the data for specific record. We can use the PHP RESTful API to make the necessary update in the data that is stored in the MySQL database.
Create update.php file in the api folder and place the following code.
update.php
<?php
error_reporting(0);
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header("Access-Control-Allow-Methods: PUT");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
include_once '../inc/database.php';
include_once '../class/employees.php';
$database = new Database();
$db = $database->getConnection();
$empObj = new Employees($db);
$employeeTable = 'employees';
$requertMethod = $_SERVER['REQUEST_METHOD'];
if ($requertMethod == 'PUT') {
$formData = json_decode(file_get_contents("php://input"), true);
$updateEmployeeRecord = $empObj->updateEmpyoyeeData($employeeTable, $formData, $_GET);
echo $updateEmployeeRecord;
} else {
$data = [
'status' => 405,
'message' => $requertMethod . ' Method now allowed',
];
header("HTTP/1.0 405 Method now allowed");
echo json_encode($data);
}
?>| METHOD | URL NAME |
| PUT | http://localhost/BlogPost/Create-PHP-CRUD-REST-API-with-MySQL/api/update.php?id=5 |
Remove/Delete Single Mysql Record using PHP API
Create file delete.php in api folder in this file we will write connection to delete or delete an employee record from MySQL data table using PHP 8 RESTful API. We’ll make an API call using the deleteEmployee() method.
delete.php
<?php
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=UTF-8');
header("Access-Control-Allow-Methods: DELETE");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
include_once '../inc/database.php';
include_once '../class/employees.php';
$database = new Database();
$db = $database->getConnection();
$empObj = new Employees($db);
$employeeTable = 'employees';
$requertMethod = $_SERVER['REQUEST_METHOD'];
if ($requertMethod == 'DELETE') {
if (isset($_GET['id'])) {
$empId = $_GET['id'];
$deleteEmployee = $empObj->deleteEmployeeId($employeeTable, $empId);
echo $deleteEmployee;
}
} else {
$data = [
'status' => 405,
'message' => $requertMethod . ' Method now allowed',
];
header("HTTP/1.0 405 Method now allowed");
echo json_encode($data);
}
?>| METHOD | URL NAME |
| PUT | http://localhost/BlogPost/Create-PHP-CRUD-REST-API-with-MySQL/api/update.php?id=5 |
Conclusion
That’s it, in this tutorial we learned how to create a simple CRUD RESTful API with PHP and MySQL.
You can download the entire project file from GitHub.
