How to Create CRUD API in PHP - onlyxcodes

Tuesday 12 May 2020

How to Create CRUD API in PHP

Hi, friends, I 'm coming with a simple and useful RESTful API tutorial named PHP CRUD API after a long time here. I already have a PHP REST API tutorial for beginners and in this tutorial, I detailed explore the fundamentals of REST and API, how HTTP methods help to perform the targeted 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.


php crud api tutorial -onlyxcodes

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 will be the PHP-CRUD-API project file structure. This project establishes the location of the folder inside xampp / htdocs


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, we 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.


MethodEndURL
GEThttp://localhost/php-rest-api-crud

fetch all product records from database - php crud api tutorial

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.


MethodEndURL
POSThttp://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
}

create new product record from postman tool - php crud api tutorial

See the new product created on the table.


new product record 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.


MethodEndURL
PUThttp://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
}

Update Particular Existing Product Record from Postman Tool | PHP CRUD API Tutorial

See id 3 number record updated on the table.


Particular Product 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.


MethodEndURL
DELETEhttp://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
}

delete particular product record from postman tool

See product with 3 Id number deleted on the table.


particular product record 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.


MethodEndURL
POSThttp://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.


search product name from postman tool

Download Codes

4 comments:

  1. Hi,
    I get an error from the index.


    Warning: Use of undefined constant MYSQL_ASSOC - assumed 'MYSQL_ASSOC' (this will throw an Error in a future version of PHP) in D:\xampp\htdocs\php-rest-api-crud\index.php on line 16



    Warning: mysqli_fetch_all() expects parameter 2 to be int, string given in D:\xampp\htdocs\php-rest-api-crud\index.php on line 16

    null

    What can I do?
    greets from germany

    ReplyDelete
    Replies
    1. Change MYSQL_ASSOC to MYSQLI_ASSOC, which means that the array is extracted by mysqli instead of mysql.

      Delete
  2. Al utilizar el método PUT me dice lo siguiente: Could not get response
    Error: socket hang up

    ReplyDelete
    Replies
    1. The application work well please follow instructions

      Delete

Post Bottom Ad