Simple Ajax Pagination Using PHP And MYSQL

Simple Ajax Pagination Using PHP And MYSQL

Ajax pagination has become basic requirement of all modern web applications. I’ve designed this tutorial to absolute ajax beginners who want to learn “How ajax works and how to implement a pagination with Ajax, Jquery, PHP and MYSQL with loading effect. The tutorial contains mainly two files. One of them fetches data from database and another gives the pagination numbers.

Simple Ajax Pagination Using PHP And MYSQL

Simple Ajax Pagination Using PHP And MYSQL

As like always first of all we make the basic mark up of tutorial.

Style.css

/*css file*/
.pagin {
padding: 10px 0;
font:bold 11px/30px arial, serif;
}
.pagin * {
padding: 2px 6px;
color:#0A7EC5;
margin: 2px;
border-radius:3px;
}
.pagin a {
		border:solid 1px #8DC5E6;
		text-decoration:none;
		background:#F8FCFF;
		padding:6px 7px 5px;
}

.pagin span, a:hover, .pagin a:active,.pagin span.current {
		color:#FFFFFF;
		background:-moz-linear-gradient(top,#B4F6FF 1px,#63D0FE 1px,#58B0E7);

}
.pagin span,.current{
	padding:8px 7px 7px;
}
.content{
	padding:10px;
	font:bold 12px/30px gegoria,arial,serif;
	border:1px dashed #0686A1;
	border-radius:5px;
	background:-moz-linear-gradient(top,#E2EEF0 1px,#CDE5EA 1px,#E2EEF0);
	margin-bottom:10px;
	text-align:left;
	line-height:20px;
}
.outer_div{
	margin:auto;
	width:600px;
}
#loader{
	position: absolute;
	text-align: center;
	top: 75px;
	width: 100%;
	display:none;
}

Now come to index.php. This file performs two operations. When it gets the action parameter in get or post and if the value of action is ajax then fetches the data from database according to page number otherwise executes the else part.
Index.php

<?php
$action = (isset($_REQUEST['action'])&& $_REQUEST['action'] !=NULL)?$_REQUEST['action']:'';
if($action == 'ajax'){
	/* Connect To Database*/
	$dbname = 'csc';
	$link = mysql_connect("localhost","root","") or die("Couldn't make connection.");
	$db = mysql_select_db($dbname, $link) or die("Couldn't select database");

	include 'pagination.php'; //include pagination file

	//pagination variables
	$page = (isset($_REQUEST['page']) && !empty($_REQUEST['page']))?$_REQUEST['page']:1;
	$per_page = 4; //how much records you want to show
	$adjacents  = 4; //gap between pages after number of adjacents
	$offset = ($page - 1) * $per_page;

	//Count the total number of row in your table*/
	$count_query   = mysql_query("SELECT COUNT(id) AS numrows FROM messages");
	$row     = mysql_fetch_array($count_query);
	$numrows = $row['numrows'];
	$total_pages = ceil($numrows/$per_page);
	$reload = 'index.php';

	//main query to fetch the data
	$query = mysql_query("SELECT * FROM messages ORDER BY RAND() LIMIT $offset,$per_page");

	//loop through fetched data
while($result = mysql_fetch_array($query)){
	echo '<div class="content">'.$result['message'].'</div>';
}
echo paginate($reload, $page, $total_pages, $adjacents);
} else{
?>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Simple Ajax Pagination With PHP And MySql</title>
<script type="text/javascript" src="jquery-1.5.2.min.js"></script>
<link media="screen" href="style.css" type="text/css" rel="stylesheet">
<script type="text/javascript">
	$(document).ready(function(){
		load(1);
	});

	function load(page){
		$("#loader").fadeIn('slow');
		$.ajax({
			url:'index.php?action=ajax&page='+page,
			success:function(data){
				$(".outer_div").html(data).fadeIn('slow');
				$("#loader").fadeOut('slow');
			}
		})
	}
</script>
</head>
<body>
<div id="loader"><img src="loader.gif"></div>
<div class="outer_div"></div>
</body>
</html>
<?php }?>

Now we make the pagination that is pagination.php. This file gets 4 parameters. Total number of pages, current page no., adjacent, reload page or we can say current page on which pagination will be performed.
pagination.php

<?php
function paginate($reload, $page, $tpages, $adjacents) {
	$prevlabel = "&lsaquo; Prev";
	$nextlabel = "Next &rsaquo;";
	$out = '<div class="pagin green">';

	// previous label

	if($page==1) {
		$out.= "<span>$prevlabel</span>";
	} else if($page==2) {
		$out.= "<a href='javascript:void(0);' onclick='load(1)'>$prevlabel</a>";
	}else {
		$out.= "<a href='javascript:void(0);' onclick='load(".($page-1).")'>$prevlabel</a>";

	}

	// first label
	if($page>($adjacents+1)) {
		$out.= "<a href='javascript:void(0);' onclick='load(1)'>1</a>";
	}
	// interval
	if($page>($adjacents+2)) {
		$out.= "...\n";
	}

	// pages

	$pmin = ($page>$adjacents) ? ($page-$adjacents) : 1;
	$pmax = ($page<($tpages-$adjacents)) ? ($page+$adjacents) : $tpages;
	for($i=$pmin; $i<=$pmax; $i++) {
		if($i==$page) {
			$out.= "<span class='current'>$i</span>";
		}else if($i==1) {
			$out.= "<a href='javascript:void(0);' onclick='load(1)'>$i</a>";
		}else {
			$out.= "<a href='javascript:void(0);' onclick='load(".$i.")'>$i</a>";
		}
	}

	// interval

	if($page<($tpages-$adjacents-1)) {
		$out.= "...\n";
	}

	// last

	if($page<($tpages-$adjacents)) {
		$out.= "<a href='javascript:void(0);' onclick='load($tpages)'>$tpages</a>";
	}

	// next

	if($page<$tpages) {
		$out.= "<a href='javascript:void(0);' onclick='load(".($page+1).")'>$nextlabel</a>";
	}else {
		$out.= "<span>$nextlabel</span>";
	}
	$out.= "</div>";
	return $out;
}
?>

The code snippets given here are only for understanding purpose. To understand the Ajax Pagination more, you can view the demo or download the tutorial in zip file.

17 Responses to “Simple Ajax Pagination Using PHP And MYSQL”
  1. Jitendra Meena says:

    Thanx a lot bro :) , it’s awesome !!!

  2. Ilídio Rocha says:

    hi, thanks for this amazing pagination :)
    How i can use this pagination twice on the same page? For example: 2 divs with some content and each content with pagination.

  3. Vignesh says:

    super…!

  4. Tuwenjia says:

    Thanks for your nice job. I can finally manage my data into pages.

    In each data entry, I add an checkbox. How can I preserve the checked status of these checkboxes when I switch across these ajax pages.

    Someone told me to use cookies, but how exactly? Can you kindly help me out? Thank you!

  5. alaa says:

    This code works fine locally , when i upload it to my server I face 500 (internal server error) ?!

    how can I fix this?

  6. suneth says:

    Thank you very much for the sample Code…

  7. says:

    Hello Manish Jangir,

    This is really an awesome pagination. Loved it very much. Going to use it for the recipe site I am developing.

    Thanks a lot. Cheers :)

  8. mohamed says:

    perfect one , i need to learn how to do that !!

  9. chicco says:

    nice work,please i have one question, if i post more than one value how to give it to ajax script for the pagination

  10. leo says:

    hey! Manish Jangir:

    thanks for your code, i am new to symfony2 framework , i want to use your code for my first project, but i have no idea. could you help me? thank you very much!

  11. GAurav says:

    Thanks I have 20 types of paging php script but it is good to understand Thanks Its help full….

  12. David says:

    thanks, great code. could us a part of in in my website :)