Live Add Edit Delete Datatables Records with Ajax, PHP & MySQL

PHPZAG Team PHP, Tutorials

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

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:


You can view the live demo from the Demo link and can download the full script from the Download link below.
Demo Download

67 thoughts on “Live Add Edit Delete Datatables Records with Ajax, PHP & MySQL”

  1. I am trying to do same thing on my application. I find issues. Can I have someone to help me?

        1. I have updated the tutorial with all issue fix with pagination. You can download the zip file. Thanks!

  4. Hi,

    Unable to find the download code.
    could you please provide the source code.
    Thanks & Regards,
    Sanjeev

  5. Thanks!!! A great Tutorial!!!
    Can u explain how to handle with column order if I add more columns?
    The table became instable…

    1. You need to handle when initialize DataTables in ajax.js file to modify order in columnDefs option. Thanks.

    2. Hi,
      Did you find the way?
      I have issu with accent charachters and cant solve it by myself.
      I would apprichiate any help!

      Thanks!

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

    1. May there will be issue to get data from MySQL database table. Try to debug line by line to know the exact issue. thanks!

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

        1. Found it – I had to change the database user ” root” to the database name (I have no direct root access)

  8. 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,

  9. Can’t unlock the download. No matter how many times I click on the facebook like.

    1. 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!

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

    1. $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!

  11. I would like to send a variable to function listRecords , so that it will only shows rows from that value

    1. Try to debug to know the exact cause of issue, may be there error that causing issue. Thanks!

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

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

    1. Try to debug by printing GET value outside query or you can print query to know the cause of issue. thanks!

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

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

      1. 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’].’ ‘;

  17. 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,
    },
    ],

    1. 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’].’ ‘;

  18. 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!

    1. You need to make column auto-increment by alter query.

      For example:

      ALTER TABLE `tablename` MODIFY `ID` INT AUTO_INCREMENT PRIMARY KEY;

  19. Great work! when i click update the pop up come only with name other filled are empty how to fix

    1. You can check “pageLength” in ajax.js to change value as per your requirement. thanks!

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

      1. Please, Ordering in columns name and age are not ok. When i order by name, it orders by id. And age orders by name.. !

  25. 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;
    }

  26. 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”
    }
    } );

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

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

  29. 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!

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

Comments are closed.