PHP MySQL REST API CRUD Tutorial

PHP MySQL REST API CRUD Tutorial

Introduction

        I was looking for a simple Full Stack PHP CRUD REST API Tutorial online, but there was not one tutorial which had both the UI and the REST APIs together. In most of the tutorials either the UI only is explained or else the REST APIs are explained without the UI. Hence I decided to come up with a simple post on this which can be used as a base to build your own full stack PHP app.
        In this post, we will build an Employee Dashboard App which can be used to view, create, update and delete Employee Data. We will be building the corresponding CRUD APIs. 

PHP

        PHP (Hypertext Preprocessor), originally stood for “Personal Home Page” and is a scripting language used for web development. Advantages of PHP include:
  1. State: Each and every web request starts from a completely blank slate.
  2. Concurrency: Each and every web request runs in a single PHP thread.
  3. Fast: As the PHP program operates at a request level, it is fast and efficient.
To know more about PHP please check the PHP website.

I prefer to run my PHP App on Apache Server as generally in production any PHP app would use Apache or a similar web server.

Requirements to Run the Application:
  1. PHP
  2. MySQL
  3. Apache
  4. IDE of your choice
PHP, Apache and MySQL Database should be setup and running in your machine. To setup, run and test if PHP and Apache web server are working fine, please refer to my post on: Apache and PHP Setup

To setup, run and test if the MySQL Database is working fine, please refer to my post on: MySQL Database Setup.

Once PHP, Apache and MySQL Database are setup and running in your machine, here are the additional steps required for the PHP app.

Step 1: MySQL Database Configuration

Create a PHP file for MySQL database configuration. I have named my database as: softwaredevelopercentral. My root user does not have a password. Hence here is my file: database.php 
    <?php 
    class Database {
        private $host = "127.0.0.1";
        private $database_name = "softwaredevelopercentral";
        private $username = "root";
        private $password = "";

        public $conn;

        public function getConnection(){
            $this->conn = null;
            try{
                $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->database_name, $this->username, $this->password);
                $this->conn->exec("set names utf8");
            }catch(PDOException $exception){
                echo "Database could not be connected: " . $exception->getMessage();
            }
            return $this->conn;
        }
    }  
?>

Step 2: Create the Employee Class

Create an Employee class with the Employee attributes and functions for viewing, creating, updating and deleting employee data. Here is the full file: employees.php: 
    <?php
    class Employee{

        // Connection
        private $conn;

        // Table
        private $db_table = "Employee";

        // Columns
        public $id;
        public $name;
        public $email;
        public $age;
        public $designation;
        public $created;

        // DB Connection
        public function __construct($db){
            $this->conn = $db;
        }

        // GET ALL
        public function getEmployees(){
            $sqlQuery = "SELECT id, name, email, age, designation, created FROM " . $this->db_table . "";
            $stmt = $this->conn->prepare($sqlQuery);
            $stmt->execute();
            return $stmt;
        }

        // CREATE
        public function createEmployee(){
            $sqlQuery = "INSERT INTO
                        ". $this->db_table ."
                    SET
                        name = :name, 
                        email = :email, 
                        age = :age, 
                        designation = :designation, 
                        created = :created";
        
            $stmt = $this->conn->prepare($sqlQuery);
        
            // sanitize the data
            $this->name=htmlspecialchars(strip_tags($this->name));
            $this->email=htmlspecialchars(strip_tags($this->email));
            $this->age=htmlspecialchars(strip_tags($this->age));
            $this->designation=htmlspecialchars(strip_tags($this->designation));
            $this->created=htmlspecialchars(strip_tags($this->created));
        
            // bind the data
            $stmt->bindParam(":name", $this->name);
            $stmt->bindParam(":email", $this->email);
            $stmt->bindParam(":age", $this->age);
            $stmt->bindParam(":designation", $this->designation);
            $stmt->bindParam(":created", $this->created);
        
            if($stmt->execute()){
               return true;
            }
            return false;
        }

        // GET SINGLE EMPLOYEE DATA
        public function getSingleEmployee(){
            $sqlQuery = "SELECT
                        id, 
                        name, 
                        email, 
                        age, 
                        designation, 
                        created
                      FROM
                        ". $this->db_table ."
                    WHERE 
                       id = ?
                    LIMIT 0,1";

            $stmt = $this->conn->prepare($sqlQuery);

            $stmt->bindParam(1, $this->id);

            $stmt->execute();

            $dataRow = $stmt->fetch(PDO::FETCH_ASSOC);
            
            $this->name = $dataRow['name'];
            $this->email = $dataRow['email'];
            $this->age = $dataRow['age'];
            $this->designation = $dataRow['designation'];
            $this->created = $dataRow['created'];
        }        

        // UPDATE
        public function updateEmployee(){
            $sqlQuery = "UPDATE
                        ". $this->db_table ."
                    SET
                        name = :name, 
                        email = :email, 
                        age = :age, 
                        designation = :designation, 
                        created = :created
                    WHERE 
                        id = :id";
        
            $stmt = $this->conn->prepare($sqlQuery);
        
            $this->name=htmlspecialchars(strip_tags($this->name));
            $this->email=htmlspecialchars(strip_tags($this->email));
            $this->age=htmlspecialchars(strip_tags($this->age));
            $this->designation=htmlspecialchars(strip_tags($this->designation));
            $this->created=htmlspecialchars(strip_tags($this->created));
            $this->id=htmlspecialchars(strip_tags($this->id));
        
            // bind data
            $stmt->bindParam(":name", $this->name);
            $stmt->bindParam(":email", $this->email);
            $stmt->bindParam(":age", $this->age);
            $stmt->bindParam(":designation", $this->designation);
            $stmt->bindParam(":created", $this->created);
            $stmt->bindParam(":id", $this->id);
        
            if($stmt->execute()){
               return true;
            }
            return false;
        }

        // DELETE
        function deleteEmployee(){
            $sqlQuery = "DELETE FROM " . $this->db_table . " WHERE id = ?";
            $stmt = $this->conn->prepare($sqlQuery);
        
            $this->id=htmlspecialchars(strip_tags($this->id));
        
            $stmt->bindParam(1, $this->id);
        
            if($stmt->execute()){
                return true;
            }
            return false;
        }

    }
