PHP Datatables CRUD Example

XpertPhp

07-Aug-2022

514

In this article, we will explain to you how to create php datatables crud example. here we will give you a simple example of php datatables crud example.

Whenever you start PHP crud that time will have compulsorily required database connectivity using PHP.

We will go through the steps of a creating PHP CRUD operation post. We want to demonstrate how to connect PHP with MySQL, We hope you can learn something new from this tutorial. in our post on how to connect PHP to MySQL, create a new record, fetch a record, update a record and delete the record.

Create MySQL Database Table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `email` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Connect to Database

Create a new connect.php file. The code below is to provide how to connect with the database.
The following code is used to connect MySQL to PHP. It requires a hostname, database username, database password, and database name.
connect.php

<?php

 $hostname="localhost";
 $username="root";
 $password="";
 $database="demo";

 $conn = mysqli_connect($hostname,$username,$password,$database); 
 
?>

index.php

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>PHP Datatables CRUD Example - XpertPhp</title>

<!-- DataTables CSS library -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css"/>

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>

<!-- DataTables JS library -->
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>

    <style type="text/css">
        .bs-example{
            margin: 20px;
        }
    </style>
</head>
<body>
    <div class="bs-example">
        <div class="container">
            <div class="row">
                <div class="col-md-12">
                    <div class="page-header clearfix">
                        <h2 class="float-left">Users List</h2>
                        <a href="javascript:void(0)" class="btn btn-primary float-right add-model"> Add User </a>
                    </div>
                     
                   <table id="usersListTable" class="display" style="width:100%">
                        <thead>
                            <tr>
                                <th>Name</th>
                                <th>Email</th>
                                <th>Action</th>
                            </tr>
                        </thead>
                        <tfoot>
                            <tr>
                                <th>Name</th>
                                <th>Email</th>
                                <th>Action</th>
                            </tr>
                        </tfoot>
                  </table>
                </div>
            </div>        
        </div>
    </div>
</body>

<div class="modal fade" id="edit-modal" aria-hidden="true">
  <div class="modal-dialog">
      <div class="modal-content">
          <div class="modal-header">
              <h4 class="modal-title" id="userCrudModal">Update User</h4>
          </div>
          <div class="modal-body">
              <form id="update-form" name="update-form" class="form-horizontal">
                 <input type="hidden" name="id" id="id">
                  <input type="hidden" class="form-control" id="mode" name="mode" value="update">
                  <div class="form-group">
                      <label for="name" class="col-sm-2 control-label">Name</label>
                      <div class="col-sm-12">
                          <input type="text" class="form-control" id="name" name="name" placeholder="Enter Name" value="" maxlength="50" required="">
                      </div>
                  </div>

                  <div class="form-group">
                      <label class="col-sm-2 control-label">Email</label>
                      <div class="col-sm-12">
                          <input type="email" class="form-control" id="email" name="email" placeholder="Enter Email" value="" required="">
                      </div>
                  </div>
                  <div class="col-sm-offset-2 col-sm-10">
                   <button type="submit" class="btn btn-primary" id="btn-save" value="create">Update</button>
                  </div>
              </form>
          </div>
          <div class="modal-footer">
             
          </div>
      </div>
  </div>
</div>

<div class="modal fade" id="add-modal" aria-hidden="true">
  <div class="modal-dialog">
      <div class="modal-content">
          <div class="modal-header">
              <h4 class="modal-title" id="userCrudModal">Add User</h4>
          </div>
          <div class="modal-body">
              <form id="add-form" name="add-form" class="form-horizontal">
                 <input type="hidden" class="form-control" id="mode" name="mode" value="add">
                  <div class="form-group">
                      <label for="name" class="col-sm-2 control-label">Name</label>
                      <div class="col-sm-12">
                          <input type="text" class="form-control" id="name" name="name" placeholder="Enter Name" value="" maxlength="50" required="">
                      </div>
                  </div>

                  <div class="form-group">
                      <label class="col-sm-2 control-label">Email</label>
                      <div class="col-sm-12">
                          <input type="email" class="form-control" id="email" name="email" placeholder="Enter Email" value="" required="">
                      </div>
                  </div>
                  <div class="col-sm-offset-2 col-sm-10">
                   <button type="submit" class="btn btn-primary" id="btn-save" value="create">Add</button>
                  </div>
              </form>
          </div>
          <div class="modal-footer">
             
          </div>
      </div>
  </div>
</div>

<script>
$(document).ready(function(){
    $('#usersListTable').DataTable({
        "processing": true,
        "serverSide": true,
        "order": [],
        "ajax": "fetch_user.php"
    });
});


  /*  add user model */
$('.add-model').click(function () {
    $('#add-modal').modal('show');
});

// add form submit
$('#add-form').submit(function(e){

    e.preventDefault();

    // ajax
    $.ajax({
        url:"insert_user.php",
        type: "POST",
        data: $(this).serialize(), // get all form field value in serialize form
        success: function(){
            var oTable = $('#usersListTable').dataTable(); 
            oTable.fnDraw(false);
            $('#add-modal').modal('hide');
            $('#add-form').trigger("reset");
        }
    });
});  

/* edit user function */
$('body').on('click', '.btn-edit', function () {
    var id = $(this).data('id');
     $.ajax({
        url:"single_user.php",
        type: "POST",
        data: {id: id},
        dataType : 'json',
        success: function(result){
          $('#id').val(result.data.id);
          $('#name').val(result.data.name);
          $('#email').val(result.data.email);
          $('#edit-modal').modal('show');
        }
    });
});

