Pagination using PHP and MySQL – 2

Date January 3, 2014 Author By PravinS Category MySql, PHP

Below is simple pagination script using PHP and MySQL. Links will be in following format.
First | Previous | Next | Last

CSS class for pagination link.

	.paging
	{
		font-family:Verdana, Arial, Helvetica, sans-serif;
		font-size: 11px;
		color:#000000;
		text-decoration: none;
	}
	a.paging:link
	{
		font-family:Verdana, Arial, Helvetica, sans-serif;
		font-size: 11px;
		color:#000000;
		text-decoration: underline;
	}
	a.paging:visited 
	{
		FONT-SIZE: 11px; 
		COLOR: #000000; 
		font-family:Verdana, Arial, Helvetica, sans-serif;
		TEXT-DECORATION: underline;
	}
	a.paging:active 
	{
		FONT-SIZE: 11px; 
		COLOR: #000000; 
		font-family:Verdana, Arial, Helvetica, sans-serif;
		TEXT-DECORATION: underline;
	}
	a.paging:hover 
	{
		font-family:Verdana, Arial, Helvetica, sans-serif;
		font-size: 11px;
		color:#FF9900;
		text-decoration: none;
	}

Here are some required functions/methods which are used for Pagination.

	function execute($sql)
	{
		if ($sql!="")
		{
			$result = mysql_query($sql) or die("Error: ".mysql_errno().":- ".mysql_error());
			if ($result)
				return $result;
			else
				return false;
		}
	}

	function recordset($result)
	{
		if ($result)
		{
			while($row = mysql_fetch_assoc($result))
				$data[] = $row;
		}
		return $data;
	}

Pagination function

	function pagingPN($sql, $page, $limit, $getvars, $class)
	{
		if ($page == "")
			$page = 1;
		if ($limit == 0)
			$limit = 1;
		$tsql = $sql;
		$result = mysql_query($tsql) or die("Error: ".mysql_errno().":- ".mysql_error());
		$total = mysql_num_rows($result);
		$totnumpages = ceil($total/$limit);
		if ($offset < 0)
			$offset = 0;
		else          
			$offset = ($page - 1) * $limit;
		$sql = $sql. "  limit $offset, $limit"; 
		$rs = execute($sql);
		$res = recordset($rs);
		$serial_no = ($page - 1) * $limit;
		
		if ($total > 0)
		{
			$link .= "<font face='verdana' size='1'>Page: <strong>".$page."</strong>&nbsp;of&nbsp;<strong>".$totnumpages."</strong>&nbsp;&nbsp;&nbsp;Goto:&nbsp;</font>";
			
			if ($page > 1)
			{
				$link .= "<a href=".$_SERVER['PHP_SELF']."?page=1$getvars class='".$class."' title='Jump to First Page'>First</a>&nbsp;|&nbsp;";
				$prev = $page - 1;
				$link .= "<a href=".$_SERVER['PHP_SELF']."?page=".$prev."$getvars class='".$class."' title='Goto Previous Page'>Previous</a><span class='".$class."'>&nbsp;|&nbsp;</span>";
			}
			else
			{
				$link .= "<span class='".$class."' title='Jump to First Page'>First</span>&nbsp;|&nbsp;<span class='".$class."' title='Goto Previous Page'>Previous&nbsp;|&nbsp;</span>";
			}
			if ($page < $totnumpages)
			{
				$next = $page + 1;
				$link .= "<a href=".$_SERVER['PHP_SELF']."?page=".$next."$getvars class='".$class."' title='Goto Next Page'>Next</a>&nbsp;|&nbsp;";
				$link .= "<a href=".$_SERVER['PHP_SELF']."?page=".$totnumpages."$getvars class='".$class."' title='Jump to Last Page'>Last</a>";
			}
			else
			{
				$link .= "<span class='".$class."' title='Goto Next Page'>Next</span>&nbsp;| <span class='".$class."' title='Jump to Last Page'>Last</span>";
			}
		}		
		$retarr["sql"] = $sql;
		$retarr["records"] = $res;
		$retarr["serial_no"] = $_no;
		$retarr["link"] = $link;
		return $retarr;
	}

Usage of Pagination function
pagingPN() function will return result in array format with SQL query, record set, serial number and pagination link. So just use pagination function like this.

$sql = "SELECT QUERY";
$page = trim($_GET['page']);
$limit = 20; // records per page
$getvars = "&var1=val1&var2=val2"; // query string parameters
$class = "paging"; // pagination links CSS
$result_array = pagingPN($sql, trim($_GET['page']), $limit, $getvars, $class);
$pagination_link = $result_array['link']; // just echo this variable where you want to display the pagination links (First | Previous | Next | Last)
$serial_no = $result_array['serial_no']; // use this variable in while or for loop to display serial numbers of records (use as $serial_no++;)
$records = $result_array['records']; // result set will be in 2 dimensional array format
$sql = $result_array['sql']; // sql is for debugging purpose

May this will be help full !!!

Tags: pagination in php mysql, php mysql pagination, php pagination, php pagination function