Tag

CRUD Operation PHP MySQL

Browsing

This is the 2nd part of the CRUD Operations Tutorial & the 3rd part of the PHP Web Application Tutorial series.

If you missed previous parts, then you can find here => Getting Started with Web Application using PHP & MySQL | Tutorials

I’m assuming that you have followed the previous part of this Tutorial & created API for CRUD Operations using PHP & MySQL.

In this part, we’ll use AdminLTE Template to create UI for our CRUD API we have created in the previous part of this article.

We have also set up our Template & created the database in the First part of this Tutorial.

After following the previous article, your project structure will look like this.

php mysql crud operation structure

We know that we have created the master.php in the First part, which is our Layout page.

We also have “Doctor” Folder at the root directory of our Project. We’ll create all pages related to Doctor in this Folder.

Let’s start by creating a page to display all doctors data into a Table.

We have created “index.php” file inside “Doctor” Folder. Update this file with the Code below.

<?php
  $content = '<div class="row">
                <div class="col-xs-12">
                <div class="box">
                  <div class="box-header">
                    <h3 class="box-title">Dostors List</h3>
                  </div>
                  <!-- /.box-header -->
                  <div class="box-body">
                    <table id="doctors" class="table table-bordered table-hover">
                      <thead>
                      <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Phone</th>
                        <th>CNIC</th>
                        <th>Address</th>
                        <th>Action</th>
                      </tr>
                      </thead>
                      <tbody>
                      </tbody>
                      <tfoot>
                      <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Phone</th>
                        <th>CNIC</th>
                        <th>Address</th>
                        <th>Action</th>
                      </tr>
                      </tfoot>
                    </table>
                  </div>
                  <!-- /.box-body -->
                </div>
                <!-- /.box -->
              </div>
            </div>';

  include('../master.php');
?>
<!-- page script -->
<script>

  $(document).ready(function(){

    $.ajax({
        type: "GET",
        url: "../api/doctor/read.php",
        dataType: 'json',
        success: function(data) {
            var response="";
            for(var user in data){
                response += "<tr>"+
                "<td>"+data[user].name+"</td>"+
                "<td>"+data[user].email+"</td>"+
                "<td>"+data[user].phone+"</td>"+
                "<td>"+data[user].cnic+"</td>"+
                "<td>"+data[user].address+"</td>"+
                "<td><a href='update.php?id="+data[user].id+"'>Edit</a> | <a onClick=Remove('"+data[user].id+"')>Remove</a></td>"+
                "</tr>";
            }
            $(response).appendTo($("#doctors"));
        }
    });
  });

  function Remove(id){

    $.ajax(
        {
            type: "POST",
            url: '../api/doctor/delete.php',
            dataType: 'json',
            data: {
                id: id
            },
            error: function (result) {
                alert(result.responseText);
            },
            success: function (result) {
                if (result['status'] == true) {
                    alert("Successfully Removed Doctor!");
                    window.location.href = '/medibed/doctor';
                }
                else {
                    alert(result['message']);
                }
            }
        });
  }

</script>

Code Explanation

First of all, we have the content of this page in a PHP variable & then we simply included our master.php because it has the code for Layout page.

On document load, we’re using ajax to consume our read API to get all doctors.

We also have a function to remove a Doctor.

Now go to this Url => http://localhost/medibed/doctor/

you’ll see this page

php mysql crud operations

Doctor remove will also work fine because of remove function ready in our code.

Now we need to create pages to Create & Update a Doctor.

Create a new file inside “Doctor” Folder with the name “create.php” & add this Code.