?>

Step 3: Create the CRUD APIs

Please Note: All the APIs are present in the api folder of the project structure.

Create API

Create Employee API is a POST API. Here is the file: create.php:
<?php
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");
    header("Access-Control-Allow-Methods: POST");
    header("Access-Control-Max-Age: 3600");
    header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");

    include_once '../config/database.php';
    include_once '../class/employees.php';

    $database = new Database();
    $db = $database->getConnection();

    $item = new Employee($db);

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

    $item->name = $data->name;
    $item->email = $data->email;
    $item->age = $data->age;
    $item->designation = $data->designation;
    $item->created = date('Y-m-d H:i:s');
    
    if($item->createEmployee()){
        echo 'Employee created successfully.';
    } else{
        echo 'Employee could not be created.';
    }
?>

Read API

Read Employee has two GET APIs. The first GET API is to get all employee details. Here is the file: read.php:
<?php
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");
    
    include_once '../config/database.php';
    include_once '../class/employees.php';

    $database = new Database();
    $db = $database->getConnection();

    $items = new Employee($db);

    $stmt = $items->getEmployees();
    $itemCount = $stmt->rowCount();

    if($itemCount > 0){
        
        $employeeArr = array();
        $employeeArr["body"] = array();
        $employeeArr["itemCount"] = $itemCount;

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            extract($row);
            $e = array(
                "id" => $id,
                "name" => $name,
                "email" => $email,
                "age" => $age,
                "designation" => $designation,
                "created" => $created
            );

            array_push($employeeArr["body"], $e);
        }
        echo json_encode($employeeArr);
    }

    else{
        http_response_code(404);
        echo json_encode(
            array("message" => "No data found.")
        );
    }
?>
The second GET API is to get a single employee data. Here is the file: single_read.php:
<?php
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");
    header("Access-Control-Allow-Methods: POST");
    header("Access-Control-Max-Age: 3600");
    header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");

    include_once '../config/database.php';
    include_once '../class/employees.php';

    $database = new Database();
    $db = $database->getConnection();

    $item = new Employee($db);

    $item->id = isset($_GET['id']) ? $_GET['id'] : die();
  
    $item->getSingleEmployee();

    if($item->name != null){
        // create array
        $emp_arr = array(
            "id" =>  $item->id,
            "name" => $item->name,
            "email" => $item->email,
            "age" => $item->age,
            "designation" => $item->designation,
            "created" => $item->created
        );
      
        http_response_code(200);
        echo json_encode($emp_arr);
    }
      
    else{
        http_response_code(404);
        echo json_encode("Employee is not found.");
    }
?>

Update API

Update Employee API is a PUT API. Here is the file: update.php:
<?php
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");
    header("Access-Control-Allow-Methods: PUT");
    header("Access-Control-Max-Age: 3600");
    header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
    
    include_once '../config/database.php';
    include_once '../class/employees.php';
    
    $database = new Database();
    $db = $database->getConnection();
    
    $item = new Employee($db);
    
    $data = json_decode(file_get_contents("php://input"));

    $item->id = $data->id;
    
    // employee values
    $item->name = $data->name;
    $item->email = $data->email;
    $item->age = $data->age;
    $item->designation = $data->designation;
    $item->created = date('Y-m-d H:i:s');
    
    if($item->updateEmployee()){
        echo json_encode("Employee data is updated.");
    } else{
        echo json_encode("Employee data could not be updated");
    }
?>

Delete API

Delete Employee has two DELETE APIs. The first DELETE API can be called as an API from any other App or system. Here is the file: delete.php:
<?php
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");
    header("Access-Control-Allow-Methods: DELETE");
    header("Access-Control-Max-Age: 3600");
    header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
    
    include_once '../config/database.php';
    include_once '../class/employees.php';
    
    $database = new Database();
    $db = $database->getConnection();
    
    $item = new Employee($db);

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

    $item->id = $data->id;
    
    if($item->deleteEmployee()){
        echo json_encode("Employee deleted.");
    } else{
        echo json_encode("Data could not be deleted");
    }
?>
The second DELETE API is called only from the Employee Dashboards UI. Here is the file: deleteUI.php 
<?php
    header("Access-Control-Allow-Origin: *");
    header("Content-Type: application/json; charset=UTF-8");
    header("Access-Control-Allow-Methods: DELETE");
    header("Access-Control-Max-Age: 3600");
    header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
    
    include_once '../config/database.php';
    include_once '../class/employees.php';
    
    $database = new Database();
    $db = $database->getConnection();
    
    $item = new Employee($db);

    $item->id = isset($_GET['id']) ? $_GET['id'] : die();
    
    if($item->deleteEmployee()){
        echo json_encode("Employee deleted.");
    } else{
        echo json_encode("Data could not be deleted");
    }
?>

