PHP Datatables CRUD Example

August 7, 2022 XpertPhp

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 ;

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); 
 
?>

<?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>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

<!DOCTYPE html>

<head>

<title>PHP Datatables CRUD Example - XpertPhp</title>

.bs-example{

margin: 20px;

}

</style>

</head>

<body>

<h2 class="float-left">Users List</h2>

</div>

<thead>

<tr>

<th>Email</th>

<th>Action</th>

</tr>

</thead>

<tfoot>

<tr>

<th>Email</th>

<th>Action</th>

</tr>

</tfoot>

</table>

</div>

</body>

<h4 class="modal-title" id="userCrudModal">Update User</h4>

</div>

</div>

<label class="col-sm-2 control-label">Email</label>

</div>

<button type="submit" class="btn btn-primary" id="btn-save" value="create">Update</button>

</div>

</form>

</div>

</div>

</div>

</div>

<label class="col-sm-2 control-label">Email</label>

</div>

</div>

</form>

</div>

</div>

$(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.

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));

?>

<?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> <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.

<?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

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.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);
    
?>

<?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);

?>

<?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);

?>