<?php 
  $content = '<div class="row">
                <!-- left column -->
                <div class="col-md-12">
                  <!-- general form elements -->
                  <div class="box box-primary">
                    <div class="box-header with-border">
                      <h3 class="box-title">Add Doctor</h3>
                    </div>
                    <!-- /.box-header -->
                    <!-- form start -->
                    <form role="form">
                      <div class="box-body">
                        <div class="form-group">
                          <label for="exampleInputName1">Name</label>
                          <input type="text" class="form-control" id="name" placeholder="Enter Name">
                        </div>
                        <div class="form-group">
                          <label for="exampleInputEmail1">Email address</label>
                          <input type="email" class="form-control" id="email" placeholder="Enter email">
                        </div>
                        <div class="form-group">
                          <label for="exampleInputPassword1">Password</label>
                          <input type="password" class="form-control" id="password" placeholder="Password">
                        </div>
                        <div class="form-group">
                          <label for="exampleInputName1">Phone</label>
                          <input type="text" class="form-control" id="phone" placeholder="Enter Phone">
                        </div>
                        <div class="form-group">
                          <label for="exampleInputName1">CNIC</label>
                          <input type="text" class="form-control" id="cnic" placeholder="Enter CNIC">
                        </div>
                        <div class="form-group">
                          <label for="exampleInputName1">Address</label>
                          <input type="text" class="form-control" id="address" placeholder="Enter Address">
                        </div>
                      </div>
                      <!-- /.box-body -->

                      <div class="box-footer">
                        <input type="button" class="btn btn-primary" onClick="AddDoctor()" value="Submit"></input>
                      </div>
                    </form>
                  </div>
                  <!-- /.box -->
                </div>
              </div>';

  include('../master.php');
?>
<script>
  function AddDoctor(){
        $.ajax(
        {
            type: "POST",
            url: '../api/doctor/create.php',
            dataType: 'json',
            data: {
                name: $("#name").val(),
                email: $("#email").val(),				
                password: $("#password").val(),
                phone: $("#phone").val(),
                cnic: $("#cnic").val(),
                address: $("#address").val()
            },
            error: function (result) {
                alert(result.responseText);
            },
            success: function (result) {
                if (result['status'] == true) {
                    alert("Successfully Added New Doctor!");
                    window.location.href = '/medibed/doctor';
                }
                else {
                    alert(result['message']);
                }
            }
        });
    }
</script>

Now click on Create Doctor inside Doctors SideBar Menu or go to this Url => http://localhost/medibed/doctor/create.php

You’ll see this page

create user php mysql

Try to Create a new Doctor.

I hope everything is working as expected.

Let’s complete our Update Doctor functionality by creating a new file inside “Doctor” Folder as “update.php”

Add the below code inside this newly created file.

<?php
  $content = '<div class="row">
                <!-- left column -->
                <div class="col-md-12">
                  <!-- general form elements -->
                  <div class="box box-primary">
                    <div class="box-header with-border">
                      <h3 class="box-title">Update Doctor</h3>
                    </div>
                    <!-- /.box-header -->
                    <!-- form start -->
                    <form role="form">
                      <div class="box-body">
                        <div class="form-group">
                          <label for="exampleInputName1">Name</label>
                          <input type="text" class="form-control" id="name" placeholder="Enter Name">
                        </div>
                        <div class="form-group">
                          <label for="exampleInputEmail1">Email address</label>
                          <input type="email" class="form-control" id="email" placeholder="Enter email">
                        </div>
                        <div class="form-group">
                          <label for="exampleInputPassword1">Password</label>
                          <input type="password" class="form-control" id="password" placeholder="Password">
                        </div>
                        <div class="form-group">
                          <label for="exampleInputName1">Phone</label>
                          <input type="text" class="form-control" id="phone" placeholder="Enter Phone">
                        </div>
                        <div class="form-group">
                          <label for="exampleInputName1">CNIC</label>
                          <input type="text" class="form-control" id="cnic" placeholder="Enter CNIC">
                        </div>
                        <div class="form-group">
                          <label for="exampleInputName1">Address</label>
                          <input type="text" class="form-control" id="address" placeholder="Enter Address">
                        </div>
                      </div>
                      <!-- /.box-body -->

                      <div class="box-footer">
                        <input type="button" class="btn btn-primary" onClick="UpdateDoctor()" value="Update"></input>
                      </div>
                    </form>
                  </div>
                  <!-- /.box -->
                </div>
              </div>';
              
  include('../master.php');
