php sample script for pagination
Following link can help you, its has easy to use description too
http://www.strangerstudios.com/sandbox/pagination/diggstyle.php
If you would like to have a tutorial then you should read on
Simple PHP Pagination script
This is a mix of HTML and code but it's pretty basic, easy to understand and should be fairly simple to decouple to suit your needs I think.
try {
// Find out how many items are in the table
$total = $dbh->query('
SELECT
COUNT(*)
FROM
table
')->fetchColumn();
// How many items to list per page
$limit = 20;
// How many pages will there be
$pages = ceil($total / $limit);
// What page are we currently on?
$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
'options' => array(
'default' => 1,
'min_range' => 1,
),
)));
// Calculate the offset for the query
$offset = ($page - 1) * $limit;
// Some information to display to the user
$start = $offset + 1;
$end = min(($offset + $limit), $total);
// The "back" link
$prevlink = ($page > 1) ? '<a href="?page=1" title="First page">«</a> <a href="?page=' . ($page - 1) . '" title="Previous page">‹</a>' : '<span class="disabled">«</span> <span class="disabled">‹</span>';
// The "forward" link
$nextlink = ($page < $pages) ? '<a href="?page=' . ($page + 1) . '" title="Next page">›</a> <a href="?page=' . $pages . '" title="Last page">»</a>' : '<span class="disabled">›</span> <span class="disabled">»</span>';
// Display the paging information
echo '<div id="paging"><p>', $prevlink, ' Page ', $page, ' of ', $pages, ' pages, displaying ', $start, '-', $end, ' of ', $total, ' results ', $nextlink, ' </p></div>';
// Prepare the paged query
$stmt = $dbh->prepare('
SELECT
*
FROM
table
ORDER BY
name
LIMIT
:limit
OFFSET
:offset
');
// Bind the query params
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
// Do we have any results?
if ($stmt->rowCount() > 0) {
// Define how we want to fetch the results
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$iterator = new IteratorIterator($stmt);
// Display the results
foreach ($iterator as $row) {
echo '<p>', $row['name'], '</p>';
}
} else {
echo '<p>No results could be displayed.</p>';
}
} catch (Exception $e) {
echo '<p>', $e->getMessage(), '</p>';
}
PHP pagination with single sql query
As your first query is only looking for the number of rows, you can optimize this by selecting the number of rows.
$query= "SELECT COUNT(*) FROM $database.$table WHERE `id` <> '$id'";
This way the SQL server will just return the number of rows, not the matching rows.
Edit
You can also load the whole data and store it in a session variable. That way you load the data only once and can display the relevant part without another query.
Is there any simple pagination framework for php which can be easily implementable?
Below is the file named paging.inc.php
You just need to pass the where class to the query and just need to get the count only and name the count varibale same as in the file then you will need to set the records per page and other parameters.
After this you need to fire the query which will bring the records with the limit variable from the file and this can be used for sorting the columns in the listing.
<?
//CODE FOR PAGING
if(!isset($num_totrec)) $num_totrec = $db_recs[0]["tot"];
//$num_totrec SHOULD BE PASSED
if(!isset($pg_limit) && empty($pg_limit))
$pg_limit = $PAGE_LIMIT; //page limit
if(!isset($rec_limit) && empty($rec_limit))
$rec_limit = $REC_LIMIT; //record limit
if($_REQUEST["TotalRecords"]!="")
$rec_limit = $_REQUEST["TotalRecords"];
$num_tmp = 0;
$var_flg = "0";
$var_limit = "";
$num_limit = 0;
$var_filter = "";
if($_GET[start]=='') {$_GET[start] = 1;$start=1;}
if(($_GET[start]-1)*$rec_limit > $num_totrec)
{
$_GET[start]=1;
$start=1;
}
if(isset($tempvar) && !empty($tempvar))
{
if($stat==1 && $tempvar == "true") $stat=0;
else if($stat==0 && $tempvar == "true") $stat=1;
else $stat=1;
}
$sort_order = ($stat==1)? $asc_order:$desc_order;
$sort_img = "<img src='$sort_order' border='0'>";
$var_filter = "";
$var_filter = "&stat=$stat";
//CODE FOR COLUMN SHOULD SORT ASCENDING/DESCENDING - END
//CHANGE THIS CODE WITH SUITABLE VARIABLES
if(isset($ptype)) $var_filter.= "&ptype=".$ptype."&action=Section";
foreach ($_GET as $key=>$val)
{
if($key != "stat" && $key != "start" && $key != "nstart" && $key != "tempvar" && $key != "sorton")
{
if(is_array($val))
{
for($k=0;$k<count($val);$k++)
{
$var_filter .= '&'.$key.'[]=' . $val[$k];
}
}else if($key == "keyword"){
$var_filter.= "&$key=". stripcslashes($val);
}
else
$var_filter.= "&$key=$val";
}
}
foreach ($_POST as $key=>$val)
{
if($key != "stat" && $key != "start" && $key != "nstart" && $key != "tempvar" && $key != "sorton")
{
if(is_array($val))
{
for($k=0;$k<count($val);$k++)
{
$var_filter .= '&'.$key.'[]=' . $val[$k];
}
}else if($key == "keyword"){
$var_filter.= "&$key=". stripcslashes($val);
}
else
$var_filter.= "&$key=$val";
}
}// end
if(isset($month_val) && $month_val != "") $var_filter.= "&month_val=$month_val";
if(isset($year_val) && $year_val != "") $var_filter.= "&year_val=$year_val";
if(isset($action) && $action != "") $var_filter.= "&action=$action";
//SET Extra querystring variables to pass from here
//$var_extra can be attached with the links for this purpose
if(isset($start)){
$num_limit = ($start-1)*$rec_limit;
$var_limit = " LIMIT $num_limit,$rec_limit";
}else $var_limit = " LIMIT 0,$rec_limit";
if(!isset($nstart)){
if($num_totrec){ //if recs exists!!
if($rec_limit>$num_totrec){
$num_pgs = 1;
$var_flg = "2";
}else{
$num_loopctr =0;
$num_loopctr = ceil($num_totrec/$rec_limit);
if($pg_limit>$num_loopctr){
$num_pgs = $num_loopctr;
$var_flg = "2";
}else{
$num_pgs = $pg_limit;
if($num_totrec<=($rec_limit*$pg_limit)) $var_flg = "2";
else $var_flg = "1";
}
}
$var_link = "";
$var_prevlink ="";
//if sorting is set
$var_sort_link="";
if(isset($sorton)) $var_sort_link = "&sorton=$sorton";
$var_prevlink ="<font size=1 color=black> |";
for($i=1;$i<=$num_pgs;$i++)
{
IF($i==1)
$var_link.= "<font size=1 color=red>$i</font> | ";
else
$var_link.= "<a href=\"$var_self$PHP_SELF?nstart=1&start=$i$var_filter$var_sort_link$var_extra\"><font size=1 color=black>$i</font></a> | ";
}
if($var_flag !="0" and $var_flg!="2"){ $var_link .= " > <a href=\"$var_self$PHP_SELF?nstart=2&start=$i$var_filter$var_filter$var_sort_link$var_extra\"><font size=1 color=black>next</font></a>"; }else {$var_link .= " </font>";
}
$page_link = "";
$page_link = "$var_prevlink $var_link";
}else{
//IF NO RECORDS EXISTS!!
$var_link="";
}
}else{ //if nstart is set
if($num_totrec){ //if recs exists!!
$num_loopctr =0;
$num_rem_rec = 0;
$num_rem_rec = ($num_totrec-(($nstart-1)*$rec_limit*$pg_limit));
$num_loopctr = ceil($num_rem_rec/$rec_limit);
$num_tmp = $rec_limit*$nstart*$pg_limit;
if($num_tmp>$num_totrec){
$num_pgs = $num_loopctr;
$var_flg = "2";
}else{
$num_pgs = $pg_limit;
if($num_totrec==($nstart*$rec_limit*$pg_limit)) $var_flg = "2";
else $var_flg = "1";
}
$var_link = "";
$var_prevlink ="";
//if sorting is set
$var_sort_link="";
if(isset($sorton)) $var_sort_link = "&sorton=$sorton";
$num_prevnstart = 0;
$num_prevstart = 0;
$num_prevnstart = $nstart-1;
$num_prevstart = ($nstart*$pg_limit)-$pg_limit;
$num_tmp = ($num_totrec/$rec_limit);
if($nstart<=1) $var_prevlink ="<font size=1 color=black> |";
else $var_prevlink ="<a href=\"$var_self$PHP_SELF?nstart=$num_prevnstart&start=$num_prevstart$var_filter$var_sort_link$var_extra\"><font size=1 color=black>prev</font></a> <font size=1 color=black>< |</font>";
for($i=1;$i<=$num_pgs;$i++)
{
$num_start = $num_prevstart+$i;
$num_nstart = $nstart+1;
IF($start==$num_start)
$var_link.= "<font size=1 color=red>$num_start</font> | ";
else
$var_link.= "<a href=\"$var_self$PHP_SELF?nstart=$nstart&start=$num_start$var_filter$var_sort_link$var_extra\"><font size=1 color=black>$num_start</font></a> | ";
}
$num_start++;
if($var_flag!="0" and $var_flg!="2"){ $var_link .= " ><a href=\"$var_self$PHP_SELF?nstart=$num_nstart&start=$num_start$var_filter$var_sort_link$var_extra\"><font size=1 color=black>next</font></a></font>"; }else {$var_link .= "<font size=1 color=black> </font>";
}
$page_link = "";
$page_link = "$var_prevlink $var_link";
}else{
//IF NO RECORDS EXISTS!!
$var_link="";
}
}
//if set the paging variables
if(isset($nstart)) $var_pgs = "&nstart=$nstart&start=$start"; //attach this with the sorting links
//CODE FOR PAGING ENDS OVER HERE
?>
Posting a sample code below so you can get what i said in the above description.
This is just a sample how i use it.
$where_arr = array();
if($keyword !=""){
$where_arr[] ="$option like '".$keyword."%'";
}
if(count($where_arr)>0)
$where_clause = " WHERE ".implode(" AND ", $where_arr);
else
$where_clause = '';
$table_clause = " from admin";
$sql = "select count(iAdminId) as tot ".$table_clause.$where_clause;
$rs_sql = $sqlObj->select($sql);
$num_totrec = $rs_sql[0]['tot'];
include("gen_pagingmsg.inc.php");
if($sorton != "")
{
switch ($sorton)
{
case "1":
$sort = "vUserName";
if($stat!=1) $sort .= " DESC";
break;
case "2":
($stat==1)? $sort = "vFirstName, vLastName" : $sort = "vFirstName desc, vLastName DESC";
break;
case "3":
$sort = "vEmail";
if($stat!=1) $sort .= " DESC";
break;
case "4":
$sort = "dLastAccess";
if($stat!=1) $sort .= " DESC";
break;
case "5":
$sort = "iTotLogin";
if($stat!=1) $sort .= " DESC";
break;
case "6":
$sort = "eStatus";
if($stat!=1) $sort .= " DESC";
break;
default:
$sort ="vUserName";
}
}else {
$sort ="vUserName";
}
$sql = "select iAdminId, concat(vFirstName, ' ', vLastName) as vName, vUserName, vEmail, dLastAccess, iTotLogin, eStatus ".$table_clause.$where_clause." order by ".$sort.$var_limit;
//echo "<br>".$sql;
$db_sql = $sqlObj->select($sql);
Below is the gen_pagingmsg.inc.php
<?
# =========================================================================
# Paging Paging comes from this File. Don't Remove this below line.
# =========================================================================
//echo $num_totrec;
//ECHO $rec_limit ;
if($ADMIN_SHOWPAGING_TOP=="N" && $ADMIN_SHOWPAGING_BOTTOM=="N")
$rec_limit=$num_totrec;
include("paging.inc.php");
# =========================================================================
//$keyword = stripcslashes($keyword);
if($keyword!="")
{
$var_msg="Your search for #keyword# has found #num_totrec# matches:";
$var_msg=str_replace("#keyword#","<font color=#000000>$keyword</font>",$var_msg);
$var_msg=str_replace("#num_totrec#","<font color=#000000>$num_totrec</font>",$var_msg);
}
if(!isset($start))
$start = 1;
$num_limit = ($start-1)*$rec_limit;
$startrec = $num_limit;
$lastrec = $startrec + $rec_limit;
$startrec = $startrec + 1;
if($lastrec > $num_totrec)
$lastrec = $num_totrec;
if($num_totrec > 0 )
{
$recmsg = "Showing ".$startrec." - ".$lastrec." Records Of ".$num_totrec;
}
else
{
$recmsg="Sorry !... No Records Found";
}
?>
This file basically provide you the message and paging html which you can keep just above or below your listing
Pagination in PHP (paginate comments)
or works much more differently from ||
Try using || instead
ALSO
You are setting $page = '' on every page load no matter what. so it is always page 1
if(($page='') or !is_numeric($page)){ //!!!ahhh
$page = 1;
}
Change that to:
if(($page == '') || !is_numeric($page)){
$page = 1;
}
php get the post data when using pagination script
No, it's not possible. When a form is submitted (with method=post) to the server, that's one POST request. If you want to make another POST request, you need to make another POST request. If you click a link, that's not a POST request.
I'm assuming your scenario is something like a search form, which is submitted via POST and which returns several pages of results. In this case, POST is misused anyway. An HTTP POST request should be used for altering data on the server, like registering a new user or deleting a record. Just a search form is not data alteration, it's just data retrieval. As such, your form should be method=get. That will result in a URL with the form values as query parameters:
mainPage.php?search=foobar
You can then trivially create pagination URLs from that:
printf('<a href="mainPage.php?%s">...', http_build_query(array('page' => $i) + $ _GET));
Which will result in:
mainPage.php?search=foobar&page=2
This way all your requests are self contained GET queries.
How to create pagination with PDO PHP
Here is a simple approach to pagination:
<?php
$limit = 2;
$query = "SELECT count(*) FROM kategori";
$s = $db->query($query);
$total_results = $s->fetchColumn();
$total_pages = ceil($total_results/$limit);
if (!isset($_GET['page'])) {
$page = 1;
} else{
$page = $_GET['page'];
}
$starting_limit = ($page-1)*$limit;
$show = "SELECT * FROM kategori ORDER BY id DESC LIMIT ?,?";
$r = $db->prepare($show);
$r->execute([$starting_limit, $limit]);
while($res = $r->fetch(PDO::FETCH_ASSOC)):
?>
<h4><?php echo $res['id'];?></h4>
<p><?php echo $res['nama_kat'];?></p>
<hr>
<?php
endwhile;
for ($page=1; $page <= $total_pages ; $page++):?>
<a href='<?php echo "?page=$page"; ?>' class="links"><?php echo $page; ?>
</a>
<?php endfor; ?>
Simple pagination with only next and previous buttons
<?php
error_reporting(E_ALL ^ E_NOTICE);
// How many adjacent pages should be shown on each side?
$adjacents = 5;
$query = $mysqli->query("select COUNT(*) as num from posts order by id desc");
$total_pages = mysqli_fetch_array($query);
$total_pages = $total_pages['num'];
$limit = 12; //how many items to show per page
$page = $_GET['page'];
if($page)
$start = ($page - 1) * $limit; //first item to display on this page
else
$start = 0; //if no page var is given, set start to
/* Get data. */
$result = $mysqli->query("select * from posts order by id desc LIMIT $start, $limit");
/* Setup page vars for display. */
if ($page == 0) $page = 1; //if no page var is given, default to 1.
$prev = $page - 1; //previous page is page - 1
$next = $page + 1; //next page is page + 1
$lastpage = ceil($total_pages/$limit); //lastpage is = total pages / items per page, rounded up.
$lpm1 = $lastpage - 1; //last page minus 1
$pagination = "";
if($lastpage > 1)
{
$pagination .= "<div class=\"pagination\">";
//previous button
if ($page > 1)
$pagination.= "<a href=\"videos-$prev.html\">« previous</a>";
else
$pagination.= "<span class=\"disabled\">« previous</span>";
//next button
if ($page < $lastpage)
$pagination.= "<a href=\"videos-$next.html\">next »</a>";
else
$pagination.= "<span class=\"disabled\">next »</span>";
$pagination.= "</div>\n";
}
$q = $mysqli->query("select * from posts order by id desc limit $start,$limit");
while($row=mysqli_fetch_assoc($q)){
// LOOP Code
} echo $pagination;?>
Related Topics
How to Do Single Sign-On with PHP
How to Get CSV File to Download on Ie? Works on Firefox
Symfony2 Create Own Encoder for Storing Password
Get Characters After Last/In Url
Is_Null($X) VS $X === Null in PHP
How to Get Email and Their Attachments from PHP
Redirecting from Http to Https with PHP
Best Way to Store Passwords in MySQL Database
Stop Data Inserting into a Database Twice
Use of Initialization Vector in Openssl_Encrypt
PHP Prepend Associative Array with Literal Keys
Get Last Executed Query in PHP Pdo