Pagination using PHP and MySQL – 2
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> of <strong>".$totnumpages."</strong> Goto: </font>"; if ($page > 1) { $link .= "<a href=".$_SERVER['PHP_SELF']."?page=1$getvars class='".$class."' title='Jump to First Page'>First</a> | "; $prev = $page - 1; $link .= "<a href=".$_SERVER['PHP_SELF']."?page=".$prev."$getvars class='".$class."' title='Goto Previous Page'>Previous</a><span class='".$class."'> | </span>"; } else { $link .= "<span class='".$class."' title='Jump to First Page'>First</span> | <span class='".$class."' title='Goto Previous Page'>Previous | </span>"; } if ($page < $totnumpages) { $next = $page + 1; $link .= "<a href=".$_SERVER['PHP_SELF']."?page=".$next."$getvars class='".$class."' title='Goto Next Page'>Next</a> | "; $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> | <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 !!!
Hi, phpguru.
A few years ago I worked with a code that you developed … I don’t remember the project’s name.
The only thing I remember is that when I write the table’s name in the url, the CRUD of the table appears.
Could you share me this project, please?
sorry bro, I couldn’t able to find that script.