Step 4: Create the UI Pages and add the required logic

Employee Dashboard

The landing page for this Employee Dashboard App is index.php and it shows the details of all the employees. It calls the Read GET API to get the data for all the Employees which is in the file read.php. 

Please Note: index.php is present in the root of the project folder structure. 

Here is the file index.php: 
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Employee Dashboard</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
    <style>
        .wrapper{
            width: 1200px;
            margin: 0 auto;
        }
        table tr td:last-child{
            width: 240px;
        }
    </style>
    <script>
        $(document).ready(function(){
            $('[data-toggle="tooltip"]').tooltip();
        });
    </script>
</head>
<body>
<div class="wrapper">
    <div class="container-fluid">
        <div class="row">
            <div class="col-md-100">
                <div class="mt-5 mb-3 clearfix">
                    <h2 class="pull-left">Employees Details</h2>
                    <a href="view/create.php" class="btn btn-success pull-right"><i class="fa fa-plus"></i> Add New Employee</a>
                </div>
                <?php

                $url = "http://localhost:8080/php-mysql-crud-rest/api/read.php";

                $ch = curl_init();
                curl_setopt($ch, CURLOPT_URL, $url);
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($ch, CURLOPT_HEADER, false);
                curl_setopt($ch, CURLOPT_FOLLOWLOCATION, false);

                $httpCode = curl_getinfo($ch , CURLINFO_HTTP_CODE); // this results 0 every time
                $response = curl_exec($ch);

                if ($response === false)
                    $response = curl_error($ch);

                $obj = json_decode($response);

                      if($obj->body > 0){
                        echo '<table class="table table-bordered table-striped">';
                        echo "<thead>";
                        echo "<tr>";
                        echo "<th>#</th>";
                        echo "<th>Name</th>";
                        echo "<th>Email</th>";
                        echo "<th>Age</th>";
                        echo "<th>Designation</th>";
                        echo "<th>Created</th>";
                        echo "<th>Action</th>";
                        echo "</tr>";
                        echo "</thead>";
                        echo "<tbody>";
                        //while($row = mysqli_fetch_array($result)){
                          for ($x = 0; $x < $obj->itemCount; $x++) {
                            echo "<tr>";
                            echo "<td>" . $obj->body[$x]->id . "</td>";
                            echo "<td>" . $obj->body[$x]->name . "</td>";
                            echo "<td>" . $obj->body[$x]->email . "</td>";
                            echo "<td>" . $obj->body[$x]->age . "</td>";
                            echo "<td>" . $obj->body[$x]->designation . "</td>";
                            echo "<td>" . $obj->body[$x]->created . "</td>";
                            echo "<td>";
                            echo '<a href="view/read.php?id='. $obj->body[$x]->id .'" class="mr-3" title="View Record" data-toggle="tooltip"><span class="fa fa-eye"></span></a>';
                            echo '<a href="view/update.php?id='. $obj->body[$x]->id .'" class="mr-3" title="Update Record" data-toggle="tooltip"><span class="fa fa-pencil"></span></a>';
                            echo '<a href="view/delete.php?id='. $obj->body[$x]->id .'" title="Delete Record" data-toggle="tooltip"><span class="fa fa-trash"></span></a>';
                            echo "</td>";
                            echo "</tr>";
                        }
                        echo "</tbody>";
                        echo "</table>";
                    } else{
                        echo '<div class="alert alert-danger"><em>No records were found.</em></div>';
                    }
                ?>
            </div>
        </div>
    </div>
</div>
</body>
</html>

View Single Employee

Please Note: All the other UI pages are present in the view folder of the project structure.

This calls Read GET API to fetch single employee data which is present in the file single_read.php. Here is the file read.php: 
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>View Record</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        .wrapper{
            width: 1200px;
            margin: 0 auto;
        }
    </style>
</head>
<body>
<?php
// Check existence of id parameter before processing further
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
    
    $url = "http://localhost:8080/php-mysql-crud-rest/api/single_read.php/?id=";
    $url.=$_GET["id"];


    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_HEADER, false);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, false);

    $httpCode = curl_getinfo($ch , CURLINFO_HTTP_CODE); // this results 0 every time
    $response = curl_exec($ch);

    if ($response === false)
        $response = curl_error($ch);

    $obj = json_decode($response,true);
    
} else{
    // URL doesn't contain id parameter. Redirect to error page
    header("location: error.php");
    exit();
}
?>
<div class="wrapper">
    <div class="container-fluid">
        <div class="row">
            <div class="col-md-12">
                <h2 class="mt-5 mb-3">View Record</h2>
                <div class="form-group">
                    <label>Name</label>
                    <p><b><?php echo $obj["name"]; ?></b></p>
                </div>
                <div class="form-group">
                    <label>Email</label>
                    <p><b><?php echo $obj["email"]; ?></b></p>
                </div>
                <div class="form-group">
                    <label>Age</label>
                    <p><b><?php echo $obj["age"]; ?></b></p>
                </div>
                <div class="form-group">
                    <label>Designation</label>
                    <p><b><?php echo $obj["designation"]; ?></b></p>
                </div>
                <p><a href="../index.php" class="btn btn-primary">Back</a></p>
            </div>
        </div>
    </div>
</div>
</body>
</html>

Create Employee UI

Here is the file create.php: 
<?php

// Define variables and initialize with empty values
$name = $email = $age = $designation = "";
$name_err = $email_err = $age_err = $designation_err = "";

// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
    // Validate name
    $input_name = trim($_POST["name"]);
    if(empty($input_name)){
        $name_err = "Please enter a name.";
    } elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){
        $name_err = "Please enter a valid name.";
    } else{
        $name = $input_name;
    }

    // Validate email
    $input_email = trim($_POST["email"]);
    if(empty($input_email)){
        $email_err = "Please enter an email.";
    } else{
        $email = $input_email;
    }

    // Validate age
    $input_age = trim($_POST["age"]);
    if(empty($input_age)){
        $age_err = "Please enter the age.";
    } elseif(!ctype_digit($input_age)){
        $age_err = "Please enter a positive integer value.";
    } else{
        $age = $input_age;
    }

    // Validate designation
    $input_designation = trim($_POST["designation"]);
    if(empty($input_designation)){
        $designation_err = "Please enter a designation.";
    } elseif(!filter_var($input_designation, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){
        $designation_err = "Please enter a valid designation.";
    } else{
        $designation = $input_designation;
    }

    // Check input errors before inserting in database
    if(empty($name_err) && empty($email_err) && empty($age_err) && empty($designation_err)) {
        include_once '../config/database.php';
        include_once '../class/employees.php';

        $database = new Database();
        $db = $database->getConnection();

        $item = new Employee($db);

        // employee values
        $item->name = $name;
        $item->email = $email;
        $item->age = $age;
        $item->designation = $designation;
        $item->created = date('Y-m-d H:i:s');

        if ($item->createEmployee()) {
            echo '<div class="alert alert-success">' . json_encode("Employee created successfully.") . '</div>';
            echo '<p><a href="../index.php" class="btn btn-primary">Back</a></p>';
        } else {
            echo json_encode("Employee could not be created");
        }
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Create Record</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        .wrapper{
            width: 1200px;
            margin: 0 auto;
        }
    </style>
</head>
<body>
<div class="wrapper">
    <div class="container-fluid">
        <div class="row">
            <div class="col-md-12">
                <h2 class="mt-5">Create Record</h2>
                <p>Please fill this form and submit to add employee record to the database.</p>
                <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
                    <div class="form-group">
                        <label>Name</label>
                        <input type="text" name="name" class="form-control <?php echo (!empty($name_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $name; ?>">
                        <span class="invalid-feedback"><?php echo $name_err;?></span>
                    </div>
                    <div class="form-group">
                        <label>Email</label>
                        <input type="text" name="email" class="form-control <?php echo (!empty($email_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $email; ?>">
                        <span class="invalid-feedback"><?php echo $email_err;?></span>
                    </div>
                    <div class="form-group">
                        <label>Age</label>
                        <input type="text" name="age" class="form-control <?php echo (!empty($age_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $age; ?>">
                        <span class="invalid-feedback"><?php echo $age_err;?></span>
                    </div>
                    <div class="form-group">
                        <label>Designation</label>
                        <input type="text" name="designation" class="form-control <?php echo (!empty($designation_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $designation; ?>">
                        <span class="invalid-feedback"><?php echo $designation_err;?></span>
                    </div>
                    <input type="submit" class="btn btn-primary" value="Submit">
                    <a href="../index.php" class="btn btn-secondary ml-2">Cancel</a>
                </form>
            </div>
        </div>
    </div>
</div>
</body>
</html>
Update Employee UI

Here is the file update.php: 
<?php

// Check existence of id parameter before processing further
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
    $url = "http://localhost:8080/php-mysql-crud-rest/api/single_read.php/?id=";
    $url.=$_GET["id"];

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_HEADER, false);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, false);

    $httpCode = curl_getinfo($ch , CURLINFO_HTTP_CODE); // this results 0 every time
    $response = curl_exec($ch);

    if ($response === false)
        $response = curl_error($ch);

    $obj = json_decode($response,true);
} else{
    // URL doesn't contain id parameter. Redirect to error page
    header("location: error.php");
    exit();
}

// Define variables and initialize with empty values
$name = $email = $age = $designation = "";
$name_err = $email_err = $salary_err = $designation_err = "";

