RestFul Web Services with PHP MySQLi

Last updated on December 28th, 2019 at 10:44 pm

In this tutorial I will explain to you how we can create API in JSON format using PHP. In my last post, I already explained how we can create REST full web services with MySQL if you did not read that post yet you can read that from this link.  But we all know that MySQL is depreciated and MySQLi is the latest. So in this post, I will explain to you how to create REST API using MySQLi in simple steps.

Prerequisites

Before we start building our web service or REST web service I hope you have good knowledge of PHP and also Object-Oriented Programming (PHP OOP), and also PHPMyadmin.

Build our REST Full web service with PHP and MySQLi

In the below step I will show you how we can perform CURD operation on the database using our web service with help of REST clients like Postman or AdvancedREST Client.

Step 1. Create a new database in from PHPmyadmin and create a table inside your Database using below SQL query.

CREATE TABLE IF NOT EXISTS `user` (
 `id` int(10) NOT NULL,
 `name` text NOT NULL,
 `email` text NOT NULL,
 `status` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

Step 2. Now once we create or DB and table we need to create a new PHP project and create new file conn.php in which we can connect or PHP to our MySQL server.

<?php
$connection = new mysqli("localhost", "root", "", "service") or die(mysqli_error());
?>

Step 3. In this step I will show you how we can insert data in the database, You need to create a new separate file later you can do all CRUD operation in a single file but for this tutorial, I create separate files for all CURD operation.

<?php
include 'conn.php';
$name = $_POST['name'];
$email = $_POST['email'];
$status = $_POST['status'];

$sql = "INSERT INTO `service`.`user` (`id`, `name`, `email`, `status`) VALUES (NULL, '$name', '$email', '$status');";

if ($connection->query($sql)) {
$msg = array("status" =>1 , "msg" => "Your record inserted successfully");
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($connection);
}

$json = $msg;

header('content-type: application/json');
echo json_encode($json);


@mysqli_close($conn);

?>

In the below image, I am using Advance REST client you can use this or postman.

Step 4. Once data save successfully we create the next service to select(select.php) that data from database you can add below code in your file and run that code.

<?php
include 'conn.php';
//Select data from database

$getData = "select * from user";
$qur = $connection->query($getData);

while($r = mysqli_fetch_assoc($qur)){

$msg[] = array("name" => $r['name'], "email" => $r['email'], "status" => $r['status']);
}
$json = $msg;

header('content-type: application/json');
echo json_encode($json);

@mysqli_close($conn);

?>

Now when you save that file and request that data from REST client you get data in JSON format as shown in below code example.

[
    {
        "name": "Jatin",
        "email": "[email protected]",
        "status": "active"
    },
    {
        "name": "Aneh",
        "email": "[email protected]",
        "status": "active"
    },
    {
        "name": "Anil",
        "email": "[email protected]",
        "status": "active"
    },
    ....
]

Step 5. Now create new file to perform delete operation. I named my file delete.php and paste bellow code.

<?php
include 'conn.php';
//Delete record from database

$name = $_POST['name'];
$email = $_POST['email'];
$status = $_POST['status'];

$query = "DELETE FROM `user` WHERE   `name`='$name' && email = '$email'";
if ($connection->query($query)) {
    $msg = array("status" =>1 , "msg" => "Record Deleted successfully");
} else {
    echo "Error: " . $query . "<br>" . mysqli_error($connection);
} 

$json = $msg;

header('content-type: application/json');
echo json_encode($json);

@mysqli_close($conn);

Now once you add delete code run this file from REST client and pass a parameter to delete data from the database.

Step 6. Create new file to perform an update operation. I named my file update.php and paste bellow code to update data and get a response.

update.php

<?php
include 'conn.php';
//Update record in database


$name = $_POST['name'];
$email = $_POST['email'];
$status = $_POST['status'];
$id = $_POST['id'];

$query = "UPDATE `user` SET `name`='$name' ,`email`='$email' WHERE  `id`='$id'";
if ($connection->query($query)) {
       $msg = array("status" =>1 , "msg" => "Record Updated successfully");
}else {
    echo "Error: " . $query . "<br>" . mysqli_error($connention);
}


$json = $msg;

header('content-type: application/json');
echo json_encode($json);

@mysqli_close($conn);

MultiQuery execution in MySQLI

MySQLi has one very good feature of is to perform MultiQuery operation which improves speed and saves no of the line of code. Create file multiquery.php and paste the below code to perform the operation.

<?php
include 'conn.php';
//Insert Multyple record into database

$name = $_POST['name'];
$email = $_POST['email'];
$status = $_POST['status'];
$name1 = $_POST['name1'];
$email1 = $_POST['email1'];
$status1 = $_POST['status1'];
$name2 = $_POST['name2'];
$email2 = $_POST['email2'];
$status2 = $_POST['status2'];

$query = "INSERT INTO `service`.`user` (`id`, `name`, `email`, `status`) VALUES (NULL, '$name', '$email', '$status');";
$query .= "INSERT INTO `service`.`user` (`id`, `name`, `email`, `status`) VALUES (NULL, '$name1', '$email1', '$status1');";
$query .= "INSERT INTO `service`.`user` (`id`, `name`, `email`, `status`) VALUES (NULL, '$name2', '$email2', '$status2');";

 if ($connection->multi_query($query)) {
 $msg = array("status" =>1 , "msg" => "All Records Inserted successfully");
} else {
    echo "Error: " . $query . "<br>" . mysqli_error($connention);
}

$json = $msg;

header('content-type: application/json');
echo json_encode($json);

@mysqli_close($conn);

That’s it hope this will help you in creating web service :-).