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;
It is really nice!
ReplyDeleteCan I use another type of input? e.g.: date, datetime, select, checkbox
same question from me. plz reply
Deletefor me it doesnt work I dont know why
ReplyDeleteThx for very nice tutorial. How can i use "onkeydown with keycode=40" to setfocus to one row down, to the same field when pressing the "down-arrow" on keyboard.
ReplyDeleteI have tried with no success
$(document).on('keydown','#user_data > tbody tr .num',function (e) {
if (e.which === 40) {
$(this).parents("tr").next("tr").find('.num').focus();
}
});
Please be so kind to assist as the inline edit blocks me
Very nice tutorial. How can one adapt it to navigate with arrow keys down ie keycode=40 Pls be so kind to assist
ReplyDeleteChamp!!! (y)
ReplyDeleteGreat and very smooth. Best inline editor I have found and you also inc add/delete records with a search to boot. Thanks!!!
ReplyDeleteHi, this script is fine, but it does not work.
ReplyDeleteThank you very much
I have a question, what if someone submits a form like this using script
ReplyDeletef o r m action="{ YPUR URL}delete.php" method="POST" id="myForm">
i n p u t type="text" name="id" value="45" />
/f o r m>
Is there any way to secure these CRUD operations using AJAX
i have asked in previous post also, now if i run this script then i can perform the CRUD on ur site. how to make it Secure, do you have any suggestions, here is the code which i used to add.
ReplyDeleteform action="http://www.itidabhoi.org/demo/live-datatables-insert-update-delete/insert.php" method="POST" id="myForm">
input type="text" name="first_name" value="BHANU" >
input type="text" name="last_name" value="DHUNGANA" >
input type="submit" value="submit" name="">
f o r m>
i tried ur code... but the insertion is not working
ReplyDeletegreat sir
ReplyDeletegreat................
ReplyDeletehttp://prntscr.com/hn1zaq
ReplyDeleteThank you, code was helpful. But one problem, am not getting the: js,ajax,jq to work locally, only when connected online. Plz help.
ReplyDeletethe live add function is not working
ReplyDeleteThank you sir for your great help
ReplyDeleteThe update button is missing. I guess that is homework!
ReplyDeleteCordial regards, I need a field to be date type, you could help me with this information. Thank you
ReplyDeletehow i can create row number?
ReplyDeleteRealy nice example. thank you!!
ReplyDeletehow to make validations?
ReplyDelete
ReplyDeleteHello good afternoon. Could you please help me with your post? As I would do to register images. I will be very grateful to myself on this matter
ReplyDeleteHello good afternoon. Could you please help me with your post? As I would do to register images. I will be very grateful to me in this question and a button next to the "delete" that can be visualized the id of the clicked line? Please helpe-me
kkk
ReplyDeleteHow to add PDF/Excel export button for print purposes?
ReplyDeletethnk u guys for these amazing topis are very intuitive
ReplyDeletebut cant u try to use real human voice in tutorials cause they are awesome and help alot of people but that reader sometime misspell word and i like to full understand thing and how to are used.
Thanks anyways great material great topics awesome coding style
#perfect
what if you do want to leave fields blank
ReplyDeleteThis article is very useful for newbies web developers. Can you add some option into this code. such as - data table excel/pdf export button and manipulate drop down data one or multiple rows from another table.
ReplyDeleteHow can I stop the table from refreshing after updating I.E {Show 100 entries goes back to show 10entries}...
ReplyDeleteGreat instruction, but one problem, with a table with more than one page display of data, it would be nice upon update, insert, or delete to return to the page of which the update, insert, or delete was performed. The function fetch_data redisplays the table always only the first page of date records, not the page you were working on in this case.
ReplyDeleteDataTables warning: table id=user_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1 .........it says when i load
ReplyDeleteyou can enter a field checkbox?
ReplyDeletevery informative
ReplyDeletehow to get edit cell on double click .. please help
ReplyDeletehow to get edit cell on double click instead of single click .. please do help
ReplyDelete$('#user_data').on( 'dbclick', 'tbody td', function () {
ReplyDeletedataTable.cell( this ).edit();
} );
tried this for double click but not working..
update no work
ReplyDeletegood tuto
ReplyDeleteHola, como podria hacer una caja con opcion multiple o combobox??
ReplyDeleteAwesome bro,, Thanks so much..
ReplyDeletehello there,
ReplyDeletenice tutorial!
i was able to create the datatable and it works with my database.
i have a question: how could i add a kind of validation form on the line for update the database? they are not input field? thx
$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';
ginzu
nice tut,
ReplyDeletei was able to create the datatable and interact with my db (insert, update, delete)..
but how could i add a validation form when i want to update the fields?
these are not input field. do you have a tips?
$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';
thanks in advance
nice
ReplyDeleteThis is great, the only question I have is how do you edit(update) a blank column
ReplyDeleteupdate, delete and insert is not working for me just shows // // in message place, please help me
ReplyDeletei am also facing the same problem,could u solve?
Deletecan i use select box in this code???
ReplyDeleteeverything works just fine but i have a problem with delete button..when i delete, nothing happend..help me please .. my email = blazeartz@gmail.com
ReplyDeleteCan we make auto refresh this table after every 10 or more seconds?
ReplyDeletemeta refresh
Deleteafter add button the columns appearing are non editable
ReplyDeletecontenteditable is not working
ReplyDeletewhen I add the fourth bar, an error appears. what could it be?
ReplyDeleteDataTables warning: table id=user_data - Invalid JSON response. ....what's the issue...pls explain
ReplyDeleteDataTables warning: table id=user_data - Invalid JSON response. ....what's the issue...pls explain
ReplyDeletecontenteditable class="update" style="min-height:15px"
ReplyDeletehttps://stackoverflow.com/questions/24793551/firefox-does-not-allow-blank-contenteditable-area
Great. Thanks!!
ReplyDeletei dont know how to add more columns i tried and it ended up crashing the whole thing i wanted to have at least 13 columns but didnt know what to do
ReplyDeletePlease suggest same thing with laravel .
ReplyDeletethis same thig how can use in codeigniter with serch and pdf
ReplyDeleteundefined index length and start
ReplyDeleteWonderful work, congratulation!
ReplyDeletePlease it is possible show how create in this a combobox column?
Can do Live demo
ReplyDeletecontenteditabel is not working .can some one please help
ReplyDeletebro your demo site is hijacked
ReplyDeletebro your demo site is hijacked
ReplyDeleteHello, it is possible to give this source code with connection « new PDO » ???
ReplyDeletehow to insert date picker in every column in this code? i try many times but error appears.
ReplyDeleteAn example about working:
ReplyDeleteWhe have a table with 10 fields (columns) for every record (rows). We add 200 rows. Script shows, for example, the 50 last indexed rows. So, it paginates 8 pages. Now I need update a row on page 7 and I need update 5 of his 10 fields. If I modify a field and then click on second field it updates database and go back to first paginated page. So I need to repeat 4 times more this process (go to page 7, locate register row and modify next field) to update my 5 fields of this row. It is not comfortable or operational. How may I modify code to allow me to change all fields of a register without going back to first paginate page every time? (for example, adding a button next delete button to edit all row, not only a field)
Thanks
thank you !! it's working fine
ReplyDeleteDataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7
ReplyDeleteThis is a problem how can i solve it , please?
you give great example but i want some changes if possible than help me correct my code
ReplyDeletei want add one more column in database like code (it have numeric value like 123,123,231)
problem is there when i fetch all record my where condition was not work if possible than help me
Pretty cool I was inspired by this page and created a similar example here:
ReplyDeletehttp://www.abrandao.com/2019/12/saving-contenteditable-html-using-php/
I decided just to write to an html file and parse the corresponding div. code is up on Gtihub
Nice work, Everything doing fine. Thanks a lot my brother
ReplyDeleteYour fetch.php code is a mess.
ReplyDeleteits not working in firefox and edge edit option textbox not displaying.cant predict its in an editmode. chrome it is fine
ReplyDeletehey when i am add record in table redirect on
ReplyDeletehttps://www.songlyricst.com
Update Panel is not working please help me and it is urgent!!!
ReplyDeleteDataTables warning: table id=user_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
ReplyDeleteplese
Good Job... very helpful full function
ReplyDeleteHWD GROUP OF SOFTWARE
Great work, but you have some really bad issues with your code.
ReplyDeleteFor example:
$(document).on('click', '#insert', function(){
var first_name = $('#data1').text();
var last_name = $('#data2').text();
This only works if you're adding one row at the time, because it will always get the text() of the first row, you're trying to add, since you're using ID.
merci beaucoup pour ce tuto formidable
ReplyDeletei keep getting this message:
ReplyDeleteDataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7
Nice project!
ReplyDeleteIf I update a field on 3rd page of paginate then returns to first page. How can I remain on 3rd page after it has changed.
For example, I use 10 fields for every register. I search a register and then I'd like to change 3 fields of this. If I change one of them, it returns to first page of paginate and I must search again to change the second field. This is a litte bit buggy.
Thanks for your script.
error
ReplyDeleteFrist Name
frist=first
the code runs for me, except the update part. i already checked several times, i think there's no missing letters and incorrect words....but why not updating?
ReplyDeleteis the the column_name different from column at update section?
ReplyDeletewhy update feature not working? the update.php is looking for defined id...every time i clicked to update the data.
ReplyDeletei am very happy this example is sussessfully run
ReplyDeleteDataTables warning: table id=user_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
ReplyDeleteExcellent! It works for me perfectly.
ReplyDeleteExcelente! Me funciona a la perfección.
excellent work.
ReplyDeleteIs there any way out that I can add class or id attribute to eac table row that is fetched through databse.
Great!!!!
ReplyDeletehow to fetch more than 500 rows and display it on the datatabe using php and mysql
ReplyDeletecan we add data at a particular row , means particular index ? like google excel sheet ?
ReplyDelete