?>
<script>
    $(document).ready(function(){

        $.ajax({
            type: "GET",
            url: "../api/doctor/read_single.php?id=<?php echo $_GET['id']; ?>",
            dataType: 'json',
            success: function(data) {
                $('#name').val(data['name']);
                $('#email').val(data['email']);
                $('#password').val(data['password']);
                $('#phone').val(data['phone']);
                $('#cnic').val(data['cnic']);
                $('#address').val(data['address']);
            },
            error: function (result) {
                console.log(result);
            },
        });
    });

    function UpdateDoctor(){
        $.ajax(
        {
            type: "POST",
            url: '../api/doctor/update.php',
            dataType: 'json',
            data: {
                id: <?php echo $_GET['id']; ?>,
                name: $("#name").val(),
                email: $("#email").val(),				
                password: $("#password").val(),
                phone: $("#phone").val(),
                cnic: $("#cnic").val(),
                address: $("#address").val()
            },
            error: function (result) {
                alert(result.responseText);
            },
            success: function (result) {
                if (result['status'] == true) {
                    alert("Successfully Updated Doctor!");
                    window.location.href = '/medibed/doctor';
                }
                else {
                    alert(result['message']);
                }
            }
        });
    }
</script>

</body>
</html>

Now you can also update any doctor record.

We have successfully created our CRUD(Create, Read, Update, Delete) Operations using Beautiful & Free UI Template.

You can also download the complete source code from GitHub.

Download Source Code

Please Comment If you found any problem.

You might be Interested in

Previous Part

Yes, I’d say a large percentage of most applications I’ve worked on is basic CRUD(Create, Read, Update, Delete) operations. Especially In any Web Application, you need to repeat the CRUD Operations for every object in your system.

For creating beautiful & responsive UI, I’m using AdminLTE Template. So, In case if you missed the first part of this Tutorial, then you can find here => Getting Started with Web Application using PHP & MySQL | Tutorials

php mysql crud operations

Assuming that you have followed the above tutorial & setup your project, let’s start our CRUD Operations Tutorial using PHP & MySQL.

I’m performing my CRUD Operation on this Database Table. You can use your own Table.

doctors table

Creating Folders

  1. First of all, create a new folder at the root of your project directory as “api”.
  2. Inside “api” folder create three folders as “config”, “objects” & “doctors”.

we’ll keep all Doctor API inside “doctors” folder.

This directory structure will help us to keep our project easy to manage.

Database Connectivity

In your “config” folder inside “api” folder, create a new file there as “database.php” and paste this code there

<?php
class Database{
 
    // specify your own database credentials
    private $host = "localhost";
    private $db_name = "PHPWebAppTutorial";
    private $username = "root";
    private $password = "";
    public $conn;
 
    // get the database connection
    public function getConnection(){
 
        $this->conn = null;
 
        try{
            $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
            $this->conn->exec("set names utf8");
        }catch(PDOException $exception){
            echo "Connection error: " . $exception->getMessage();
        }
 
        return $this->conn;
    }
}
?>

Now, your project structure should look like this.

php mysql crud operation structure

Let’s start from creating API for CRUD operations.

First of all, create a Model of your Doctor.

So, create a new file as “doctor.php” inside “objects” folder & add this Code:

<?php
class Doctor{
 
    // database connection and table name
    private $conn;
    private $table_name = "doctors";
 
    // object properties
    public $id;
    public $name;
    public $email;
    public $password;
    public $phone;
    public $gender;
    public $address;
    public $cnic;
    public $profile_status;
    public $specialist;
    public $created;
 
    // constructor with $db as database connection
    public function __construct($db){
        $this->conn = $db;
    }

    // read all doctors
    function read(){
    
        // select all query
        $query = "SELECT
                    `id`, `name`, `email`, `password`, `phone`, `gender`, `address`, `cnic`, `profile_status`, `specialist`, `created`
                FROM
                    " . $this->table_name . " 
                ORDER BY
                    id DESC";
    
        // prepare query statement
        $stmt = $this->conn->prepare($query);
    
        // execute query
        $stmt->execute();
    
        return $stmt;
    }

    // get single doctor data
    function read_single(){
    
        // select all query
        $query = "SELECT
                    `id`, `name`, `email`, `password`, `phone`, `gender`, `address`, `cnic`, `profile_status`, `specialist`, `created`
                FROM
                    " . $this->table_name . " 
                WHERE
                    id= '".$this->id."'";
    
        // prepare query statement
        $stmt = $this->conn->prepare($query);
    
        // execute query
        $stmt->execute();
        return $stmt;
    }

