PHP Rest API CRUD Example

Hi, friends, In this tutorial I will show you the PHP Rest API CRUD Example with MySQL database. I have previously written a PHP REST API tutorial for beginners in which I went over the basics of REST and API as well as how HTTP methods work to carry out the desired REST API or EndURL operation.

Now in this tutorial, I will explain how to create RESTful API without any framework in core PHP and MySQL and apply CRUD Operations on end URL through HTTP methods.

Inside this tutorial, I will explain all codes summary and provide step by step practical visualization demo how REST API work to insert, update, delete and search product dynamically from the database table via Postman tool. So Let’s dive into tons tutorial codes that are very easy.

Table Content

1. Project File Structure

2. Database And Table Configuration

3. dbconfig.php

4. index.php

5. .htaccess

6. api-create.php

7. api-update.php

8. api-delete.php

9. api-search.php

Project File Structure

This is the file structure for my project, which I set up within the xmpp/htdoc folder.

xampp/
├── htdocs/
│   ├── php-rest-api-crud/
        └── .htaccess
        └──  api-create.php
        └──  api-delete.php
        └──  api-search.php
        └──  api-update.php
        └──  dbconfig.php
        └──  index.php

Database And Table Configuration

We need to build a database in your PhpMyAdmin before we get a tutorial. Here I use a database called "php_crud_api_db" but you can use any name whatever depends on you. 

And you can do this by using the terminal or command-line tool to enter the MySQL root user to make a Database.

To create the database, we enter the root user through a command-line.

mysql -u root -p

Create a new MySQL database.

CREATE DATABASE php_crud_api_db;

Search the database created or not.

SHOW DATABASES;

+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| MySQL                 |
| performance_schema    |
| php_crud_api_db       |
| sys                   |
+-----------------------+

And make use of the database.

use php_crud_api_db;

After you have completed the design of the database, then you can run the following SQL script to successfully create the product table.