// Processing form data when form is submitted
if(isset($_POST["id"]) && !empty($_POST["id"])){
    // Get hidden input value
    $id = $_GET["id"];

    // Validate name
    $input_name = trim($_POST["name"]);
    if(empty($input_name)){
        $name_err = "Please enter a name.";
    } elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){
        $name_err = "Please enter a valid name.";
    } else{
        $name = $input_name;
    }

    // Validate email
    $input_email = trim($_POST["email"]);
    if(empty($input_email)){
        $email_err = "Please enter an email.";
    } else{
        $email = $input_email;
    }

    // Validate salary
    $input_salary = trim($_POST["salary"]);
    if(empty($input_salary)){
        $salary_err = "Please enter the age.";
    } elseif(!ctype_digit($input_salary)){
        $salary_err = "Please enter a positive integer value.";
    } else{
        $age = $input_salary;
    }

    // Validate designation
    $input_designation = trim($_POST["designation"]);
    if(empty($input_designation)){
        $designation_err = "Please enter a designation.";
    } else{
        $designation = $input_designation;
    }

    // Check input errors before inserting in database
    if(empty($name_err) && empty($email_err) && empty($salary_err) && empty($designation_err)){

        include_once '../config/database.php';
        include_once '../class/employees.php';

        $database = new Database();
        $db = $database->getConnection();

        $item = new Employee($db);

        $item->id = $id;

        // employee values
        $item->name = $name;
        $item->email = $email;
        $item->age = $age;
        $item->designation = $designation;
        $item->created = date('Y-m-d H:i:s');

        if($item->updateEmployee()){
            echo '<div class="alert alert-success">'.json_encode("Employee data updated.").'</div>';
            echo '<p><a href="../index.php" class="btn btn-primary">Back</a></p>';
        } else{
            echo json_encode("Data could not be updated");
        }
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Update Record</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        .wrapper{
            width: 1200px;
            margin: 0 auto;
        }
    </style>
</head>
<body>

<div class="wrapper">
    <div class="container-fluid">
        <div class="row">
            <div class="col-md-12">
                <h2 class="mt-5 mb-3">View Record</h2>
                <div class="form-group">
                    <label>Name</label>
                    <p><b><?php echo $obj["name"]; ?></b></p>
                </div>
                <div class="form-group">
                    <label>Email</label>
                    <p><b><?php echo $obj["email"]; ?></b></p>
                </div>
                <div class="form-group">
                    <label>Age</label>
                    <p><b><?php echo $obj["age"]; ?></b></p>
                </div>
                <div class="form-group">
                    <label>Designation</label>
                    <p><b><?php echo $obj["designation"]; ?></b></p>
                </div>
            </div>
        </div>
    </div>
</div>

<div class="wrapper">
    <div class="container-fluid">
        <div class="row">
            <div class="col-md-12">
                <h2 class="mt-5">Update Record</h2>
                <p>Please edit the input values and submit to update the employee record.</p>
                <form action="<?php echo htmlspecialchars(basename($_SERVER['REQUEST_URI'])); ?>" method="post">
                    <div class="form-group">
                        <label>Name</label>
                        <input type="text" name="name" class="form-control <?php echo (!empty($name_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $name; ?>">
                        <span class="invalid-feedback"><?php echo $name_err;?></span>
                    </div>
                    <div class="form-group">
                        <label>Email</label>
                        <input name="email" class="form-control <?php echo (!empty($email_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $email; ?>">
                        <span class="invalid-feedback"><?php echo $email_err;?></span>
                    </div>
                    <div class="form-group">
                        <label>Age</label>
                        <input type="text" name="salary" class="form-control <?php echo (!empty($salary_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $age; ?>">
                        <span class="invalid-feedback"><?php echo $salary_err;?></span>
                    </div>
                    <div class="form-group">
                        <label>Designation</label>
                        <input type="text" name="designation" class="form-control <?php echo (!empty($designation_err)) ? 'is-invalid' : ''; ?>" value="<?php echo $designation; ?>">
                        <span class="invalid-feedback"><?php echo $designation_err;?></span>
                    </div>
                    <input type="hidden" name="id" value="<?php echo $id; ?>"/>
                    <input type="submit" class="btn btn-primary" value="Submit">
                    <a href="../index.php" class="btn btn-secondary ml-2">Cancel</a>
                </form>
            </div>
        </div>
    </div>
</div>
</body>
</html>
Delete Employee UI

This calls DELETE API to delete single employee data which is present in the file deleteUI.php. Here is the file delete.php: 
<?php

// Check existence of id parameter before processing further
if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){
    //View Logic
    $urlView = "http://localhost:8080/php-mysql-crud-rest/api/single_read.php/?id=";
    $urlView.=$_GET["id"];

    //echo $url;

    $chView = curl_init();
    curl_setopt($chView, CURLOPT_URL, $urlView);
    curl_setopt($chView, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($chView, CURLOPT_HEADER, false);
    curl_setopt($chView, CURLOPT_FOLLOWLOCATION, false);

    $httpCodeView = curl_getinfo($chView , CURLINFO_HTTP_CODE); // this results 0 every time
    $responseView = curl_exec($chView);

    if ($responseView === false)
        $responseView = curl_error($chView);

    $objView = json_decode($responseView,true);
}
else{
    // URL doesn't contain id parameter. Redirect to error page
    header("location: error.php");
    exit();
}

// Process delete operation after confirmation
if(isset($_POST["id"]) && !empty($_POST["id"])) {
// Check existence of id parameter before processing further
    // Get hidden input value
    $id = $_GET["id"];
    if (isset($_GET["id"]) && !empty(trim($_GET["id"]))) {

        $url = "http://localhost:8080/php-mysql-crud-rest/api/deleteUI.php/?id=";
        $url .= $_GET["id"];

        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_HEADER, false);
        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, false);

        $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE); // this results 0 every time
        $response = curl_exec($ch);

        if ($response === false) {
            $response = curl_error($ch);
        }
        else{
            echo '<div class="alert alert-success">'.json_encode("Employee deleted successfully.").'</div>';
            echo '<p><a href="../index.php" class="btn btn-primary">Back</a></p>';
        }

        $obj = json_decode($response, true);
    } else {
        // Check existence of id parameter
        if (empty(trim($_GET["id"]))) {
            // URL doesn't contain id parameter. Redirect to error page
            header("location: error.php");
            exit();
        }
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Delete Record</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        .wrapper{
            width: 1200px;
            margin: 0 auto;
        }
    </style>
</head>
<body>
<div class="wrapper">
    <div class="container-fluid">
        <div class="row">
            <div class="col-md-12">
                <h2 class="mt-5 mb-3">View Record</h2>
                <div class="form-group">
                    <label>Name</label>
                    <p><b><?php echo $objView["name"]; ?></b></p>
                </div>
                <div class="form-group">
                    <label>Email</label>
                    <p><b><?php echo $objView["email"]; ?></b></p>
                </div>
                <div class="form-group">
                    <label>Age</label>
                    <p><b><?php echo $objView["age"]; ?></b></p>
                </div>
                <div class="form-group">
                    <label>Designation</label>
                    <p><b><?php echo $objView["designation"]; ?></b></p>
                </div>
            </div>
        </div>
    </div>
</div>

<div class="wrapper">
    <div class="container-fluid">
        <div class="row">
            <div class="col-md-12">
                <h2 class="mt-5 mb-3">Delete Record</h2>
                <form action="<?php echo htmlspecialchars(basename($_SERVER['REQUEST_URI'])); ?>" method="post">
                    <div class="alert alert-danger">
                        <p>Are you sure you want to delete this employee record?</p>
                        <p>
                            <input type="hidden" name="id" value="<?php echo $id; ?>"/>
                            <input type="submit" class="btn btn-danger" value="Yes">
                            <a href="../index.php" class="btn btn-secondary ml-2">No</a>
                        </p>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>
</body>
</html>

Error UI

We re-diect to an error page when any error is detected in the workflow. Here is the file error.php: 
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Error</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <style>
        .wrapper{
            width: 600px;
            margin: 0 auto;
        }
    </style>
</head>
<body>
<div class="wrapper">
    <div class="container-fluid">
        <div class="row">
            <div class="col-md-12">
                <h2 class="mt-5 mb-3">Invalid Request</h2>
                <div class="alert alert-danger">Sorry, you've made an invalid request. Please <a href="../index.php" class="alert-link">go back</a> and try again.</div>
            </div>
        </div>
    </div>
</div>
</body>
</html>

Run Application:

1. Execute the following in the MySQL Database 
mysql> use softwaredevelopercentral;
Database changed
mysql> show tables;
Empty set (0.42 sec)

mysql> CREATE TABLE IF NOT EXISTS `Employee` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(256) NOT NULL,
    ->   `email` varchar(50),
    ->   `age` int(11) NOT NULL,
    ->   `designation` varchar(255) NOT NULL,
    ->   `created` datetime NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> )ENGINE=InnoDB  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 3 warnings (0.68 sec)