    // create doctor
    function create(){
    
        if($this->isAlreadyExist()){
            return false;
        }
        
        // query to insert record
        $query = "INSERT INTO
                    " . $this->table_name . "
                SET
                    name=:name, email=:email, password=:password, phone=:phone, gender=:gender, cnic=:cnic, address=:address, profile_status=:profile_status, specialist=:specialist, created=:created";
    
        // prepare query
        $stmt = $this->conn->prepare($query);
    
        // sanitize
        $this->name=htmlspecialchars(strip_tags($this->name));
        $this->email=htmlspecialchars(strip_tags($this->email));
        $this->password=htmlspecialchars(strip_tags($this->password));
        $this->phone=htmlspecialchars(strip_tags($this->phone));
        $this->gender=htmlspecialchars(strip_tags($this->gender));
        $this->cnic=htmlspecialchars(strip_tags($this->cnic));
        $this->address=htmlspecialchars(strip_tags($this->address));
        $this->profile_status=htmlspecialchars(strip_tags($this->profile_status));
        $this->specialist=htmlspecialchars(strip_tags($this->specialist));
        $this->created=htmlspecialchars(strip_tags($this->created));
    
        // bind values
        $stmt->bindParam(":name", $this->name);
        $stmt->bindParam(":email", $this->email);
        $stmt->bindParam(":password", $this->password);
        $stmt->bindParam(":phone", $this->phone);
        $stmt->bindParam(":gender", $this->gender);
        $stmt->bindParam(":cnic", $this->cnic);
        $stmt->bindParam(":address", $this->address);
        $stmt->bindParam(":profile_status", $this->profile_status);
        $stmt->bindParam(":specialist", $this->specialist);
        $stmt->bindParam(":created", $this->created);
    
        // execute query
        if($stmt->execute()){
            $this->id = $this->conn->lastInsertId();
            return true;
        }
    
        return false;
        
    }

    // update doctor 
    function update(){
    
        // query to insert record
        $query = "UPDATE
                    " . $this->table_name . "
                SET
                    name='".$this->name."', email='".$this->email."', password='".$this->password."', phone='".$this->phone."', cnic='".$this->cnic."', address='".$this->address."'
                WHERE
                    id='".$this->id."'";
    
        // prepare query
        $stmt = $this->conn->prepare($query);

        // execute query
        if($stmt->execute()){
            return true;
        }
        return false;
    }

    // delete doctor
    function delete(){
        
        // query to insert record
        $query = "DELETE FROM
                    " . $this->table_name . "
                WHERE
                    id= '".$this->id."'";
        
        // prepare query
        $stmt = $this->conn->prepare($query);

        // execute query
        if($stmt->execute()){
            return true;
        }
        return false;
    }

    function isAlreadyExist(){

        $query = "SELECT *
            FROM
                " . $this->table_name . " 
            WHERE
                email='".$this->email."'";

        // prepare query statement
        $stmt = $this->conn->prepare($query);

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

        if($stmt->rowCount() > 0){
            return true;
        }
        else{
            return false;
        }
    }
}

you can see that we have all CRUD Operations including read_single function for reading a single record from MySQL database table.

Now, we’ll create a separate file for each operation inside “doctor” folder & we’ll call functions from “doctor.php” inside “objects” folder.

Let’s create a file inside “doctor” folder as “create.php” for creating a new doctor & add the below code inside this file.

<?php
 
// get database connection
include_once '../config/database.php';
 
// instantiate doctor object
include_once '../objects/doctor.php';
 
$database = new Database();
$db = $database->getConnection();
 
$doctor = new Doctor($db);
 
// set doctor property values
$doctor->name = $_POST['name'];
$doctor->email = $_POST['email'];
$doctor->password = base64_encode($_POST['password']);
$doctor->phone = $_POST['phone'];
$doctor->gender = isset($_POST['gender']);
$doctor->cnic = $_POST['cnic'];
$doctor->address = $_POST['address'];
$doctor->profile_status = 1;
$doctor->specialist = isset($_POST['specialist']);
$doctor->created = date('Y-m-d H:i:s');
 
// create the doctor
if($doctor->create()){
    $doctor_arr=array(
        "status" => true,
        "message" => "Successfully Signup!",
        "id" => $doctor->id,
        "name" => $doctor->name,
        "email" => $doctor->email,
        "phone" => $doctor->phone,
        "gender" => $doctor->gender,
        "cnic" => $doctor->cnic,
        "address" => $doctor->address,
        "specialist" => $doctor->specialist
    );
}
else{
    $doctor_arr=array(
        "status" => false,
        "message" => "Email already exists!"
    );
}
print_r(json_encode($doctor_arr));
?>

