A fancy add,edit,delete ajax paginated mysql table

In this Tutorial we will learn how to create a paginated mysql table using a php class. There are many php pagination classes,but the one we are going to use in this tutorial is simple yet robust.We will be dealing with Six files including the pagination class, dont worry we will be going through each file one at a time,hopefully by the end of this tutorial,the whole files will come together perfectly. First of all this tutorial is inspired by this tutorial,basically the function of both tables are the same save that the one we are about to create is more fancy.This table can be used to add,edit,delete and search for data.
So let gets started,we will be using a pagination class written by Reneesh T.K,you can find the full documentation of the class here. Also we will be using a fancy modal by codrops. All Ajax calls for this tutorial will be jQuery based so put on your jQuery cap on.

1. index.php

<head>
  <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js">
  </script>
  <script type="text/javascript" src="pagination.js"></script>
</head>

<body>
	<div id="formDiv">
		<form id="formSearch" >
			Search by Name 
			<input type="text" id="fieldSearch" name="search_text" >
			<input type="submit" value="Search">
		</form>
		<div  id="divLoading"></div> 
			<div id="selectDiv">
			<small>
				<select id="pageRecord">
					<option value="1">1</option>
					<option value="2">2</option>
					<option selected value="3">3</option>
					<option value="4">4</option>
				</select><i> Record per Page</i>
			</small>
		</div>
	</div>
	
	<div  id="divPageData"></div>
</body>

The form element will be used to query the database,the div with id "divLoading" is the wrapper for our image loading gif file which will be imported by ajax to this page,the "divPageData" is the wrapper that hold the data imported also by ajax form file tablePage.php,we will see more of this file later on.

2. style.css

	*, *:after, *:before { 
		-webkit-box-sizing: border-box; 
		-moz-box-sizing: border-box; 
		box-sizing: border-box; 
	}

	.clearfix:before, .clearfix:after { 
		content: ''; 
		display: table; 
	}

	.clearfix:after { 
		clear: both;
	}

	body {
		position: relative;
		background: #fff;
		font-size: 100%;
		line-height: 1.25;
		font-weight: 300;
		font-family: 'Lato', Arial, sans-serif;
	}

	#formDiv {
		position:relative;
		width:650px;
		background:#fef0e3;
		border-radius:3px;
		padding:5px;
		margin:20px 0 10px 280px;
		border:1px solid #e75854;
	}

	#formSearch input[type="submit"] {
		background-color:#e75854;
		color:#fff
	}

	#divLoading {
		position:absolute;
		top:8px;
		left:620px;
	}

	#selectDiv {
		position:absolute;
		top:8px;
		left:370px;
	}

	#divPageData {
		margin-left:280px;
	}

	ul{ border:0; margin:0; padding:0;}

	#pagination-flickr li {
		border:0; margin:0; padding:0;
		font-size:11px;
		list-style:none;
		border-radius:5px;
	}

	#pagination-flickr li a {
		border-radius:5px;
	}

	#pagination-flickr a {
		border:solid 1px #DDDDDD;
		margin-right:2px;
		background-color:#e75854;
	}

	#pagination-flickr .previous-off,
	#pagination-flickr .next-off {
		color:#666666;
		display:block;
		float:left;
		font-weight:bold;
		padding:3px 4px;
	}

	#pagination-flickr .next a,
	#pagination-flickr .previous a {
		font-weight:bold;
		border:solid 1px #FFFFFF;
	}

	#pagination-flickr .active {
		color:#ff0084;
		font-weight:bold;
		display:block;
		float:left;
		padding:4px 6px;
	}

	#pagination-flickr a:link,
	#pagination-flickr a:visited {
		color:#fff;
		display:block;
		float:left;
		padding:3px 6px;
		text-decoration:none;
	}

	#pagination-flickr a:hover {
		border:solid 1px #666666;
	}

	#student_wrap {
		width:650px;
		border:1px solid #e75854;
		border-radius:4px;
		overflow: hidden;
		background:#fff;
	}

	#students th {
		font-size:12px;
		text-align:left;
		padding:3px 3px 3px 5px;
		background-color:#e75854;
		color:#ffffff;
		border-left: 1px solid #fef0e3;
	}

	#students   th:first-child { 
		border: none; 
	}

	#students tbody td:first-child {
		border-left: none; 
	}

	#students  tbody td {
		font-size:12px;
		padding:3px 3px 3px 5px;
		border-left: 1px solid #fef0e3;
	}

	#students tfoot td {
		padding:0;
		background: #fef0e3;
	}

	#students tfoot td div{
		border-top:1px solid #e75854;
		padding:4px;
	}

	#students tfoot #total td {
		padding:4px;
	}

	.odd {
		background-color: #fff;
	}

	.even {
		background-color: #fef0e3 ;
	}