// add form submit
$('#update-form').submit(function(e){

    e.preventDefault();
       
    // ajax
    $.ajax({
        url:"update_user.php",
        type: "POST",
        data: $(this).serialize(), // get all form field value in serialize form
        success: function(){
            var oTable = $('#usersListTable').dataTable(); 
            oTable.fnDraw(false);
            $('#edit-modal').modal('hide');
            $('#update-form').trigger("reset");
        }
    });
});  

/* DELETE FUNCTION */
$('body').on('click', '.btn-delete', function () {
    var id = $(this).data('id');
    if (confirm("Are You sure want to delete !")) {
     $.ajax({
        url:"delete_user.php",
        type: "POST",
        data: {id: id},
        dataType : 'json',
        success: function(result){
            var oTable = $('#usersListTable').dataTable(); 
            oTable.fnDraw(false);
        }
     });
    } 
    return false;
});

</script>
</html>

Fetch all Records in MySQL Databases

Create a new PHP file fetch_user.php in this file in add the below code.
The following code shows how to fetch all the records from the database in the index file.
list_user
fetch_user.php

<?php
require_once('connect.php');
extract($_GET);
$totalCount = $conn->query("SELECT * FROM `users` ")->num_rows;
$search_where = "";
if(!empty($search)){
    $search_where = " where ";
    $search_where .= " name LIKE '%{$search['value']}%' ";
    $search_where .= " OR email LIKE '%{$search['value']}%' ";
}
$columns_arr = array("id","name","email");
$query = "SELECT * FROM `users` {$search_where}";
if(isset($order[0]['dir']))
{
	$query .= " ORDER BY {$columns_arr[$order[0]['column']]} {$order[0]['dir']}";
}
else
{
	$query .= " ORDER BY id DESC";
}
if($length != -1)
{
	$query .= ' LIMIT ' . $start . ', ' . $length;
}
$query = $conn->query($query);
$recordsFilterCount = $conn->query("SELECT * FROM `users` {$search_where} ")->num_rows;



$recordsTotal= $totalCount;
$recordsFiltered= $recordsFilterCount;
$data = array();
while($row = $query->fetch_assoc()){
	$sub_array = array();
	$sub_array[] = $row['name'];
	$sub_array[] = $row['email'];
	$sub_array[] = '<button type="button" data-id="'.$row["id"].'" class="btn btn-warning btn-sm btn-edit">Edit</button>&nbsp;<button type="button" class="btn btn-danger btn-sm btn-delete" data-id="'.$row["id"].'">Delete</button>';
	$data[] = $sub_array;
}

echo json_encode(array('draw'=>$draw,'recordsTotal'=>$recordsTotal,'recordsFiltered'=>$recordsFiltered,'data'=>$data));

?>

Creating New Record in MySQL Database

add_user.php
Create a PHP file add_user.php in this file in add the below code.
The code below in the INSERT query creates a new record to the database table. This HTML form contains input fields to enter user data to be inserted into the table.

add_user

<?php
    require_once('connect.php');

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

    $sql = "INSERT INTO users(name,email) VALUES ('$name','$email')";
    $result = $conn->query($sql);
	
	if($result){
            $resp['status'] = 'success';
	}else{
	    $resp['status'] = 'failed';
	    $resp['msg'] = 'An error occured while saving the data. Error: '.$conn->error;
	}
    echo json_encode($resp);
    
?>

Php MySQL Update Record

Create a new update_user.php file in this file to add the below code.
First, we will fetch a particular data record in the update form. On submitting edited user information we form an update query to edit the record with the reference of its edit_id.

update_user

update_user.php

<?php
    require_once('connect.php');

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

    $sql = "UPDATE `users` set `name` = '{$name}', `email` = '{$email}' where id = '{$id}'";
    $result = $conn->query($sql);
	
	if($result){
            $resp['status'] = 'success';
	}else{
	    $resp['status'] = 'failed';
	    $resp['msg'] = 'An error occured while saving the data. Error: '.$conn->error;
	}
    echo json_encode($resp);
    
?>

single_user.php

<?php
require_once('connect.php');

$id = $_POST['id'];

$result = $conn->query("SELECT * FROM `users` where id = '{$id}'");
if($result){
    $resp['status'] = 'success';
    $resp['data'] = $result->fetch_assoc();
}else{
    $resp['status'] = 'success';
    $resp['error'] = 'An error occured while fetching the data. Error: '.$conn->error;
}
echo json_encode($resp);

?>

Delete Record from MySQL Database

Create a new delete_user.php file in this file to add the below code.
The below code is used to delete a particular record from the database.

<?php
require_once('connect.php');

$id = $_POST['id'];

$result = $conn->query("delete FROM `users` where id = '{$id}'");
if($result){
    $resp['status'] = 'success';
}else{
    $resp['status'] = 'success';
    $resp['error'] = 'An error occured while fetching the data. Error: '.$conn->error;
}
echo json_encode($resp);

?>

We think would you like this article, so you can click on the “Show Demo” button and you can see this demo article.

PHP Datatables CRUD Example

Create MySQL Database Table

Connect to Database

Fetch all Records in MySQL Databases

Creating New Record in MySQL Database

Php MySQL Update Record

Delete Record from MySQL Database

Search

Tools

Category

Latest Post

Popular Post

Connect With Facebook