In this article, we will be creating a Web API application. I will explain step by step tutorials from scratch and perform CRUD operations in PHP / MySql.

Web Server
First of all, you need a web server. If you are using a windows machine, you will download and install Wamp server. If you have Mac machine, you will download and install LAMP server. If you have a Linux machine, you will install MySql.

Download Wamp Server

Prepare the Database
Next, start your local web server on your machine. open a web browser and go to http://localhost/phpmyadmin/ then you will reach PhpMyAdmin console

You might log in to the PhpMyAdmin console using the credential, you have chosen during installation.

After successful login, you will be redirected to the homepage. On your PhpMyAdmin, create a database named “ng7_crud”.

Next, run the following SQL code to create our “products” table.

--
-- Table structure for table `products`
--
CREATE TABLE IF NOT EXISTS `products` (
`p_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`p_name` varchar(128) NOT NULL,
`p_description` text NOT NULL,
`p_price` double NOT NULL,
`p_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`p_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Again, run the following SQL code on your PhpMyAdmin to insert the sample data or record on our ‘products’ database table.

INSERT INTO `products` (`p_id`, `p_name`, `p_description`, `p_price`) VALUES
(1, 'Basketball', 'A ball used in the NBA.', 49.99),
(3, 'Gatorade', 'This is a very good drink for athletes.', 1.99),
(4, 'Eye Glasses', 'It will make you read better.', 6),
(5, 'Trash Can', 'It will help you maintain cleanliness.', 3.95),
(6, 'Mouse', 'Very useful if you love your computer.', 11.35),
(7, 'Earphone', 'You need this one if you love music.', 7),
(8, 'Pillow', 'Sleeping well is important.', 8.99);

Create a ‘web_api’ directory inside of www directory into the wamp server installation directory. Inside of ‘web_api’ folder, create ‘config’ folder and open it. Next, create the database.php file inside of ‘config’ folder.

open database.php file and write the following code.

<?php
// used to connect to the database
$host = "localhost";
$db_name = "ng7_crud";
$username = "root";
$password = "";

try {
$con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}

// show error
catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
?>

this file create a connection with MySql database.

create a new record
Next, create a file with the name ‘create.php’ inside of the ‘web_api’ folder. Open that file and write the following code.

<?php
if($_POST){

// include database connection
include 'config/database.php';

try{

// insert query
$query = "INSERT INTO products SET p_name=:name, p_description=:description, p_price=:price";
// prepare query for execution
$stmt = $con->prepare($query);
// posted values
$name = $_POST['name'];
$description = $_POST['description'];
$price = $_POST['price'];
// bind the parameters
$stmt->bindParam(':name', $name);
$stmt->bindParam(':description', $description);
$stmt->bindParam(':price', $price);
// Execute the query
if
($stmt->execute()){
echo json_encode(array('result'=>'success'));
}else{
echo json_encode(array('result'=>'fail'));
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
?>

View Records
Create a file with the name ‘view.php’ inside of the ‘web_api’ folder. Open that file and write the following code.

<?php
// include database connection
include 'config/database.php';

// delete message prompt will be here

// select all data
$query = "SELECT p_id, p_name, p_description, p_price FROM products ORDER BY p_id DESC";
$stmt = $con->prepare($query);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo $json;
?>

View One Record

Create a file with the name ‘view_one.php’ inside of the ‘web_api’ folder. Open that file and write the following code.

<?php
// get passed parameter value, in this case, the record ID
// isset() is a PHP function used to verify if a value is there or not
$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');

//include database connection
include 'config/database.php';

// read current record's data
try {
// prepare select query
$query = "SELECT p_id, p_name, p_description, p_price FROM products WHERE p_id = ? LIMIT 0,1";
$stmt = $con->prepare( $query );

// this is the first question mark
$stmt->bindParam(1, $id);

// execute our query
$stmt->execute();

// store retrieved row to a variable
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$json = json_encode($row);
echo $json;
}

// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
?>

Update Record

Create a file with the name ‘update.php’ inside of the ‘web_api’ folder. Open that file and write the following code.

<?php

// check if form was submitted
if($_POST){
include 'config/database.php';
try{
// write update query
// in this case, it seemed like we have so many fields to pass and
// it is better to label them and not use question marks
$query = "UPDATE products
SET p_name=:name, p_description=:description, p_price=:price
WHERE p_id = :id";

// prepare query for excecution
$stmt = $con->prepare($query);

// posted values
$id = $_POST['id'];
$name = $_POST['name'];
$description = $_POST['description'];
$price = $_POST['price'];

// bind the parameters
$stmt->bindParam(':name', $name);
$stmt->bindParam(':description', $description);
$stmt->bindParam(':price', $price);
$stmt->bindParam(':id', $id);

// Execute the query
if
($stmt->execute()){
echo json_encode(array('result'=>'success'));
}else{
echo json_encode(array('result'=>'fail'));
}

}

// show errors
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
?>

Delete Record

Create a file with the name ‘delete.php’ inside of the ‘web_api’ folder. Open that file and write the following code.

<?php
// include database connection
include 'config/database.php';

try {

// get record ID
// isset() is a PHP function used to verify if a value is there or not
$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');

// delete query
$query = "DELETE FROM products WHERE p_id = ?";
$stmt = $con->prepare($query);
$stmt->bindParam(1, $id);

if($stmt->execute()){
// redirect to read records page and
// tell the user record was deleted
echo json_encode(array('result'=>'success'));
}else{
echo json_encode(array('result'=>'fail'));
}
}

// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
?>

finally your ‘web_api’ directory like this.

Next, test the api using post man.

view.php

create.php

view_one.php

update.php

delete.php

all set, our web API is ready now.