This is our main css file,there are other css files need to use for our javascript modal. We will be refering to those files at the concluding part of this tutorial.

3. pagination.js

$(document).ready(function(){
	$.ajaxSetup({
		timeout: 10000,
		cache: false,
		error:	function(x,e){
			if(x.status==0){
				alert('You are offline, please check your connection');
			} else if (x.status==404){
				alert('Request unknown URL');
			} else if (x.status==500){
				alert('Internal Server Error!');
			} else if (e=='parsererror'){
				alert('Error.\nParsing JSON Request failed!');
			} else if (e=='timeout') {
				alert('Request Time out!');
			} else {
				alert('Unknown Error: \n'+x.responseText);
			}
		}
	});

	$('#divLoading').ajaxStart(function(){
		$(this).fadeIn();
		$(this).html("<img src='loading.gif' />");
	}).ajaxStop(function(){
		$(this).fadeOut();
	});

	loadData();

	function loadData()
	{
		var dataString;
		var search = $("input#fieldSearch").val();
		var record = $("select#pageRecord").val();
		dataString = 'nama='+ search + '&perpage='+ record;

		$.ajax({
			url: "testpage.php",
			type: "GET",
			data: dataString,
			success: function(data)
			{
				$('#divPageData').html(data);
			}
		});
	}

	$("form#formSearch").submit(function(){
		loadData();
		return false;
	});

}); 

The $.ajaxSetup call handles all ajax errors for the entire script,since we will be relying heavily on ajax, this function is very very important. The ajaxstart function fades in our loading image gif on start of any ajax call for the entire script and the ajaxstop hides the image gif at the stop of any ajax calls. The loadData() loads the table from our tablepage.php file on click of the search button on our index.php file,we will be seeing more use of this function later on.

3. connect.php

<?php
	$hostname = 'localhost';
	$username = 'root';
	$password = '';
	$database = 'demo';

	$con = mysqli_connect( $hostname, $username, $password, $database )
	or die ( mysqli_error() );
?>;