mysql> show tables;
+------------------------------------+
| Tables_in_softwaredevelopercentral |
+------------------------------------+
| employee                           |
+------------------------------------+
1 row in set (0.01 sec)

mysql> INSERT INTO `Employee` (`id`, `name`, `email`, `age`, `designation`, `created`) VALUES
    -> (1, 'Jim Dane', 'jimdane@gmail.com', 33, 'Software Engineer', '2022-06-01 01:15:30'),
    -> (2, 'Jane Foster', 'jane.foster@yahoo.com', 28, 'HR Manager', '2022-03-03 03:25:10'),
    -> (3, 'Timothy Hade', 'timhahe@gmail.com', 34, 'Accounts Manager', '2020-09-20 05:50:29'),
    -> (4, 'Sam Joe', 'sj@yahoo.com', 40, 'Software Architect', '2019-05-15 06:14:32'),
    -> (5, 'Caroline Kane', 'carolkane@gmail.com', 45, 'General Manager', '2015-04-06 07:31:52');
Query OK, 5 rows affected (0.16 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from employee;
+----+---------------+-----------------------+-----+--------------------+---------------------+
| id | name          | email                 | age | designation        | created             |
+----+---------------+-----------------------+-----+--------------------+---------------------+
|  1 | Jim Dane      | jimdane@gmail.com     |  33 | Software Engineer  | 2022-06-01 01:15:30 |
|  2 | Jane Foster   | jane.foster@yahoo.com |  28 | HR Manager         | 2022-03-03 03:25:10 |
|  3 | Timothy Hade  | timhahe@gmail.com     |  34 | Accounts Manager   | 2020-09-20 05:50:29 |
|  4 | Sam Joe       | sj@yahoo.com          |  40 | Software Architect | 2019-05-15 06:14:32 |
|  5 | Caroline Kane | carolkane@gmail.com   |  45 | General Manager    | 2015-04-06 07:31:52 |
+----+---------------+-----------------------+-----+--------------------+---------------------+
5 rows in set (0.00 sec)
2. Ensure that Tomcat Web Server is running and paste the entire project folder in the Apache htdocs folder. For me this is: C:\Apache24\htdocs 

3. Open a Browser of your choice and enter the URL below:
    
4. View a Record by clicking on the view record button
5. Create a new Employee by clicking on Add New Employee button
Check the Database
mysql> select * from employee;
+----+---------------+-----------------------+-----+--------------------+---------------------+
| id | name          | email                 | age | designation        | created             |
+----+---------------+-----------------------+-----+--------------------+---------------------+
|  1 | Jim Dane      | jimdane@gmail.com     |  33 | Software Engineer  | 2022-06-01 01:15:30 |
|  2 | Jane Foster   | jane.foster@yahoo.com |  28 | HR Manager         | 2022-03-03 03:25:10 |
|  3 | Timothy Hade  | timhahe@gmail.com     |  34 | Accounts Manager   | 2020-09-20 05:50:29 |
|  4 | Sam Joe       | sj@yahoo.com          |  40 | Software Architect | 2019-05-15 06:14:32 |
|  5 | Caroline Kane | carolkane@gmail.com   |  45 | General Manager    | 2015-04-06 07:31:52 |
|  6 | Lydia Taylor  | lydia@gm.com          |  30 | HR Operations Lead | 2022-09-18 14:06:44 |
+----+---------------+-----------------------+-----+--------------------+---------------------+
6 rows in set (0.00 sec)
6. Update Employee by clicking on Update Record icon

Check the database 
mysql> select * from employee;
+----+---------------+-----------------------+-----+--------------------+---------------------+
| id | name          | email                 | age | designation        | created             |
+----+---------------+-----------------------+-----+--------------------+---------------------+
|  1 | Jim Dane      | jimdane@gmail.com     |  33 | Software Engineer  | 2022-06-01 01:15:30 |
|  2 | Jane Foster   | jane.foster@yahoo.com |  28 | HR Manager         | 2022-03-03 03:25:10 |
|  3 | Timothy Hade  | timhahe@gmail.com     |  34 | Accounts Manager   | 2020-09-20 05:50:29 |
|  4 | Sam Joe       | sj@yahoo.com          |  40 | Software Architect | 2019-05-15 06:14:32 |
|  5 | Caroline Kane | carolkane@gmail.com   |  45 | General Manager    | 2015-04-06 07:31:52 |
|  6 | Lydia Taylor  | lydia.taylor@ed.com   |  30 | HR Operations Lead | 2022-09-18 14:14:53 |
+----+---------------+-----------------------+-----+--------------------+---------------------+
6 rows in set (0.00 sec)
7. Delete an Employee using the Delete Record icon
Check the database 
mysql> select * from employee;
+----+---------------+-----------------------+-----+--------------------+---------------------+
| id | name          | email                 | age | designation        | created             |
+----+---------------+-----------------------+-----+--------------------+---------------------+
|  1 | Jim Dane      | jimdane@gmail.com     |  33 | Software Engineer  | 2022-06-01 01:15:30 |
|  2 | Jane Foster   | jane.foster@yahoo.com |  28 | HR Manager         | 2022-03-03 03:25:10 |
|  3 | Timothy Hade  | timhahe@gmail.com     |  34 | Accounts Manager   | 2020-09-20 05:50:29 |
|  4 | Sam Joe       | sj@yahoo.com          |  40 | Software Architect | 2019-05-15 06:14:32 |
|  5 | Caroline Kane | carolkane@gmail.com   |  45 | General Manager    | 2015-04-06 07:31:52 |
+----+---------------+-----------------------+-----+--------------------+---------------------+
5 rows in set (0.00 sec)

API calls and results:

1. Read GET API to fetch all the Employee Data
    Response: 
{
    "body": [
        {
            "id": 1,
            "name": "Jim Dane",
            "email": "jimdane@gmail.com",
            "age": 33,
            "designation": "Software Engineer",
            "created": "2022-06-01 01:15:30"
        },
        {
            "id": 2,
            "name": "Jane Foster",
            "email": "jane.foster@yahoo.com",
            "age": 28,
            "designation": "HR Manager",
            "created": "2022-03-03 03:25:10"
        },
        {
            "id": 3,
            "name": "Timothy Hade",
            "email": "timhahe@gmail.com",
            "age": 34,
            "designation": "Accounts Manager",
            "created": "2020-09-20 05:50:29"
        },
        {
            "id": 4,
            "name": "Sam Joe",
            "email": "sj@yahoo.com",
            "age": 40,
            "designation": "Software Architect",
            "created": "2019-05-15 06:14:32"
        },
        {
            "id": 5,
            "name": "Caroline Kane",
            "email": "carolkane@gmail.com",
            "age": 45,
            "designation": "General Manager",
            "created": "2015-04-06 07:31:52"
        }
    ],
    "itemCount": 5
}
2. Read GET API to fetch a single Employee Record
    Response: 
  {
    "id": "2",
    "name": "Jane Foster",
    "email": "jane.foster@yahoo.com",
    "age": 28,
    "designation": "HR Manager",
    "created": "2022-03-03 03:25:10"
}
3. Create POST API to create an Employee
    Body:  
  {
    "name": "Irene Jones",
    "email": "irene.jones@ed.com",
    "age": 29,
    "designation": "Finance Analyst"
   }
Check Database
mysql> select * from employee;
+----+---------------+-----------------------+-----+--------------------+---------------------+
| id | name          | email                 | age | designation        | created             |
+----+---------------+-----------------------+-----+--------------------+---------------------+
|  1 | Jim Dane      | jimdane@gmail.com     |  33 | Software Engineer  | 2022-06-01 01:15:30 |
|  2 | Jane Foster   | jane.foster@yahoo.com |  28 | HR Manager         | 2022-03-03 03:25:10 |
|  3 | Timothy Hade  | timhahe@gmail.com     |  34 | Accounts Manager   | 2020-09-20 05:50:29 |
|  4 | Sam Joe       | sj@yahoo.com          |  40 | Software Architect | 2019-05-15 06:14:32 |
|  5 | Caroline Kane | carolkane@gmail.com   |  45 | General Manager    | 2015-04-06 07:31:52 |
|  7 | Irene Jones   | irene.jones@ed.com    |  29 | Finance Analyst    | 2022-09-18 15:20:07 |
+----+---------------+-----------------------+-----+--------------------+---------------------+
6 rows in set (0.00 sec)
4. Update PUT API to update an Employee 
    Body:  
  {
    "id": 7,
    "name": "Irene Jones",
    "email": "irene.j@edb.com",
    "age": 29,
    "designation": "Finance Analyst"
  }
Check Database 
mysql> select * from employee;
+----+---------------+-----------------------+-----+--------------------+---------------------+
| id | name          | email                 | age | designation        | created             |
+----+---------------+-----------------------+-----+--------------------+---------------------+
|  1 | Jim Dane      | jimdane@gmail.com     |  33 | Software Engineer  | 2022-06-01 01:15:30 |
|  2 | Jane Foster   | jane.foster@yahoo.com |  28 | HR Manager         | 2022-03-03 03:25:10 |
|  3 | Timothy Hade  | timhahe@gmail.com     |  34 | Accounts Manager   | 2020-09-20 05:50:29 |
|  4 | Sam Joe       | sj@yahoo.com          |  40 | Software Architect | 2019-05-15 06:14:32 |
|  5 | Caroline Kane | carolkane@gmail.com   |  45 | General Manager    | 2015-04-06 07:31:52 |
|  7 | Irene Jones   | irene.j@edb.com       |  29 | Finance Analyst    | 2022-09-18 15:40:33 |
+----+---------------+-----------------------+-----+--------------------+---------------------+
6 rows in set (0.00 sec)
5. DELETE API to delete an Employee
    Body: 
  {
    "id": 7
  }
Check Database 
mysql> select * from employee;
+----+---------------+-----------------------+-----+--------------------+---------------------+
| id | name          | email                 | age | designation        | created             |
+----+---------------+-----------------------+-----+--------------------+---------------------+
|  1 | Jim Dane      | jimdane@gmail.com     |  33 | Software Engineer  | 2022-06-01 01:15:30 |
|  2 | Jane Foster   | jane.foster@yahoo.com |  28 | HR Manager         | 2022-03-03 03:25:10 |
|  3 | Timothy Hade  | timhahe@gmail.com     |  34 | Accounts Manager   | 2020-09-20 05:50:29 |
|  4 | Sam Joe       | sj@yahoo.com          |  40 | Software Architect | 2019-05-15 06:14:32 |
|  5 | Caroline Kane | carolkane@gmail.com   |  45 | General Manager    | 2015-04-06 07:31:52 |
+----+---------------+-----------------------+-----+--------------------+---------------------+
5 rows in set (0.00 sec)

Conclusion and GitHub link:

    This tutorial gives a comprehensive overview of a Full Stack PHP App with integration to MySQL Database, REST APIs and CRUD operations. The code for the application used in this post is available on GitHub. 
    Learn the most popular and trending technologies like Blockchain, Cryptocurrency, Machine Learning, Chatbots, Internet of Things (IoT), Big Data Processing, Elastic Stack, React, Highcharts, Progressive Web Application (PWA), Angular, gRPC, GraphQL, Golang, Akka HTTP, Play Framework, Dropwizard, Docker, Netflix Eureka, Netflix Zuul, Spring Cloud, Spring Boot, Flask and RESTful Web Service integration with MongoDB, Kafka, Redis, Aerospike, MySQL DB in simple steps by reading my most popular blog posts at Software Developer Central.   
    If you like my post, please feel free to share it using the share button just below this paragraph or next to the heading of the post. You can also tweet with #SoftwareDeveloperCentral on Twitter. To get a notification on my latest posts or to keep the conversation going, you can follow me on Twitter or Instagram. Please leave a note below if you have any questions or comments.



Comments

Post a Comment

Popular Posts

Elasticsearch, Logstash, Kibana Tutorial: Load MySQL Data into Elasticsearch

Image
Elasticsearch, Logstash, Kibana Tutorial: Load MySQL Data into Elasticsearch Introduction           I was searching for a tutorial online on all the elements of the "Elastic Stack" (formerly the "ELK stack") and all I found was either a tutorial on Elasticsearch only or a tutorial on Logstash only or a tutorial on Kibana only or a data migrate tutorial using Logstash and Elaticsearch. Hence I came up with this tutorial which gives a comprehensive overview of all the elements in the Elastic Stack. The use case that I am using in this post is a data load from MySQL DB into Elasticsearch using Logstash and visualizing this data using Kibana. Elasticsearch         Elasticsearch is a distributed, RESTful search and analytics engine that is based on Apache Lucene . It provides a distributed, multitenant-capable full-text search engine with an HTTP web interface and schema-free JSON documents. For more details on Elasticsearch, ...
Keep reading

Dropwizard MySQL Integration Tutorial

Image
Dropwizard MySQL Integration Tutorial Introduction         Dropwizard is one of the most popular and most used frameworks for building microservices. I am assuming that you have a Basic Knowledge of Dropwizard and have a Basic Dropwizard Application running in your machine. If not, please check my blog on Basic Dropwizard Application by going to the link:  Dropwizard Tutorial . In this tutorial, we will be integrating a Dropwizard Application with MySQL Database, which is a relational database, to perform CRUD operations. The same steps can be followed to connect to any other relational database. Requirements to Run the Application: Java Maven MySQL Database IDE of your choice MySQL Database should be setup and running in your machine. To setup, run and test if the MySQL Database is working fine, please refer to my post on: MySQL Database Setup . In this post, I am using a simple Employee Table to perform CRUD operations. Here is the s...
Keep reading

Send Email in Java

Image
Send Email in Java Introduction         Sending an email is one of the most important and most frequently used functionalities in a technology organization. A software developer should know how this is done. This post will give you a comprehensive overview of sending emails using Java mail libraries. This post will give you an overview of the following: Send a plain text email in HTML format. Create an attachment and send an email. Use an existing file as an attachment and send an email.  As RESTful web services are commonly used today, I will be using Dropwizard for building out the email functionality as RESTful APIs. Hence here are what I will be using in this post: Java mail libraries: To connect to email service provider, compose and send emails Gmail: Email service provider Dropwizard: For developing Email functionalities as RESTful web services         Dropwizard is one of the most popular and most used frameworks ...
Keep reading