In our previous tutorial, we have explained how to Create Live Editable Table with jQuery, PHP and MySQL. In this tutorial, we will explain How To Implement Live Add Edit Delete Datatables Records with Ajax, PHP & MySQL.
DataTables is a jQuery JavaScript library to convert simple HTML table to dynamic feature rich table.
The jQuery DataTables are very user friendly to list records with live add, edit, delete records without page refresh. Due to this, DataTables used widely in web application to list records.
So if you’re thinking to use jQuery DataTables in your project, then its very easy. You can easily implement jQuery DataTables in your project with PHP and Ajax.
In this tutorial you will learn how to implement Live Add, Edit and Delete DataTables Records with Ajax PHP and MySQL.
Also, read:
We will cover this tutorial in easy steps to create live example to jQuery DataTables to list records with live add, edit and delete record functionality. You can also download complete project. The download link is located at the end of tutorial.
As we will cover this tutorial with live example to Live Add Edit Delete DataTables Records with Ajax, PHP & MySQL, so the major files for this example is following.
- live-add-edit-delete-datatables-php-mysql-demo
- config
- Database.php
- Class
- Records.php
- js
- ajax.js
- index.php
- ajax_action.php
- config
Step1: Create MySQL Database Tables
First we will create MySQL database tables live_records to add, edit and delete records.
CREATE TABLE `live_records` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `skills` varchar(255) NOT NULL, `address` varchar(255) NOT NULL, `designation` varchar(255) NOT NULL, `age` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `live_records` ADD PRIMARY KEY (`id`); ALTER TABLE `live_records` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
Step2: List Records in DataTables
In index.php file, we will create Table to list records using jQuery DataTables.
<table id="recordListing" class="table table-bordered table-striped"> <thead> <tr> <th>#</th> <th>Name</th> <th>Age</th> <th>Skills</th> <th>Address</th> <th>Designation</th> <th></th> <th></th> </tr> </thead> </table>
We will initialize jQuery DataTables in ajax.js file and make ajax request to action listRecords to make server side request to fetch records to load in DataTables.
var dataRecords = $('#recordListing').DataTable({
"lengthChange": false,
"processing":true,
"serverSide":true,
'serverMethod': 'post',
"order":[],
"ajax":{
url:"ajax_action.php",
type:"POST",
data:{action:'listRecords'},
dataType:"json"
},
"columnDefs":[
{
"targets":[0, 6, 7],
"orderable":false,
},
],
"pageLength": 10
});
We will call method listRecords() on action listRecords to list records.
$record = new Records();
if(!empty($_POST['action']) && $_POST['action'] == 'listRecords') {
$record->listRecords();
}
We will create method listRecords() in class Records.php to fetch records from MySQL database and return as JSON data.
public function listRecords(){
$sqlQuery = "SELECT * FROM ".$this->recordsTable." ";
if(!empty($_POST["search"]["value"])){
$sqlQuery .= 'where(id LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= ' OR name LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= ' OR designation LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= ' OR address LIKE "%'.$_POST["search"]["value"].'%" ';
$sqlQuery .= ' OR skills LIKE "%'.$_POST["search"]["value"].'%") ';
}
if(!empty($_POST["order"])){
$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
} else {
$sqlQuery .= 'ORDER BY id DESC ';
}
if($_POST["length"] != -1){
$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
$result = $stmt->get_result();
$stmtTotal = $this->conn->prepare("SELECT * FROM ".$this->recordsTable);
$stmtTotal->execute();
$allResult = $stmtTotal->get_result();
$allRecords = $allResult->num_rows;
$displayRecords = $result->num_rows;
$records = array();
while ($record = $result->fetch_assoc()) {
$rows = array();
$rows[] = $record['id'];
$rows[] = ucfirst($record['name']);
$rows[] = $record['age'];
$rows[] = $record['skills'];
$rows[] = $record['address'];
$rows[] = $record['designation'];
$rows[] = '<button type="button" name="update" id="'.$record["id"].'"
class="btn btn-warning btn-xs update">Update</button>';
$rows[] = '<button type="button" name="delete" id="'.$record["id"].'"
class="btn btn-danger btn-xs delete" >Delete</button>';
$records[] = $rows;
}
$output = array(
"draw" => intval($_POST["draw"]),
"iTotalRecords" => $displayRecords,
"iTotalDisplayRecords" => $allRecords,
"data" => $records
);
echo json_encode($output);
}
Step3: Handle Add New Record
In index.php file, we will create Bootstrap modal to add new records to jQuery DataTables.
<div id="recordModal" class="modal fade"> <div class="modal-dialog"> <form method="post" id="recordForm"> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal">×</button> <h4 class="modal-title"><i class="fa fa-plus"></i> Add Record</h4> </div> <div class="modal-body"> <div class="form-group" <label for="name" class="control-label">Name</label> <input type="text" class="form-control" id="name" name="name" placeholder="Name" required> </div> <div class="form-group"> <label for="age" class="control-label">Age</label> <input type="number" class="form-control" id="age" name="age" placeholder="Age"> </div> <div class="form-group"> <label for="lastname" class="control-label">Skills</label> <input type="text" class="form-control" id="skills" name="skills" placeholder="Skills" required> </div> <div class="form-group"> <label for="address" class="control-label">Address</label> <textarea class="form-control" rows="5" id="address" name="address"></textarea> </div> <div class="form-group"> <label for="lastname" class="control-label">Designation</label> <input type="text" class="form-control" id="designation" name="designation" placeholder="Designation"> </div> </div> <div class="modal-footer"> <input type="hidden" name="id" id="id" /> <input type="hidden" name="action" id="action" value="" /> <input type="submit" name="save" id="save" class="btn btn-info" value="Save" /> <button type="button" class="btn btn-default" data-dismiss="modal">Close</button> </div> </div> </form> </div> </div>
We will handle modal form submit using jQuery and make Ajax request with action addRecord to add new records.
$("#recordModal").on('submit','#recordForm', function(event){
event.preventDefault();
$('#save').attr('disabled','disabled');
var formData = $(this).serialize();
$.ajax({
url:"ajax_action.php",
method:"POST",
data:formData,
success:function(data){
$('#recordForm')[0].reset();
$('#recordModal').modal('hide');
$('#save').attr('disabled', false);
dataRecords.ajax.reload();
}
})
});
We will call method addRecord() on action addRecord to add new records.
$database = new Database();
$db = $database->getConnection();
$record = new Records($db);
if(!empty($_POST['action']) && $_POST['action'] == 'addRecord') {
$record->name = $_POST["name"];
$record->age = $_POST["age"];
$record->skills = $_POST["skills"];
$record->address = $_POST["address"];
$record->designation = $_POST["designation"];
$record->addRecord();
}
We will create method addRecord() in class Records.php to add new records into MySQL database.
public function addRecord(){
if($this->name) {
$stmt = $this->conn->prepare("
INSERT INTO ".$this->recordsTable."(`name`, `age`, `skills`, `address`, `designation`)
VALUES(?,?,?,?,?)");
$this->name = htmlspecialchars(strip_tags($this->name));
$this->age = htmlspecialchars(strip_tags($this->age));
$this->skills = htmlspecialchars(strip_tags($this->skills));
$this->address = htmlspecialchars(strip_tags($this->address));
$this->designation = htmlspecialchars(strip_tags($this->designation));
$stmt->bind_param("sisss", $this->name, $this->age, $this->skills,
$this->address, $this->designation);
if($stmt->execute()){
return true;
}
}
}
Step4: Handle Update Record
We will handle records update functionality by populating records values to update modal form by make Ajax request to action getRecord to load values to modal form input.
$("#recordListing").on('click', '.update', function(){
var id = $(this).attr("id");
var action = 'getRecord';
$.ajax({
url:'ajax_action.php',
method:"POST",
data:{id:id, action:action},
dataType:"json",
success:function(data){
$('#recordModal').modal('show');
$('#id').val(data.id);
$('#name').val(data.name);
$('#age').val(data.age);
$('#skills').val(data.skills);
$('#address').val(data.address);
$('#designation').val(data.designation);
$('.modal-title').html(" Edit Records");
$('#action').val('updateRecord');
$('#save').val('Save');
}
})
});
We will call method updateRecord() from class Records.php to update records.
$database = new Database();
$db = $database->getConnection();
$record = new Records($db);
if(!empty($_POST['action']) && $_POST['action'] == 'updateRecord') {
$record->id = $_POST["id"];
$record->name = $_POST["name"];
$record->age = $_POST["age"];
$record->skills = $_POST["skills"];
$record->address = $_POST["address"];
$record->designation = $_POST["designation"];
$record->updateRecord();
}
We will create method updateRecord() in class Records.php to update records into MySQL database table.
public function updateRecord(){
if($this->id) {
$stmt = $this->conn->prepare("
UPDATE ".$this->recordsTable."
SET name= ?, age = ?, skills = ?, address = ?, designation = ?
WHERE id = ?");
$this->id = htmlspecialchars(strip_tags($this->id));
$this->name = htmlspecialchars(strip_tags($this->name));
$this->age = htmlspecialchars(strip_tags($this->age));
$this->skills = htmlspecialchars(strip_tags($this->skills));
$this->address = htmlspecialchars(strip_tags($this->address));
$this->designation = htmlspecialchars(strip_tags($this->designation));
$stmt->bind_param("sisssi", $this->name, $this->age, $this->skills, $this->address, $this->designation, $this->id);
if($stmt->execute()){
return true;
}
}
}
Step5: Handle Delete Records
We will handle records delete functionality by making ajax request with action deleteRecord to delete record from MySQL database table.
$("#recordListing").on('click', '.delete', function(){
var id = $(this).attr("id");
var action = "deleteRecord";
if(confirm("Are you sure you want to delete this record?")) {
$.ajax({
url:"ajax_action.php",
method:"POST",
data:{id:id, action:action},
success:function(data) {
dataRecords.ajax.reload();
}
})
} else {
return false;
}
});
We will call method deleteRecord() from class Records.php on action deleteRecord to delete records.
$database = new Database();
$db = $database->getConnection();
$record = new Records($db);
if(!empty($_POST['action']) && $_POST['action'] == 'deleteRecord') {
$record->id = $_POST["id"];
$record->deleteRecord();
}
We will create method deleteRecord() in class Records.php to delete records into MySQL database table.
public function deleteRecord(){
if($this->id) {
$stmt = $this->conn->prepare("
DELETE FROM ".$this->recordsTable."
WHERE id = ?");
$this->id = htmlspecialchars(strip_tags($this->id));
$stmt->bind_param("i", $this->id);
if($stmt->execute()){
return true;
}
}
}
You may also like:
- Build Live Chat System with Ajax, PHP & MySQL
- Create Event Calendar with jQuery, PHP and MySQL
- Build Invoice System with PHP & MySQL
- Push Notification System with PHP & MySQL
- Create Bootstrap Cards with PHP and MySQL
- Build Content Management System with PHP & MySQL
- Convert Unix Timestamp To Readable Date Time in PHP
- Ajax Drop Down Selection Data Load with PHP & MySQL
- Inventory Management System with Ajax, PHP & MySQL
- Drag and Drop File Upload using jQuery and PHP
- Load Dynamic Content in Bootstrap Popover with Ajax, PHP & MySQL
You can view the live demo from the Demo link and can download the full script from the Download link below.
Demo Download
I am trying to do same thing on my application. I find issues. Can I have someone to help me?
Please send your code to fix issues. thanks!
Please the pagination and tri doesn’t work?
You need to use “paging”: true, option for pagination when initialize datatables. thanks!
it show previous next but cannot move next or previous.
ps.record more than pagelength.
I have fixed the pagination issues. You can download the zip file. thanks!
Good job Sir. Excellent tutorials
Previousnext buttons not working, please.
You need to use “paging”: true, option for pagination when initialize datatables. thanks!
I have tried “paging”: true, not working
Thanks.
I have updated the tutorial with all issue fix with pagination. You can download the zip file. Thanks!
Hi,
Unable to find the download code.
could you please provide the source code.
Thanks & Regards,
Sanjeev
its at the end of tutorial. thanks!
Thanks!!! A great Tutorial!!!
Can u explain how to handle with column order if I add more columns?
The table became instable…
You need to handle when initialize DataTables in ajax.js file to modify order in columnDefs option. Thanks.
how to set mysqli charset for this.
I have fixed issues and updated the tutorial. you can download the updated code. thanks!
Hi,
Did you find the way?
I have issu with accent charachters and cant solve it by myself.
I would apprichiate any help!
Thanks!
I tried changing the database to my own database and also changed the variable but now i am getting the error Invalid JSON response.
Any fix to this?
May there will be issue to get data from MySQL database table. Try to debug line by line to know the exact issue. thanks!
I am getting the same error – only thing I did is change the name of the database in Database.php. Did you find the solution?
Found it – I had to change the database user ” root” to the database name (I have no direct root access)
Hi,
the table is quite usefull, everything worked just fine for me!
But,
1. how can i set the table to auto-fill the whole width of my monitor?
2. how can i set a fixed width for the table columns?
Thanks in advance,
You can check the jQuert Datatable documentation or this. thanks!
Can’t unlock the download. No matter how many times I click on the facebook like.
I have just checked and its working. please try again or if issue try to reload page and the download link will be displayed. thanks!
I want to put a new field, but when I put it it doesn’t pull me, and I added in the index php a text called effect and in the file records.php in the function addRecord () and put the following line $ this-> effect = htmlspecialchars (strip_tags ($ this-> effect));
INSERT INTO “. $ This-> recordsTable.” (`Name`,` age`, `skills`,` address`, `designation`,` effect`)
VALUES (?,?,?,?,?,?) “And $ stmt-> bind_param (” sisss “, $ this-> name, $ this-> age, $ this-> skills, $ this-> address, $ this-> designation, $ this-> effect);
but it doesn’t run
$stmt-> bind_param (” sisss “, $ this-> name, $ this-> age, $ this-> skills, $ this-> address, $ this-> designation, $ this-> effect);
Please add “s” to bind param at end because the “effect” field is string. if it is numeric, then you need to add “i”. It should be like this:
$stmt-> bind_param (” sissss “, $ this-> name, $ this-> age, $ this-> skills, $ this-> address, $ this-> designation, $ this-> effect);
thanks!
I would like to send a variable to function listRecords , so that it will only shows rows from that value
You can easily handle this, thanks!
Edit is not working on my localhost but work on your demo
Try to debug to know the exact cause of issue, may be there error that causing issue. Thanks!
You guys provide great tutorials, however this one is a little harder to follow when making changes due of name of table (records) which I want to change to admin and changed field/variables names and added an additional one.
I almost have it working but just cant seem to get it working correctly, would you please have a look at my changes and make fix for me, it should be a minor fix. Thanks in advance.
Yes, provide more details to help you. thanks!
I am having difficulty passing variable from 1 page to another with list.
I have copied the 4 files (php and ajax), renamed them and made my changes. It works correctly.
I created and extra button in your files passing the group_id to next page. This part works.
when I goto class/Records2.php i edit the following:
public function listRecords(){
$sqlQuery = “SELECT * FROM “.$this->recordsTable.” “;
with
public function listRecords(){
$sqlQuery = “SELECT * FROM “.$this->recordsTable.” WHERE group_id=”.$_GET[‘group_id’].” “;
But it gives me error, however when I put a value instead of the GET it lists the records correctly for the value.
please can you explain what I have done wrong.
Try to debug by printing GET value outside query or you can print query to know the cause of issue. thanks!
When I input manually data it appear, but I can’t edit it and add new record. The only thing which is working is delete button – it really delete this from database
Many Thanks for the tutorial.
Btw I found a glitch, when I sort column “age” it actually sorting “name” not “age”.
and if I sort “name” it sorting “id” instead of “name”.
this only affect column “age” and “name”, others column sort just right.
how to fixed that?
I am checking this and update. thanks!
Add this array into function listRecords
$columns = array(‘id’, ‘name’,’age’,’skills’,’address’,’designation’);
and fix this in if(!empty($_POST[“order”]))
$sqlQuery .= ‘ORDER BY ‘.$columns[$_POST[‘order’][‘0’][‘column’]].’ ‘.$_POST[‘order’][‘0’][‘dir’].’ ‘;
Great tutorial! In reference to column sorting, it seems when serverside=true column numbering is 1,2,etc vice serverside = false where column numbering is 0,1,etc. Below is what I changed in the “Inventory” example in category.js. Now my inventory category example sorts on the column heading I select. I would assume the others (product.js, customer.js, etc) would be the same. However, in the “Inventory” example I am working on pagination with serverside=true. No luck yet. Hope this helps. I am new to all this. If I am off base on this, please advise. Thank you.
“columnDefs”:[
{ “orderData”:[ 3 ],”targets”: 2 },
{ “orderData”:[ 2 ],”targets”: 1 },
{ “orderData”:[ 1 ],”targets”: 0 },
{
“targets”:[3, 4],
“orderable”:false,
},
],
The owner of the code forgot to add the array into the server side query.
Add this array into function listRecords
$columns = array(‘id’, ‘name’,’age’,’skills’,’address’,’designation’);
and fix this in if(!empty($_POST[“order”]))
$sqlQuery .= ‘ORDER BY ‘.$columns[$_POST[‘order’][‘0’][‘column’]].’ ‘.$_POST[‘order’][‘0’][‘dir’].’ ‘;
Hi,
my existing database has 15 columns and the ID column as primary key is not auto increment. What changes do I have to make?
Thanks for this great job!
You need to make column auto-increment by alter query.
For example:
ALTER TABLE `tablename` MODIFY `ID` INT AUTO_INCREMENT PRIMARY KEY;
Great work! when i click update the pop up come only with name other filled are empty how to fix
Try to debug, may there error. thanks!
How to change value of pagination from 10 to 20
You can check “pageLength” in ajax.js to change value as per your requirement. thanks!
The search is not working on my end… can you anyone help?
I get this error Fatal error: Call to a member function execute() on a non-object
Checking this and update you, thanks!
I am unable to download this. Please help
it’s fixed, you can download from download link in tutorial. thanks!
Please, Ordering in columns name and age are not ok. When i order by name, it orders by id. And age orders by name.. !
Both demo and Download keeps redirecting to different advert pages
it’s fixed, you can download from download link in tutorial. thanks!
Good job! Thanks!
I have one question – how to hide Id column ?
Hi, I am trying to add my own fields to my own database…I am missing something. Any help is greatly appreciated!
if($this->id) {
$stmt = $this->conn->prepare(”
UPDATE “.$this->recordsTable.”
SET playerNum= ?, firstName = ?, lastName = ?, playerDOB = ?, homeTown = ?, height = ?, weight = ?, shot = ?
WHERE id = ?”);
$this->id = htmlspecialchars(strip_tags($this->id));
$this->playerNum = htmlspecialchars(strip_tags($this->playerNum));
$this->firstName = htmlspecialchars(strip_tags($this->firstName));
$this->lastName = htmlspecialchars(strip_tags($this->lastName));
$this->playerDOB = htmlspecialchars(strip_tags($this->playerDOB));
$this->homeTown = htmlspecialchars(strip_tags($this->homeTown));
$this->height = htmlspecialchars(strip_tags($this->height));
$this->weight = htmlspecialchars(strip_tags($this->weight));
$this->shot = htmlspecialchars(strip_tags($this->shot));
$stmt->bind_param(“issssssss”, $this->id, $this->playerNum, $this->firstName, $this->lastName, $this->playerDOB, $this->homeTown, $this->height, $this->weight, $this->shot);
if($stmt->execute()){
return true;
}
Hello, i have one question. I want to add polish language to datatable. Where can i put this code?
$(“#recordListing”).DataTable( {
“language”: {
“url”: “//cdn.datatables.net/plug-ins/1.11.5/i18n/pl.json”
}
} );
Thank for your code, is working,
how to add field for searching, i try to add more
$sqlQuery = “SELECT * FROM “.$this->recordsTable.” “;
if(!empty($_POST[“search”][“value”])){
$sqlQuery .= ‘where (id LIKE “%’.$_POST[“search”][“value”].’%” ‘;
$sqlQuery .= ‘ OR name LIKE “%’.$_POST[“search”][“value”].’%” ‘;
$sqlQuery .= ‘ OR age LIKE “%’.$_POST[“search”][“value”].’%” ‘;
$sqlQuery .= ‘ OR address LIKE “%’.$_POST[“search”][“value”].’%” ‘;
$sqlQuery .= ‘ OR skills LIKE “%’.$_POST[“search”][“value”].’%”) ‘;
but i gtt error
DataTables warning: table id=recordListing – Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1
thank you for this tut. unfortunatly i’m not able to run it on my testsystem.
even when i try to run it without any changes (with exception the Database changes) i allways get this error: DataTables warning: table id=recordListing – Invalid JSON response
Check may be there issue with getting data from DB, thanks!
Where does this code appear?:-
$record = new Records();
if(!empty($_POST[‘action’]) && $_POST[‘action’] == ‘listRecords’) {
$record->listRecords();
}
My code editor (Visual Studio Code) cannot find it in any of the files included in the download!
its in ajax_action.php file. thanks
From the downloaded files I get “cannot be found” using the search facility in VCE:-
deleteRecord() Does not apear in Records.php
$(“#recordListing”).on(‘click’, ‘.update’, function()
public function updateRecord()
$(“#recordListing”).on(‘click’, ‘.delete’, function()
public function deleteRecord()
public function addRecord()
$(“#recordModal”).on(‘submit’,’#recordForm’, function(event)
public function listRecords()
var dataRecords = $(‘#recordListing’).DataTable(
All the above functions and methods are shown on the web page but do not appear in the downloaded package!
Would appreciate some help in getiing this code sorted please?
Just checked, its all files with code avaible in download zip file.
how can i display more 10 rows ?
Good job
you can change value of “pageLength” when initialize datatables.