CREATE TABLE IF NOT EXISTS `tbl_product` (
  `product_id` int(11) NOT NULL,
  `product_name` varchar(20) NOT NULL,
  `product_price` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;

Make sure you have properly created the table of tbl_product.

SHOW tables;

+---------------------------+
| Tables_in_php_crud_api_db |
+---------------------------+
| tbl_product               |
+---------------------------+

Now in this table, I have inserted two dumping data by following SQL codes.

INSERT INTO `tbl_product` (`product_id`, `product_name`, `product_price`) VALUES
(1, 'shirts', 400),
(2, 'watches', 300);

Once the database and table have been created, we need to create the below files:

• dbconfig.php
• index.php
• .htaccess
• api-create.php
• api-update.php
• api-delete.php
• api-search.php

dbconfig.php

We have a simple database connection code in this file using the function mysqli_connect().

<?php

$DBhost = "localhost";
$DBuser = "root";
$DBpassword ="";
$DBname="php_crud_api_db";

$conn = mysqli_connect($DBhost, $DBuser, $DBpassword, $DBname); 

if(!$conn){
 die("Connection failed: " . mysqli_connect_error());
}

?> 

index.php

The following code will retrieve (read) all the records in this file from the MySQL database. Make index.php file, and put the codes below.

<?php

header("Content-Type: application/json");
header("Acess-Control-Allow-Origin: *");

require_once "dbconfig.php";

$query = "SELECT * FROM tbl_product";

$result = mysqli_query($conn, $query) or die("Select Query Failed.");

$count = mysqli_num_rows($result);

if($count > 0)
{ 
 $row = mysqli_fetch_all($result, MYSQL_ASSOC);
 
 echo json_encode($row);
}
else
{ 
 echo json_encode(array("message" => "No Product Found.", "status" => false));
}

?>

.htaccess

Using this file codes to remove index.php extension on URL. Please file must be saved with .htaccess extension.

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php/$1 [L] 
</IfModule>

Here I check all PHP REST API on the Postman tool.

Open the Postman tool and use the URL below, select the GET method, and press the Send button to view the output.

Method	EndURL
GET	http://localhost/php-rest-api-crud

api-create.php

In this file, we will insert (create) new product into MySQL tbl_product table by following codes. 

<?php

header("Content-Type: application/json");
header("Acess-Control-Allow-Origin: *");
header("Acess-Control-Allow-Methods: POST");
header("Acess-Control-Allow-Headers: Acess-Control-Allow-Headers,Content-Type, 
Acess-Control-Allow-Methods, Authorization");

$data = json_decode(file_get_contents("php://input"), true);

$pname = $data["name"];
$pprice = $data["price"];

require_once "dbconfig.php";

$query = "INSERT INTO tbl_product(product_name, product_price) 
                       VALUES ('".$pname."', '".$pprice."')";

if(mysqli_query($conn, $query) or die("Insert Query Failed"))
{
 echo json_encode(array("message" => "Product Inserted Successfully", "status" => true)); 
}
else
{
 echo json_encode(array("message" => "Failed Product Not Inserted ", "status" => false)); 
}

?>

Codes Explanation:

See above code four headers have been added which are:

header("Content-Type: application/json"); – That tells request is a JSON format.

header("Acess-Control-Allow-Origin: *"); – This specifies that the request is approved from anyplace.

header("Acess-Control-Allow-Methods: POST"); – Is says only POST requests are allowed.

header("Acess-Control-Allow-Headers: Acess-Control-Allow-Headers, Content-Type, Acess-Control-Allow-Methods, Authorization"); – This header uses the security purpose because other people want to use their header to access the page. 

See in this header we have only allowed them to use the above three headers. And additionally, include Authorization property in this header because users can easily access authentication.

$data = json_decode(file_get_contents("php://input"), true);

php://input: – is a read-only stream that allows you to read raw data from the request body. ( Source php.net )

file_get_contents() – is the preferred way to read the contents of a file into a string. It will use memory mapping techniques if supported by your OS to enhance performance. ( Source php.net )

json_decode() – That function gets a JSON string and transfers this to a variable PHP which can be an array or an object.

["name"] and ["price"] are the array used to create new data or value in JSON code. Those values for the array communicate from table fields.

Let's check the codes in the postman tool above and create a new product in a table by following URLs.

Method	EndURL
POST	http://localhost/php-rest-api-crud/api-create.php

Selecting the POST method, selecting the raw radio button in the Body tab, setting Content-Type=application/json in the Headers tab, and pasting the following JSON code.

Note: I don't take Id in JSON code because I've already assigned auto-increment Id strategy to the product_id field and as a primary key to that field.

{
    "name" : "t-shirts",
    "price" : 800
}

See the new product created on the table.

api-update.php

This file explains how the specific product data from the MySQL database table will be updated or modified. Wrap all codes below, and paste them into this file.

We have also inserted four headers in the file below as we inserted file operation for api-create.php. But just one variation is that we applied the HTTP PUT method here to update product requests.

See this header("Acess-Control-Allow-Methods: PUT");

["id"], ["name"] and ["price"] are the array used to update particular product data or value in JSON codes. And every value in the array communicates from the table value.

<?php

header("Content-Type: application/json");
header("Acess-Control-Allow-Origin: *");
header("Acess-Control-Allow-Methods: PUT");
header("Acess-Control-Allow-Headers: Acess-Control-Allow-Headers,Content-Type, 
Acess-Control-Allow-Methods, Authorization");

$data = json_decode(file_get_contents("php://input"), true);

$pid = $data["id"];
$pname = $data["name"];
$pprice = $data["price"];

require_once "dbconfig.php";

echo $query = "UPDATE tbl_product SET product_name= '".$pname."' , 
                                 product_price= '".$pprice."' 
                           WHERE product_id='".$pid."' ";

if(mysqli_query($conn, $query) or die("Update Query Failed"))
{ 
 echo json_encode(array("message" => "Product Update Successfully", "status" => true)); 
}
else
{ 
 echo json_encode(array("message" => "Failed Product Not Updated", "status" => false)); 
}

?>

We update the existing product record to use the below URL.

Method	EndURL
PUT	http://localhost/php-rest-api-crud/api-update.php

Select the PUT method, select the raw radio button in the Body tab, set Content-Type=”application/json” in the Headers tab, and then paste the following JSON code.

Note: I take Id 3 here because I want to update a record of 3 I d number from the table.

{
    "id" : 3,
    "name" : "checks shirts",
    "price" : 1000
}

See id 3 number record updated on the table.

api-delete.php

These file codes remove or delete specific product data from the database table.

Again we have the same four headers in this file, but the only difference is that we have implemented the HTTP DELETE method to delete product requests.

The ["id"] is an array used to delete specific product data from the table in the JSON code. This value of the array communicates with specific table ids.

<?php

header("Content-Type: application/json");
header("Acess-Control-Allow-Origin: *");
header("Acess-Control-Allow-Methods: DELETE");
header("Acess-Control-Allow-Headers: Acess-Control-Allow-Headers,Content-Type, 
Acess-Control-Allow-Methods, Authorization");

$data = json_decode(file_get_contents("php://input"), true);

$pid = $data["id"];

require_once "dbconfig.php";

echo $query = "DELETE FROM tbl_product WHERE product_id='".$pid."' ";

if(mysqli_query($conn, $query) or die("Delete Query Failed"))
{ 
 echo json_encode(array("message" => "Product Delete Successfully", "status" => true)); 
}
else
{ 
 echo json_encode(array("message" => "Failed Product Not Deleted", "status" => false)); 
}

?>

Use this URL below to delete specific product information from the database.

Method	EndURL
DELETE	http://localhost/php-rest-api-crud/api-delete.php

select the DELETE method, select the raw radio button in the Body tab, set Content-Type=”application/json” in the Headers tab, and then paste the following JSON code, and click the send button.

Note: I take 3 Id number in JSON code because I want to delete 3 Id number record of the product from the table.

{
    "id" : 3
}

See product with 3 Id number deleted on the table.

api-search.php

A simple search for the product from the database in this file. To search a product name from the database, we fire select query with WHERE clause and use LIKE operator.

The percentage (%) of wildcards help you search for multiple names of the product.

In this file, we include two required headers that I have discussed in the explanation of creating codes in a new product.

The ["search"] is an array that used to simply search product names in JSON code. This value for the array engages from the field of a table product_name.

<?php

header("Content-Type: application/json");
header("Acess-Control-Allow-Origin: *");


$data = json_decode(file_get_contents("php://input"), true);

$psearch = $data["search"];

require_once "dbconfig.php";

echo $query = "SELECT * FROM tbl_product WHERE product_name LIKE '%".$psearch."%' ";

$result = mysqli_query($conn, $query) or die("Search Query Failed.");

$count = mysqli_num_rows($result);

if($count > 0)
{ 
 $row = mysqli_fetch_all($result, MYSQL_ASSOC);
 
 echo json_encode($row);
}
else
{ 
 echo json_encode(array("message" => "No Search Found.", "status" => false));
}

?>

We search for a product name to use below URL.

Method	EndURL
POST	http://localhost/php-rest-api-crud/api-search.php

Selecting the POST method, selecting the raw radio button in the Body tab, setting Content-Type=application/json in the Headers tab, and pasting the following JSON code.

{
     "search" : "shirts"
}

I 'd like to check for shirts and see the result display below.

Download Codes