This post is the example of live table data edit, delete records with pagination using Jquery, Ajax and PHP. I had implemented this using Jquery .live() function. This script helps you to instantly modify or update the table data.You know that majority of readers had requested this tutorial, hope you guys like it!.
Download Code Live Demo
The tutorial contains PHP and Javascript files.
index.php
table_data.php
load_data.php
live_edit_table.php
delete_ajax.php
db.php
EditDeletePage.js
Code Start Here:
index.php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<!-- This is a pagination script using Jquery, Ajax and PHP
The enhancements done in this script pagination with first,last, previous, next buttons -->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Live Edit, Pagination and Delete Records with Jquery</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.5/jquery.min.js"></script>
<script type="text/javascript" src="EditDeletePage.js"></script>
<style type="text/css">
body{
width: 800px;
margin: 0 auto;
padding: 0;
font-family:Arial, Helvetica, sans-serif
}
#loading{
width: 100%;
position: absolute;
top: 100px;
left: 100px;
margin-top:200px;
}
#container .pagination ul li.inactive,
#container .pagination ul li.inactive:hover{
background-color:#ededed;
color:#bababa;
border:1px solid #bababa;
cursor: default;
}
#container .data ul li{
list-style: none;
font-family: verdana;
margin: 5px 0 5px 0;
color: #000;
font-size: 13px;
}
#container .pagination{
width: 800px;
height: 25px;
}
#container .pagination ul li{
list-style: none;
float: left;
border: 1px solid #006699;
padding: 2px 6px 2px 6px;
margin: 0 3px 0 3px;
font-family: arial;
font-size: 14px;
color: #006699;
font-weight: bold;
background-color: #f2f2f2;
}
#container .pagination ul li:hover{
color: #fff;
background-color: #006699;
cursor: pointer;
}
.go_button
{
background-color:#f2f2f2;border:1px solid #006699;color:#cc0000;padding:2px 6px 2px 6px;cursor:pointer;position:absolute;margin-top:-1px;
}
.total
{
float:right;font-family:arial;color:#999;
}
.editbox
{
display:none;
}
td, th
{
width:20%;
text-align:left;;
padding:5px;
}
.editbox
{
padding:4px;
}
</style>
</head>
<body>
<div id="loading"></div>
<div id="container"></div>
<div style="margin-top:30px">
</div>
</body>
</html>
EditDeletePage.js
// 9lessons programming blog
// Srinivas Tamada http://9lessons.info
$(document).ready(function()
{
$(".delete").live('click',function()
{
var id = $(this).attr('id');
var b=$(this).parent().parent();
var dataString = 'id='+ id;
if(confirm("Sure you want to delete this update? There is NO undo!"))
{
$.ajax({
type: "POST",
url: "delete_ajax.php",
data: dataString,
cache: false,
success: function(e)
{
b.hide();
e.stopImmediatePropagation();
}
});
return false;
}
});
$(".edit_tr").live('click',function()
{
var ID=$(this).attr('id');
$("#one_"+ID).hide();
$("#two_"+ID).hide();
$("#three_"+ID).hide();
$("#four_"+ID).hide();
$("#one_input_"+ID).show();
$("#two_input_"+ID).show();
$("#three_input_"+ID).show();
$("#four_input_"+ID).show();
}).live('change',function(e)
{
var ID=$(this).attr('id');
var one_val=$("#one_input_"+ID).val();
var two_val=$("#two_input_"+ID).val();
var three_val=$("#three_input_"+ID).val();
var four_val=$("#four_input_"+ID).val();
var dataString = 'id='+ ID +'&name='+one_val+'&category='+two_val+'&price='+three_val+'&discount='+four_val;
if(one_val.length>0&& two_val.length>0 && three_val.length>0 && four_val.length>0)
{
$.ajax({
type: "POST",
url: "live_edit_ajax.php",
data: dataString,
cache: false,
success: function(e)
{
$("#one_"+ID).html(one_val);
$("#two_"+ID).html(two_val);
$("#three_"+ID).html(three_val);
$("#four_"+ID).html(four_val);
e.stopImmediatePropagation();
}
});
}
else
{
alert('Enter something.');
}
});
// Edit input box click action
$(".editbox").live("mouseup",function(e)
{
e.stopImmediatePropagation();
});
// Outside click action
$(document).mouseup(function()
{
$(".editbox").hide();
$(".text").show();
});
//Pagination
function loading_show(){
$('#loading').html("<img src='images/loading.gif'/>").fadeIn('fast');
}
function loading_hide(){
$('#loading').fadeOut('fast');
}
function loadData(page){
loading_show();
$.ajax
({
type: "POST",
url: "load_data.php",
data: "page="+page,
success: function(msg)
{
$("#container").ajaxComplete(function(event, request, settings)
{
loading_hide();
$("#container").html(msg);
});
}
});
}
loadData(1); // For first time page load default results
$('#container .pagination li.active').live('click',function(){
var page = $(this).attr('p');
loadData(page);
});
$('#go_btn').live('click',function(){
var page = parseInt($('.goto').val());
var no_of_pages = parseInt($('.total').attr('a'));
if(page != 0 && page <= no_of_pages){
loadData(page);
}else{
alert('Enter a PAGE between 1 and '+no_of_pages);
$('.goto').val("").focus();
return false;
}
});
});
live_edit_ajax
<?php
include("db.php");
if($_POST['id'])
{
$id=mysql_escape_String($_POST['id']);
$name=mysql_escape_String($_POST['name']);
$category=mysql_escape_String($_POST['category']);
$price=mysql_escape_String($_POST['price']);
$discount=mysql_escape_String($_POST['discount']);
$sql = "update products set name='$name',category='$category',price='$price',discount='$discount' where pid='$id'";
mysql_query($sql);
}
?>
load_data.php
<?php
include "db.php";
if($_POST['page'])
{
$page = $_POST['page'];
$cur_page = $page;
$page -= 1;
$per_page = 5; // Per page
$previous_btn = true;
$next_btn = true;
$first_btn = true;
$last_btn = true;
$start = $page * $per_page;
include('table_data.php');
/* ---------------Calculating the starting and endign values for the loop----------------------------------- */
if ($cur_page >= 7) {
$start_loop = $cur_page - 3;
if ($no_of_paginations > $cur_page + 3)
$end_loop = $cur_page + 3;
else if ($cur_page <= $no_of_paginations && $cur_page > $no_of_paginations - 6) {
$start_loop = $no_of_paginations - 6;
$end_loop = $no_of_paginations;
} else {
$end_loop = $no_of_paginations;
}
} else {
$start_loop = 1;
if ($no_of_paginations > 7)
$end_loop = 7;
else
$end_loop = $no_of_paginations;
}
/* ----------------------------------------------------------------------------------------------------------- */
$finaldata .= "<div class='pagination'><ul>";
// FOR ENABLING THE FIRST BUTTON
if ($first_btn && $cur_page > 1) {
$finaldata .= "<li p='1' class='active'>First</li>";
} else if ($first_btn) {
$finaldata .= "<li p='1' class='inactive'>First</li>";
}
// FOR ENABLING THE PREVIOUS BUTTON
if ($previous_btn && $cur_page > 1) {
$pre = $cur_page - 1;
$finaldata .= "<li p='$pre' class='active'>Previous</li>";
} else if ($previous_btn) {
$finaldata .= "<li class='inactive'>Previous</li>";
}
for ($i = $start_loop; $i <= $end_loop; $i++) {
if ($cur_page == $i)
$finaldata .= "<li p='$i' style='color:#fff;background-color:#006699;' class='active'>{$i}</li>";
else
$finaldata .= "<li p='$i' class='active'>{$i}</li>";
}
// TO ENABLE THE NEXT BUTTON
if ($next_btn && $cur_page < $no_of_paginations) {
$nex = $cur_page + 1;
$finaldata .= "<li p='$nex' class='active'>Next</li>";
} else if ($next_btn) {
$finaldata .= "<li class='inactive'>Next</li>";
}
// TO ENABLE THE END BUTTON
if ($last_btn && $cur_page < $no_of_paginations) {
$finaldata .= "<li p='$no_of_paginations' class='active'>Last</li>";
} else if ($last_btn) {
$finaldata .= "<li p='$no_of_paginations' class='inactive'>Last</li>";
}
$goto = "<input type='text' class='goto' size='1' style='margin-top:-1px;margin-left:60px;'/><input type='button' id='go_btn' class='go_button' value='Go'/>";
$total_string = "<span class='total' a='$no_of_paginations'>Page <b>" . $cur_page . "</b> of <b>$no_of_paginations</b></span>";
$finaldata = $finaldata . "</ul>" . $goto . $total_string . "</div>"; // Content for pagination
echo $finaldata;
}
table_data.php
<?php
$query_pag_data = "SELECT pid,name,category,price,discount from products LIMIT $start, $per_page";
$result_pag_data = mysql_query($query_pag_data) or die('MySql Error' . mysql_error());
$finaldata = "";
$tablehead="<tr><th>Product Name</th><th>Category</th><th>Price</th><th>Discount</th><th>Edit</th></tr>";
while($row = mysql_fetch_array($result_pag_data))
{
$id=$row['pid'];
$name=htmlentities($row['name']);
$category=htmlentities($row['category']);
$price=htmlentities($row['price']);
$discount=htmlentities($row['discount']);
$tabledata.="<tr id='$id' class='edit_tr'>
<td class='edit_td' >
<span id='one_$id' class='text'>$name</span>
<input type='text' value='$name' class='editbox' id='one_input_$id' />
</td>
<td class='edit_td' >
<span id='two_$id' class='text'>$category</span>
<input type='text' value='$category' class='editbox' id='two_input_$id'/>
</td>
<td class='edit_td' >
<span id='three_$id' class='text'>$price $</span>
<input type='text' value='$price' class='editbox' id='three_input_$id'/>
</td>
<td class='edit_td' >
<span id='four_$id' class='text'>$discount $</span>
<input type='text' value='$discount' class='editbox' id='four_input_$id'/>
</td>
<td><a href='#' class='delete' id='$id'> X </a></td>
</tr>";
}
$finaldata = "<table width='100%'>". $tablehead . $tabledata . "</table>"; // Content for Data
/* Total Count */
$query_pag_num = "SELECT COUNT(*) AS count FROM products";
$result_pag_num = mysql_query($query_pag_num);
$row = mysql_fetch_array($result_pag_num);
$count = $row['count'];
$no_of_paginations = ceil($count / $per_page);
?>
delete_ajax.php
<?php
include("db.php");
if($_POST['id'])
{
$id=mysql_escape_String($_POST['id']);
$sql = "delete from products where pid='$id'";
mysql_query($sql);
}
?>
db.php
<?php
$mysql_hostname = "localhost";
$mysql_user = "root";
$mysql_password = "";
$mysql_database = "db";
$prefix = "";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Opps some thing went wrong");
mysql_select_db($mysql_database, $bd) or die("Opps some thing went wrong");
?>