For reading all records from Doctors table create a file as “read.php” & add this code

<?php

// include database and object files
include_once '../config/database.php';
include_once '../objects/doctor.php';
 
// instantiate database and doctor object
$database = new Database();
$db = $database->getConnection();
 
// initialize object
$doctor = new Doctor($db);
 
// query doctor
$stmt = $doctor->read();
$num = $stmt->rowCount();

// check if more than 0 record found
if($num>0){
 
    // doctors array
    $doctors_arr=array();
    $doctors_arr["doctors"]=array();
 
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){

        extract($row);
        $doctor_item=array(
            "id" => $id,
            "name" => $name,
            "email" => $email,
            "password" => $password,
            "phone" => $phone,
            "gender" => $gender,
            "cnic" => $cnic,
            "address" => $address,
            "profile_status" => $profile_status,
            "specialist" => $specialist,
            "created" => $created
        );

        array_push($doctors_arr["doctors"], $doctor_item);
    }
 
    echo json_encode($doctors_arr["doctors"]);
}
else{
    echo json_encode(array());
}
?>

to Update a record, create a file as “update.php” & add the code below

<?php
 
// get database connection
include_once '../config/database.php';
 
// instantiate doctor object
include_once '../objects/doctor.php';
 
$database = new Database();
$db = $database->getConnection();
 
$doctor = new Doctor($db);
 
// set doctor property values
$doctor->id = $_POST['id'];
$doctor->name = $_POST['name'];
$doctor->email = $_POST['email'];
$doctor->password = base64_encode($_POST['password']);
$doctor->phone = $_POST['phone'];
$doctor->cnic = $_POST['cnic'];
$doctor->address = $_POST['address'];
 
// create the doctor
if($doctor->update()){
    $doctor_arr=array(
        "status" => true,
        "message" => "Successfully Updated!"
    );
}
else{
    $doctor_arr=array(
        "status" => false,
        "message" => "Email already exists!"
    );
}
print_r(json_encode($doctor_arr));
?>

& for the last letter of CRUD, which is D.

create another file as “delete.php” & add this code

<?php
 
// get database connection
include_once '../config/database.php';
 
// instantiate doctor object
include_once '../objects/doctor.php';
 
$database = new Database();
$db = $database->getConnection();
 
$doctor = new Doctor($db);
 
// set doctor property values
$doctor->id = $_POST['id'];
 
// remove the doctor
if($doctor->delete()){
    $doctor_arr=array(
        "status" => true,
        "message" => "Successfully Removed!"
    );
}
else{
    $doctor_arr=array(
        "status" => false,
        "message" => "Doctor Cannot be deleted. may be he's assigned to a patient!"
    );
}
print_r(json_encode($doctor_arr));
?>

in the end, we need another method which is to get a single doctor record.

So, create a new file as “read_single.php” & add the below code

<?php

// include database and object files
include_once '../config/database.php';
include_once '../objects/doctor.php';
 
// get database connection
$database = new Database();
$db = $database->getConnection();
 
// prepare doctor object
$doctor = new Doctor($db);

// set ID property of doctor to be edited
$doctor->id = isset($_GET['id']) ? $_GET['id'] : die();

// read the details of doctor to be edited
$stmt = $doctor->read_single();
if($stmt->rowCount() > 0){

    // get retrieved row
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    // create array
    $doctor_arr=array(
        "id" => $row['id'],
        "name" => $row['name'],
        "email" => $row['email'],
        "password" => $row['password'],
        "phone" => $row['phone'],
        "gender" => $row['gender'],
        "cnic" => $row['cnic'],
        "address" => $row['address'],
        "profile_status" => $row['profile_status'],
        "specialist" => $row['specialist'],
        "created" => $row['created']
    );
}

// make it json format
print_r(json_encode($doctor_arr));
?>

After this our “api” directory will look like this

php mysql crud operation structure

Our API for CRUD Operation is ready using PHP & MySQL. Now, we need to create UI using the template we set up in the previous article & then connect our UI with the API we created in this article.

We’ll create UI for our CRUD Operations in the next part of this Article.

Comment If you find any difficulty, I’m available to solve your problems.

Previous Part

Next Part