Live Table Edit And Delete With Pagination Using Jquery Ajax

Live Table Edit And Delete With Pagination Using Jquery Ajax

Although there are many jquery plugins available on the Internet to live edit the data tables but in this article we are writing our own simple php and javascript files to achieve the same target using ajax. I’m a big fan of Shrinivas Tamada (Owner of  9lessons.info) and highly inspired by him. I always love his posts and tutorials. In this article I’m just extending one of this most popular post (Live table data edit and delete) by implementing the multiselect delete and ajax pagination. The post is very useful for the beginners who want to learn ajax and jquery with php. I’ve used PHP’s OOPS concept and “On” event handler of jquery to reduce the direct function calling in the project. So without wasting your too much time, I move to further and start to write the application.

Dynamically Add Remove Text Box Using Jquery

Live Table Edit And Delete With Pagination Using Jquery Ajax

You will get the following files in the downloaded zip archive:
1. Index.php (Base php file to boot up the application)
2. Database.php (Handles database connection and other mysql methods)
3. Style.css (Mark up for the application to give a beautiful look)
4. Script.js (Create ajax functionality in the project using dom)
5. livetable.sql (Database sql dump to create the users table)

Now First of all we design our database and create the users table

CREATE DATABASE `livetable`;
CREATE TABLE `livetable`.`users`(
`id` INT(5) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50),
`email` VARCHAR(100),
`country` VARCHAR(50),
`mobile` VARCHAR(50),
PRIMARY KEY (`id`) );

Let’s move next to create the basic markup and style for our application as we always do. I tried to reduce the css code too much but a branded shirt is always looks bad without its buttons. Hope you understood.

Style.css

/* CSS Document */
body {
margin:0;
padding:0;
color:#333333;
}

#outer_container {
width:80%;
margin:0 auto;
padding: 50px 10px 10px 10px;
border:1px solid #ccc;
font-family:Arial, Helvetica, sans-serif;
font-size:12px;
position:relative;
}

input {
background:#DDF99A;
border: 1px solid #B4D349;
border-radius: 2px 2px 2px 2px;
padding: 5px;
width: 90%;
}

#loader{
position: absolute;
text-align: center;
z-index: 9999;
margin:auto;
display:none;
top: 50%;
left: 50%;
}

.delall {
background: #1F9DBF;
border: medium none;
color: #FFFFFF;
cursor: pointer;
height: 35px;
margin: 0 0 15px 45px;
padding: 0 5px 2px;
}

#data_tbl {
font-family:Arial, Helvetica, sans-serif;
border-left:1px solid #ccc;
border-top:1px solid #ccc;
}

#data_tbl tbody tr:hover{
background-color:#FFFFD2;
}

#data_tbl td,#data_tbl th{
padding:7px;
border-right:1px solid #ccc;
border-bottom:1px solid #ccc;
}

