In this post we have made a tutorial on inserting, updating, deleting notes Live using JQuery Datatables and Ajax with PHP. But now we have used the Jquery Datatables plugin to display records in table format on web pages. So in this post we will learn how to make Crud Live Ajax like adding records, reading records, updating and deleting records without refreshing web pages. Why use Datatables for PHP operations? Because in Datatables adding or editing notes on direct tables is a very complex or fast process.
We can create html tables that can be edited using the content edit table attribute to place data tags in the table. But in Datatables we cannot use the table edit content attribute directly to the tag td. Because this plugin (datatables) has generated table code automatically, this post only wants to pass data to the Datatable () method in json format. Another code has been generated. So, therefore we cannot directly use the content edit table attribute directly in the table. Using this attribute can convert tags to editable, so we can make editing data from that tag very easily. Now here we want to create editable table data tag content, so we have set one division tag for this to display data from the database to the webpage in Datatables and then after we have put the contenteditable attribute defined in the div tag and the html sent to the plugin in json format. So in this way we have made the table data of this plugin editable and we can easily edit the table content. In this way we can define this attribute for the table column where we want to edit table data directly.
After making the Datatables data editable, we now want to add an empty table row at the beginning of the table. So we have created an html code to produce an empty table row. In this code we also have that attribute, so we can write something in that table column with the insert button. Using the jquery prepend () method we have added this empty table row at the beginning of the table and to add a new record we can type the empty table and click the Insert button. So in this way we can insert records directly into these Datatables by using Ajax with PHP scripts. Here you get help from various jQuery events that generate Ajax requests and send AJAX calls to Side server scripts with database actions. Here we have created a simple single page application using the Jquery Datatables plugin with Ajax PHP and Mysql. Where we have done all operations on the table directly without going to another page or in modal pop-up but we can insert, update and delete records directly using PHP with Ajax.
ONLINE DEMO TUTORIAL VIDEO
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;
how to display image in fetch.php
ReplyDeleteyes
Delete