In this post we have made discussion on Live JQuery Datatables records insert, update, delete by using Ajax with PHP. In one of my previous famous post in which we have already seen how to live add edit delete records from simple html table by using PHP with Ajax Mysql. But now we have use Jquery Datatables plugin for display records in tabular format on web page. So in this post we will learn how to make Datatables for Live Ajax PHP Crud operation like create, read, update and delete without refresh of web page. Because in Datatables add or editing of live table records is very complex process.
Read Also
We can make html table editable by using contenteditable attribute putting in table data tag. But in Datatables we can't use contenteditable attribute directly into td tag. Because this plugin has generate table code automatically we just want to pass data to Datatable() method in json format. Other code it has been generated. So, therefore we can't directly use contenteditable attribute directly in table. By using this attribute can convert tag to become editable, so we can make edit data of that tag very easily. Now here we want to make editable table data tag content, so we have define one division tag for this for display data from database to web page in Datatables and then after we have put contenteditable attribute define in that div tag and that html pass to plugin in json format. So this way we have make this plugin table data editable and we can easily edit content of table. This way we can define this attribute for that table column in which we want edit live table data.
After make Datatables record editable, now we want to add blank table row at the start of table. So we have make html code for generate blank table row. In this code we have also is that attribute, so we can write something in that table column with insert button. By using jquery prepend() method we have append this blank table row at the start of table and for add new records we can type in that blank table and click on Insert button. So this way we can insert live records in this Datatables by using Ajax with PHP script. Here It has been get the help of different jQuery event which are generates Ajax request and send that AJAX call to the server script with crud database action. Here we have make simple single page application by using Jquery Datatables plugin with Ajax PHP and Mysql. In which we have perform all operation on live table without going to other page or in pop modal but we have insert, update and delete live records by using PHP with Ajax.
Source Code
<html>
<head>
<title>Live Add Edit Delete Datatables Records using PHP Ajax</title>
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.15/js/dataTables.bootstrap.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/css/bootstrap-datepicker.css" />
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/js/bootstrap-datepicker.js"></script>
<style>
body
{
margin:0;
padding:0;
background-color:#f1f1f1;
}
.box
{
width:1270px;
padding:20px;
background-color:#fff;
border:1px solid #ccc;
border-radius:5px;
margin-top:25px;
box-sizing:border-box;
}
</style>
</head>
<body>
<div class="container box">
<h1 align="center">Live Add Edit Delete Datatables Records using PHP Ajax</h1>
<br />
<div class="table-responsive">
<br />
<div align="right">
<button type="button" name="add" id="add" class="btn btn-info">Add</button>
</div>
<br />
<div id="alert_message"></div>
<table id="user_data" class="table table-bordered table-striped">
<thead>
<tr>
<th>Frist Name</th>
<th>Last Name</th>
<th></th>
</tr>
</thead>
</table>
</div>
</div>
</body>
</html>
<script type="text/javascript" language="javascript" >
$(document).ready(function(){
fetch_data();
function fetch_data()
{
var dataTable = $('#user_data').DataTable({
"processing" : true,
"serverSide" : true,
"order" : [],
"ajax" : {
url:"fetch.php",
type:"POST"
}
});
}
function update_data(id, column_name, value)
{
$.ajax({
url:"update.php",
method:"POST",
data:{id:id, column_name:column_name, value:value},
success:function(data)
{
$('#alert_message').html('<div class="alert alert-success">'+data+'</div>');
$('#user_data').DataTable().destroy();
fetch_data();
}
});
setInterval(function(){
$('#alert_message').html('');
}, 5000);
}
$(document).on('blur', '.update', function(){
var id = $(this).data("id");
var column_name = $(this).data("column");
var value = $(this).text();
update_data(id, column_name, value);
});
$('#add').click(function(){
var html = '<tr>';
html += '<td contenteditable id="data1"></td>';
html += '<td contenteditable id="data2"></td>';
html += '<td><button type="button" name="insert" id="insert" class="btn btn-success btn-xs">Insert</button></td>';
html += '</tr>';
$('#user_data tbody').prepend(html);
});
$(document).on('click', '#insert', function(){
var first_name = $('#data1').text();
var last_name = $('#data2').text();
if(first_name != '' && last_name != '')
{
$.ajax({
url:"insert.php",
method:"POST",
data:{first_name:first_name, last_name:last_name},
success:function(data)
{
$('#alert_message').html('<div class="alert alert-success">'+data+'</div>');
$('#user_data').DataTable().destroy();
fetch_data();
}
});
setInterval(function(){
$('#alert_message').html('');
}, 5000);
}
else
{
alert("Both Fields is required");
}
});
$(document).on('click', '.delete', function(){
var id = $(this).attr("id");
if(confirm("Are you sure you want to remove this?"))
{
$.ajax({
url:"delete.php",
method:"POST",
data:{id:id},
success:function(data){
$('#alert_message').html('<div class="alert alert-success">'+data+'</div>');
$('#user_data').DataTable().destroy();
fetch_data();
}
});
setInterval(function(){
$('#alert_message').html('');
}, 5000);
}
});
});
</script>
insert.php
<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
if(isset($_POST["first_name"], $_POST["last_name"]))
{
$first_name = mysqli_real_escape_string($connect, $_POST["first_name"]);
$last_name = mysqli_real_escape_string($connect, $_POST["last_name"]);
$query = "INSERT INTO user(first_name, last_name) VALUES('$first_name', '$last_name')";
if(mysqli_query($connect, $query))
{
echo 'Data Inserted';
}
}
?>
update.php
<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
if(isset($_POST["id"]))
{
$value = mysqli_real_escape_string($connect, $_POST["value"]);
$query = "UPDATE user SET ".$_POST["column_name"]."='".$value."' WHERE id = '".$_POST["id"]."'";
if(mysqli_query($connect, $query))
{
echo 'Data Updated';
}
}
?>
delete.php
<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
if(isset($_POST["id"]))
{
$query = "DELETE FROM user WHERE id = '".$_POST["id"]."'";
if(mysqli_query($connect, $query))
{
echo 'Data Deleted';
}
}
?>
fetch.php
<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$columns = array('first_name', 'last_name');
$query = "SELECT * FROM user ";
if(isset($_POST["search"]["value"]))
{
$query .= '
WHERE first_name LIKE "%'.$_POST["search"]["value"].'%"
OR last_name LIKE "%'.$_POST["search"]["value"].'%"
';
}
if(isset($_POST["order"]))
{
$query .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].'
';
}
else
{
$query .= 'ORDER BY id DESC ';
}
$query1 = '';
if($_POST["length"] != -1)
{
$query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));
$result = mysqli_query($connect, $query . $query1);
$data = array();
while($row = mysqli_fetch_array($result))
{
$sub_array = array();
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="first_name">' . $row["first_name"] . '</div>';
$sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="last_name">' . $row["last_name"] . '</div>';
$sub_array[] = '<button type="button" name="delete" class="btn btn-danger btn-xs delete" id="'.$row["id"].'">Delete</button>';
$data[] = $sub_array;
}
function get_all_data($connect)
{
$query = "SELECT * FROM user";
$result = mysqli_query($connect, $query);
return mysqli_num_rows($result);
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => get_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>
Database
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `user`
--
CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL,
`first_name` varchar(200) NOT NULL,
`last_name` varchar(200) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `user`
--
INSERT INTO `user` (`id`, `first_name`, `last_name`) VALUES
(1, 'John', 'Smith'),
(5, 'Peterson', 'Parker'),
(7, 'Rock', 'Madison'),
(8, 'Titan', 'Edge');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `user`
--
ALTER TABLE `user`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=9;