#data_tbl thead {
background: #00b7ea;
background: -moz-linear-gradient(top,  #00b7ea 22%, #009ec3 73%);
background: -webkit-gradient(linear, left top, left bottom, color-stop(22%,#00b7ea), color-stop(73%,#009ec3));
background: -webkit-linear-gradient(top,  #00b7ea 22%,#009ec3 73%);
background: -o-linear-gradient(top,  #00b7ea 22%,#009ec3 73%);
background: -ms-linear-gradient(top,  #00b7ea 22%,#009ec3 73%);
background: linear-gradient(to bottom,  #00b7ea 22%,#009ec3 73%);
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#00b7ea', endColorstr='#009ec3',GradientType=0 );
color: #FFFFFF;
font-weight: bold;
height: 35px;
}

.updrow,.delrow{
cursor:pointer;
}

#paginator {
margin: 15px auto;
width: 91%;
}

.pagin {
padding: 10px 0;
font:bold 11px/30px arial, serif;
}

.pagin * {
padding: 2px 6px;
color:#3A3B2D;
margin: 2px;
border-radius:3px;
}

.pagin a {
border:solid 1px #036E92;
text-decoration:none;
background:#F8FCFF;
padding:6px 7px 5px;
}

.pagin span, a:hover, .pagin a:active, .pagin span.current {
background: -moz-linear-gradient(center top , #088899 1px, #036E92 1px, #049DB8) repeat scroll 0 0 rgba(0, 0, 0, 0);
color: #FFFFFF;
}

.pagin span,.current{
padding:8px 7px 7px;
}

Script.js For Jquery Operations


//JavaScript Document
$(document).ready(function(){
     load('action=ajax&page=1'); //Load by default first page on load
});
$(document).on('click','.pagin a', function(){
    load('action=ajax&page='+$(this).attr('id')); //Load data when click on pagination
})
function load(str) {
   $("#loader").fadeIn('slow');
   $('#data').load('index.php?'+str,function() {
     $("#loader").fadeOut('slow');
   });
}

//Select All with checkbox functionality
$(document).on('click','input:checkbox',function(e) {
  if($(this).hasClass('selall')) {
       $('.selrow').prop({checked:$(this).is(':checked')});
   } else {
   if($('.selrow:checked').length == $('.selrow').length) {
      $('.selall').prop({checked:true})
   } else {
      $('.selall').prop({checked:false})
   }
  }
});

//Functionality for single or multiple delete
$(document).on('click','.delall,.delrow',function(e){
  var id;
  if($(this).hasClass('delall')) {
     e.preventDefault();
     id = $('.selrow:checked').map(function(){
              return $(this).val();
           }).get();
     } else {
        id = $(this).parents('tr').find('input:hidden').val();
     }
   if($('.selrow:checked').length == 0 && $(this).hasClass('delall')) {
     alert('Please select atleast one row');
   } else if(confirm('Do you really want to delete')) {
      load('action=delete&page=1&id='+id);
   }
      e.stopImmediatePropagation();
});

//Save updated data in database
function savedata() {
   if($('.modified').length > 0) {
       $('.modified').each(function(e){
       $tr = $(this);
       $tr.find('input:text').hide();
       $.post('index.php?'+$tr.find(':input').serialize()+'&action=update',function() {});
       $tr.find('span').show(function(){
       $(this).text($(this).next('input').val()).next('input').remove();
     });
   $tr.css('background-color','#F5E6DA').removeClass('modified').find('img').show();
});
}
}

//Save data when click anywhere on page body
$(document).on('click','html',function(e){
   if(! $(e.target).is(':input')) {
    savedata();
   }
   e.stopImmediatePropagation();
});

//Show input boxes in row when click on update icon
$(document).on('click','.updrow',function(e){
    $(this).hide();
    $tr = $(this).parents('tr');
    $tr.addClass('modified');
    $tr.css('background-color','#686C70');
    $tr.find('span').each(function(){
    $(this).hide(function(){
    $(this).after('<input type="text" name="'+$(this).attr('class')+'" value="'+$(this).text()+'" maxlength="35" />');
   });
 });
   e.stopImmediatePropagation();
});

Database.php For Database and Pagination

<?php
/**
* Database Class
* @author Manish Jangir
*/
class Database {
const DB_HOST = 'localhost';
const DB_USER = 'root';
const DB_PASSWORD = '';
const DB_NAME = 'livetable';
private $_dbconnect = NULL;
private $_table = 'users';
private $_adj = 4;
private $_tpages = 0;
private $_limit = 5;
private $_offset= 0;
private $_page = 1;
private $_prev_lbl = '&lsaquo; Prev';
private $_next_lbl = 'Next &rsaquo;';

public function __construct() {
$this->_dbconnect = mysql_connect(self::DB_HOST,self::DB_USER,self::DB_PASSWORD);
if ($this->_dbconnect) {
$db =  mysql_select_db(self::DB_NAME,$this->_dbconnect);
} else {
die(mysql_error());
}
$this->_page = (isset($_REQUEST['page']) && !empty($_REQUEST['page']))?$_REQUEST['page']:1;
$this->_offset = ($this->_page - 1) * $this->_limit;
}

private function total() {
$result = mysql_query("select count(id) AS total FROM $this->_table");
$row = mysql_fetch_array($result);
return $row['total'];
}

public function get_users() {
$query = mysql_query("SELECT * FROM $this->_table ORDER BY id DESC LIMIT $this->_offset,$this->_limit");
$result = array();
$i = 0;
while($res = mysql_fetch_assoc($query)){
$result[$i] = $res;
$i++;
}
return $result;
}

public function delete($id){
$ids = is_array($id) ? implode(',', $id) : $id;
$query = mysql_query("DELETE FROM $this->_table WHERE id IN ($ids)");
return $this->result($query);
}
public function insert($data) {
$keys = implode(',', array_keys($data));
$values = "'" . implode("','", array_values($data)) . "'";
$query = mysql_query("INSERT INTO $this->_table ($keys) VALUES ($values)");
return $this->result($query);
}

public function update($data) {
$id = $data['id'];
unset($data['id']);
$query = "UPDATE $this->_table SET ";
foreach ($data as $key => $value) {
$params[] = $key." = '".$value."'";
}
$query .= implode(',', $params)." WHERE id = $id";
return $this->result(mysql_query($query));
}

private function result($q) {
return $q ? true : false;
}

public function paginate() {
$this->_tpages = ceil($this->total()/$this->_limit);
$out = '<div>';
if($this->_page == 1) {
$out .= "<span>$this->_prev_lbl</span>";
} else {
$out .= "<a href='javascript:void(0);' id='".($this->_page-1).")'>$this->_prev_lbl</a>";
}
$out.= ($this->_page>($this->_adj+1)) ? "<a href='javascript:void(0);' id='1'>1</a>" : '';
$out.= ($this->_page>($this->_adj+2)) ? $out.= "...\n" : '';
$pmin = ($this->_page>$this->_adj) ? ($this->_page-$this->_adj) : 1;
$pmax = ($this->_page<($this->_tpages-$this->_adj)) ? ($this->_page+$this->_adj) : $this->_tpages;
for($i=$pmin; $i<=$pmax; $i++) {
if($i==$this->_page) {
$out.= "<span class='current'>$i</span>";
}else {
$out.= "<a href='javascript:void(0);' id='$i'>$i</a>";
}
}
$out. ($this->_page<($this->_tpages-$this->_adj-1)) ? $out.= "...\n" : '';
$out.= ($this->_page<($this->_tpages-$this->_adj))? $out.= "<a href='javascript:void(0);' id='$this->_tpages'>$this->_tpages</a>" : '';
if($this->_page<$this->_tpages) {
$out.= "<a href='javascript:void(0);' id='".($this->_page+1)."'>$this->_next_lbl</a>";
}else {
$out.= "<span>$this->_next_lbl</span>";
}
$out.= "</div>";
return $out;
}
}
?>

Index.php Base File

<?php
$action = (isset($_REQUEST['action']) && !empty($_REQUEST['action']))?$_REQUEST['action']: NULL;
if(empty($action)) {
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Jquery Ajax Live Table Edit</title>
<link rel="stylesheet" type="text/css" href="style.css" />
<script type="text/javascript" src="jquery-1.10.1.min.js"></script>
<script type="text/javascript" src="script.js"></script>
</head>
<body>
<div id="outer_container">
<div id="loader" ><img src="loader.gif"></div>
<div id="data" style="position:relative;">
<?php } ?>

<?php if(empty($action)) {?>
</div>
</div>
</body>
</html>
<?php }

if($action == 'ajax' || $action == 'update' || $action == 'delete') {
require_once 'database.php';
$db = new Database;
function getTable() {
GLOBAL $db;
$data = '<button>Delete Selected</button>
<form><table width="90%" cellspacing="0" cellpadding="2" align="center" border="0" id="data_tbl">
<thead>
<tr>
<th width="5%"><input type="checkbox"/></th>
<th width="25%">Name</th>
<th width="25%">Email</th>
<th width="17%">Country</th>
<th width="18%">Mobile</th>
<th width="10%">Action</th>
</tr>
</thead>
<tbody>';
$i = 1;
$cls = false;
foreach ($db->get_users() as $value) {
$bg = ($cls = !$cls) ? '#ECEEF4' : '#FFFFFF';
$data .='<tr style="background:'.$bg.'">
<td><input type="checkbox" value="'.$value['id'].'"/>
<input type="hidden" value="'.$value['id'].'" name="id" />
</td>
<td><span>'.$value['name'].' </span></td>
<td><span>'.$value['email'].'</span></td>
<td><span>'.$value['country'].'</span></td>
<td><span>'.$value['mobile'].'</span></td>
<td align="center">
<img src="edit.png" title="Update"/>&nbsp;
<img src="delete.png" title="Delete"/>
</td>
</tr>';
$i++;
}
$data .='</tbody>
</table></form>
<div id="paginator">'.$db->paginate().'</div>';
return $data;
}

if($action == 'ajax') {
echo getTable();
} else if($action == 'delete') {
$db->delete($_REQUEST['id']);
echo getTable();
} else if($action == 'update') {
unset($_REQUEST['action']);
$db->update($_REQUEST);
}
}
?>

Simple download the zip file and extract in your root folder to run the application straight forward.

Live Table Edit And Delete With Pagination Using Jquery Ajax

5 Responses to “Live Table Edit And Delete With Pagination Using Jquery Ajax”
  1. tester says:

    Hi,

    Great Work dude, but i have one request from you can you email me a different text filed option , i mean to say you have using a input in loop , but if i want to use drop-down , check-box ..etc then how i can add and edit .. can you let me know ? .

    Thanks in advance

  2. Manish says:

    Hi,
    I used this script, it works great while displaying text,
    i rename index.php to member_update.php & changed some required.
    delete works. but update is not changing the text
    Thanks

  3. Martin says:

    Hi,
    I downloaded the demo.
    I connect a table to the database. In this tutorial works only delete row?
    If I try edit record and reload page changes are not saved to the database…

    Thanks

  4. Jamial Hasan says:

    pagination not working ot appearing multiple times for large amount data. please help.

  5. chirag says:

    Thanksssssssssssssssssssssssssssss.

Leave a Reply

Manish Jangir - Find me on Bloggers.com