Pagination using PHP and MySQL – 1
Below is simple pagination script using PHP and MySQL. Pagination will be in following format. In this pagination script I have used form to submit the page drop down, so if you want to add one new form in your page then do check the form name and form id, so that it will not conflict with pagination form.
Page: 1 of 10 Goto
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 pagingGoto($sql, $page, $limit, $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 .= "<table><form name='frmpaging' action='".basename($_SERVER['PHP_SELF'])."' method='post'><tr><td><font face='verdana' size='1'>Page: <strong>".$page."</strong>&nbsp;of&nbsp;<strong>".$totnumpages."</strong>&nbsp;&nbsp;&nbsp;Goto:&nbsp;</font>"; $link .= "<select name='page' onchange='javascript: document.frmpaging.submit();' class=".$class.">"; for ($i=1;$i<=$totnumpages;$i++) { if ($page == $i) $sel = " selected "; else $sel = ""; $link .= "<option value='".$i."' '".$sel."'>".$i."</option>"; } $link .= "</select>"; $link .= "</td></tr></form></table>"; } $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($_POST['page']); $limit = 20; // records per page $class = "paging"; // pagination links CSS $result_array = pagingGoto($sql, $page, $limit, $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 !!!
pretty good blog,come again next time。