PHP 7 tutorial with MySQL CRUD REST API

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.

mysql -u root -p

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.

MethodEndURLGEThttp://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. 

mysql -u root -p

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.

MethodEndURLPOSThttp://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.

How to make simple CRUD REST API in PHP with MySQL?

How to use PHP CRUD API?

How to fetch data from database in PHP using REST API?

How to create an API with PHP and MySQL?