After we establish connection to the database we create a table like this

	CREATE TABLE IF NOT EXISTS `students` (
		`id` int(11) NOT NULL AUTO_INCREMENT,
		`name` varchar(25) COLLATE latin1_general_ci NOT NULL,
		`address` varchar(50) COLLATE latin1_general_ci NOT NULL,
		`exam_no` varchar(15) COLLATE latin1_general_ci NOT NULL,
		PRIMARY KEY (`id`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

4. pagination_class.php

<?php

include('connect.php');

/* Developed by Reneesh T.K */

class Pagination_class {
	var $result;
	var $anchors;
	var $total;
    
	function Pagination_class( $qry, $starting, $recpage ) {    
		$rst = mysqli_query( $con, $qry ) or die( mysql_error() );
		$numrows = mysqli_num_rows( $rst );
		$qry. =	" limit $starting, $recpage";
		$this->result = mysqli_query( $con, $qry ) or die( mysql_error() );
		$next =	$starting+$recpage;
		$var = ( ( intval( $numrows/$recpage ) ) -1 ) * $recpage;
		$page_showing =	intval( $starting/$recpage ) + 1;
		$total_page = ceil( $numrows/$recpage );

		if ( $numrows % $recpage != 0 ) {
			$last = ( ( intval( $numrows/$recpage ) ) ) * $recpage;
		} else {
			$last = ( ( intval( $numrows/$recpage ) ) -1 ) * $recpage;
		}

		$previous = $starting - $recpage;
		$anc = "<ul id='pagination-flickr'>";

		if( $previous < 0 ) {
			$anc .= "<li class='previous-off'>First</li>";
			$anc .= "<li class='previous-off'>Previous</li>";
		} else {
			$anc .= "<li class='next'><a href='javascript:pagination(0);'>First </a></li>";
			$anc .= "<li class='next'><a href='javascript:pagination($previous);'>Previous </a></li>";
		}

		## If you dont want the numbers just comment this block ###	
		$norepeat = 4;//no of pages showing in the left and right side    
		//of the current page in the anchors      
		$j = 1;
		$anch = "";
		for ( $i = $page_showing; $i>1; $i-- ) {
			$fpreviousPage = $i - 1;
			$page = ceil( $fpreviousPage*$recpage ) - $recpage;
			$anch = "<li><a href='javascript:pagination($page);'>$fpreviousPage </a></li>".$anch;
			if ( $j == $norepeat ) break;
			$j++;
		}
		$anc .= $anch;
		$anc .= "<li class='active'>".$page_showing."</li>";
		$j = 1;
		for ( $i = $page_showing; $i<$total_page; $i++ ) {
			$fnextPage = $i + 1;
			$page = ceil( $fnextPage*$recpage ) - $recpage;
			$anc .= "<li><a href='javascript:pagination($page);'>$fnextPage </a></li>";
			if( $j == $norepeat ) break;
			$j++;
		}
		############################################################
		
		if( $next >= $numrows ) {
			$anc .= "<li class='previous-off'>Next</li>";
			$anc .= "<li class='previous-off'>Last</li>";
		} else {
			$anc .= "<li class='next'><a href='javascript:pagination($next);'>Next </a></li>";  
			$anc .= "<li class='next'><a href='javascript:pagination($last);'>Last </a></li>";
		}
        
		$anc .= "</ul>";
		
		$this->anchors = $anc;

		$this->total = "Page : $page_showing <i> Of </i> $total_page . Total Records Found: $numrows";
	}
 
}

?>

Now Remember we mentioned earlier that we will be using a modal by codrops for this tutorial,this is how the modal,onclick let say a button element,the clicked element transforms into a popup window,the effect is very cool. So we will be using this modal for our add,edit and delete actions of our table. This is the html structure of the modal.

<div  class="morph-button morph-button-modal morph-button-modal-2 morph-button-fixed">
 
	//onclick of this button
	<button type="button">ADD</button>
	<div class="morph-content">
		<div>
    
		//transforms into s popup window having the for elements below
			<div class="content-style-form content-style-form-1">
				<span class="icon icon-close">Close the dialog</span>
				<h2>Add Data</h2>
				<form id="form1" method="post" >
					<p><label>Name</label><input type="text" id="add_name" name="add_name" /></p>
					<p><label>Address</label><input type="text" id="add_address" name="add_address" /></p>
					<p><label>Exam No. (format: AA000)</label><input type="text" id="add_exam_no" name="add_exam_no" /></p>
					<p><input type="submit" value="Add" /></p>
					<input type="hidden" id="action" name="action" value="add" />
				</form>
			</div>
		
		</div>
	</div>
	
</div>

As indicated in the above codes, onclick of the element button,transforms itself to a popup window with displaying all the elements inside the form. Its a pretty neat javascript. So let move on to the next file.

5. tablepage.php
For the sake of readability we will break the codes of this file in parts. First the javascript code on top of the file

<script>
	
	function pagination(page){
		var search = $("input#fieldSearch").val();
		var record = $("select#pageRecord").val(); 
		if (search! == ""){
			dataString = 'starting='+page+'&name='+search+'&perpage='+record+'&random='+Math.random(); 
		} else {
			dataString = 'starting='+page+'&perpage='+record+'&random='+Math.random();
		}

		$.ajax({
			url: "tablePage.php",
			data: dataString,
			type: "GET",
			success: function(data)
			{
				$('#divPageData').html(data);
			}
		});
	}

	function loadData() 
	{
		var dataString;
		var search = $("input#fieldSearch").val();
		var record = $("select#pageRecord").val();
		dataString = 'name='+search+'&perpage='+record;

		$.ajax({
			url: "tablePage.php",
			type: "GET",
			data: dataString,
			success: function(data)
			{
				$('#divPageData').html(data);
			}
		});
	}

	$('#students tr:even:not(#nav):not(#total)').addClass('even');
	$('#students tr:odd:not(#nav):not(#total)').addClass('odd') 

	$("form#form1").submit(function(){
        var vId = $("input#edit_id").val();                 
        var vName = $("input#edit_name").val();               
		var vAddress = $("input#edit_address").val();          
		var vExam = $("input#edit_exam_no").val();                      
		var myRegExp=/^[A-Z]{2}\d{4}\b/;                  

		if ((vName=="")||(vAddress == "")||(vExam == "")){
			alert("Please complete the missing field(s)");
			$("input#edit_name").focus();
			return false;
		}
		else if( !myRegExp.test(vExam)){
			alert ('Invalid Format for Exam No.');
			$("input#edit_exam_no").focus();
			return false;
		}
		else{
			$.ajax({
				url: "proses_data2.php",
				type: $(this).attr("method"), 
				data: $(this).serialize(), 
				dataType: 'json', 
				success: function(response){
					if(response.status == 3) 
					{
						alert("Data Successfully Updated");
						$(".morph-content").hide(2000);				  
						loadData();
					}
					else if(response.status==1)
					{
						alert("Please complete the missing field(s)");
						$("input#add_name").focus();
					}
					else if(response.status==2)
					{
						alert("Invalid Format for Exam No.");
						$("input#add_exam_no").focus();
					}
					else
					{
						alert("Data update unsccessful");
					}
				}
			});
        
		return false;
		
		}
		
		return false;
		
	});

	$("form#form2").submit(function(){
        $.ajax({
			url: "process_data.php",
			type: $(this).attr("method"), 
			data: $(this).serialize(), 
			dataType: 'json', 
			success: function(response){
				if(response.status == 1) 
				{
					alert("Data Successfully Delected");
					$(".morph-content").hide(2000);				  
					loadData();
				}
				else
				{
					alert("Data Failed to Delete");
				}
			}
        });
        
		return false;
		
    });  

</script>

The pagination(page) function handle the paginated bottons actions that are on the right and left of the current page been view below the table. Now this loadData() function is used to refresh the table after every add,edit and delete action has taken place. The second part of the file.

<?php 

	include( 'pagination_class.php' );
	include( 'connect.php' );

	if ( isset ( $_GET['name'] ) and !empty( $_GET['name'] ) ) {
		$name = $_GET['name'];
		$sql = "select * from students where name like '%$name%'";
	}
	else {
		$sql = "select * from students order by id";
	}

	if ( isset( $_GET['starting'] ) ) { //starting page
		$starting=$_GET['starting'];
	} else {
		$starting = 0;
	}

	$recpage = $_GET['perpage'];

	$obj = new pagination_class( $sql, $starting, $recpage );
	$result = $obj->result;

?>;

<div id="page_contents">
	<div id="addDiv">
		<!-- inside this div hold the html for our add modal -->
	</div>
	
	<div id="student_wrap"> 	
		<table style="position:relative" id="students"  width="100%" >
			<tr>
				<th>Sl No</th><th>Student Name</th><th>Address</th><th>Exam No.</th><th>Action</th>
			</tr>
			<?php if ( mysqli_num_rows( $result ) != 0 ) {
			while( $data = mysqli_fetch_array( $result ) ) { ?>
			<tbody>
				<tr>
					<td><?php echo $data['id']; ?></td>
					<td><?php echo $data['name']; ?></td>
					<td><?php echo $data['address']; ?></td>
					<td><?php echo $data['exam_no']; ?></td>
					<td><!-- inside this td holds the html for both our edit and delete modal --> </td> 
				</tr>
			</tbody> <?php } ?>
			
			<tfoot>
				<tr id="nav">
					<td colspan="5"><div><?php echo $obj->anchors; ?></div></td>
				</tr>
				<tr id="total">
					<td colspan="5"><?php echo $obj->total; ?></td>
				</tr><?php } else { ?>
				<tr>
					<td align="center" colspan="5">No Data Found</td>
				</tr>
			</tfoot><?php } ?>
		</table>
	</div>
	
</div>

Notice that we mentioned three modal that contain a form element one for the edit,delete and add button,but we the javascript section of this file only handles two form id's on submit (form1 and form 2). The reason is because instead of writing a third function to handle the third form,we assign the edit and delete form same form id('form1') then for the delete form id('form2'),by doing this we reduce the amount of script and keep things neat.For our edit form,errors like empty input fields and invalid exam no format is handled by javascript as you can see above but for our add form the errors are handled by the server in this file process_data.php file and return back to this file with the ajax success response. Let move on to our next file.

6. process_data.php

<?php 
	include( 'connect.php' );

	if( isset( $_POST['action'] ) && $_POST['action'] == "add" ) {
		$name = $_POST['add_name'];
		$address = $_POST['add_address'];
		$exam_no = $_POST['add_exam_no']; 
		$pattern = "/^[A-Z]{2}\d{4}\b/";

	if ( ( $name == "" ) || ( $address == "" ) || ( $exam_no == "" ) ) {
		echo '{"status":"1"}';
		exit;
	}
	else if ( !preg_match( $pattern, $exam_no ) ) {
		echo '{"status":"2"}';
		exit;
	} else {
		$add = mysqli_query( $con, "INSERT INTO students(name,address,exam_no)
			VALUES('$name','$address','$exam_no')" ) or die ("error");
			echo '{"status":"3"}';
			exit;
		}
	}
	else if ( isset( $_POST['action'] ) && $_POST['action'] == "update" ) {
		$id = $_POST['edit_id'];
		$name = $_POST['edit_name'];
		$address = $_POST['edit_address'];
		$exam_no = $_POST['edit_exam_no'];

		$update = mysqli_query( $con, "UPDATE students SET
			name='$name',address='$address',exam_no='$exam_no'
			WHERE id='$id'") or die ("error");
		echo '{"status":"3"}';
		exit;
	}
	else if ( isset( $_POST['action'] ) && $_POST['action'] == "delete" ) {
		$id = $_POST['delete_id'];
		$delete = mysqli_query( $con, "delete from students where id='$id'" );
		if( mysqli_affected_rows( $con ) == 1 ) {
			echo '{"status":"1"}';
		} else {
			echo '{"status":"0"}';
		}
		exit;
	}
?>

Please note that the Javascript and the css files used by our modal is not included,just to keep our tutorial precise to the point.To get a full list of this files use the download button. Also fill free to test our the demo. If you have any questions regarding this tutorial ,please use our comment form and we will try to provide a reply ASAP. And if you have any suggestions that may improve this example due let us know.
Thanks.

27 thoughts on “A fancy add,edit,delete ajax paginated mysql table

  1. seem that a problem with add page. when i tried to input the ‘Exam No’ as suggested AA000 it not allowed to complete adding the record
    it said the invalid exam number. then I tried many times with other format still the same problem.
    could you please help to solve this issue 🙁

    • This is the regular expression used to match the exam no.

      /^[A-Z]{2}\d{4}\b/
      

      it first match two capital letters and then 4 digit number following,make sure no space before and after.

    • hi i got same problem i am trying Two capital letter 4 digit like this PP4444 , BB8888, but not working please give me advise really important concept for my project

      • when i edit the data and submit i am getting parsing json request failed. can u please help with this. Thanks in advance

  2. Thank you Admin for a nice and very cool php data search, pagination, edit and delete… But i can’t edit or add my data by this code… can you help me?

  3. Dear Admin please help me for search by unicode utf 8. There is no way I can do it. We change my database Collation utf 8, row type utf8_general_ci and utf8_unicode_ci but not work unicode search this form. Can you any help me for this problem?

    Thank and Regards
    Sahriar

  4. Sorry for come back…

    Dear Admin are you help me a new problem? Listen is it possible to make a link in your data search form for click to view more details or full details page?
    Such as
    You used this code for data display
    ……………………………………………………..

    ……………………………………………………..

    now i want this for data display with link
    ……………………………………………………..
    <a href="details_page.php?eid=”>
    ……………………………………………………..

    If you help for this task i can success my job. Please help or sugest me where i will get my solution.

    Thanks and regard
    Sahriar

  5. Sorry for missing the code
    here your code
    -td- ?php echo $data[‘name’]; ?>-/td-

    now i want this for data display with link
    -a- href=”details_page.php?eid=—-?php echo $data[‘id’];?>”>—?php echo $data[‘name’];?>—/a>

    • For the link,do something like this

      -a href="details_page.php?id=<?php echo $data['id']; ?>&name_value=<?php echo $data['name'] ?>">Link-/a>
      
  6. Hi! I am using your code that served very well! Thank you.

    I made a change in the index and table to insert a search by dates in MySQL:

    7
    30
    7
    Days Before

    But I can not post the result in the select:
    $day = $_GET[‘day’];
    $sql = “select * from note WHERE data_an BETWEEN SYSDATE() – INTERVAL ‘$day’ DAY AND SYSDATE() order by id”;

    Can you give any suggestions? Thanks Admin.

    • Try this,assuming data_an is the column where the dates are stored.

      “select * from note WHERE data_an BETWEEN date_sub( NOW(),  INTERVAL {$day} DAY ) AND NOW() order by id”;
      
  7. Barth, the query you went perfect works in MySQL console, but the variable $ day is always empty. I think the problem is to declare the variable:

    var search = $(“input#fieldSearch”).val();
    var day = $(“select#daysBefore”).val();
    var record = $(“select#pageRecord”).val();
    if (search!==””){
    dataString = ‘starting=’+page+’&name=’+search+’&day=’+ day+’&perpage=’+ record+’&random=’+Math.random();)
    Or the index of the form:

    7
    30
    7
    Days Before

    Thank you.

  8. Not yet .. The $day = $_GET[‘day’]; brings nothing of the index.

    • @amaral

      This is what you will do, just zip the whole files and give me a link where I can download it,so I can take a look at the whole script and figure out the issue.

  9. Good news Barth! I managed to implement the clause date.

    I changed dataString ( dataString = ‘day=’+ day + ‘&name=’+ search + ‘&perpage=’+ record; ) in pagination.js. With this, the variable $day worked in the query suggested by you.

    Thanks for your support and have provided this code.

  10. it always keeps on insert missing data using mysql

  11. HI,
    I still have the same problem, I can not insert a No Exam.
    Are you a solotion for the regular expression?
    thank you

  12. Hi,
    when i edit the data and submit i am getting parsing json request failed. can u please help with this. Thanks in advance

  13. I am getting the “Invalid Format for Exam No.” error even after using the correct format for Exam Number like RS4562. I have looked through all the comments and there was someone with a similar problem, I don’t know if they got help….

  14. Oh My God..I cant believe your code are awesome..cos i have been searching for that more than 2 weeks..
    Thanks so much..you have such a great knowledge..

Comments are closed.