Simple PHP Pagination Script

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 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

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

PHP pagination script - duplicate

I created an answer to this feature. Below are my code for future use.

PHP:

<?PHP
          //count the number of rows in db
          function countTags(){
          global $conn, $tags_table;
          $SQL = "SELECT COUNT(tag_id) FROM $tags_table";
          return mysqli_fetch_array(queryDB($SQL))[0];
          }

          //Pager:
          //Show next pages box if $options["NumOfRecords"] is greater than 20
          //We fetching 20 records per page -- Adjust to your needs

          function pager(array $options = array()){
          $options = array_merge(array(
            'NumOfRecords'      => 0, //number of records from database 
            'maxPager'          => 5, //number pager to show eg. 1-5
            'maxPageRecords'    => 20 // since I need 20 records maximum per/page
          ),$options);

          $rows = (int) $options['NumOfRecords'];
          $maxPager = (int) $options['maxPager'];
          $maxPageRecords = (int) $options['maxPageRecords'];


          //offset records
          $offset  = $rows - $maxPageRecords; //since our max records per page is 20
          $totalPages = ceil($rows/$maxPageRecords); //move it to neart heigher number
          $numOfPagesLeft = $totalPages-1; //pages left

          if ($numOfPagesLeft) {
            //check if tab_id is set
          $tab_id = $activePage = isset($_GET['tab_id']) && (int)$_GET['tab_id'] ? (int)$_GET['tab_id']:1;

          //store pager here
          $pagerArray = array();

          //url to page we working on
          $URL = DOMAIN().'/'.(IsadminLogin() ? ("apps/tags/") :"tags/");
          $i = 0; //pager counter
          for ($row=0; $row < $numOfPagesLeft; $row++ ) { 
          $i++;
          //show only five pager
          if ($offset && $i <= $maxPager && $activePage !== $totalPages && $totalPages != $tab_id) {

            $page_id  = $tab_id++;
            $href     = $URL .$page_id;
            $active   = ($activePage == $page_id ? "current":"random");
            $ellipse  = ($i== $maxPager && $tab_id !== ($offset/$maxPageRecords) ? "<span class='page-numbers dots'>...</span>":"");
            $pagerArray[] = "<a class='page-numbers {$active}' href='{$href}/'>{$page_id}</a>{$ellipse}";
            }

          }

            //add previous page
            if ($activePage > 1) {
            //previous pagers
            $prev = $activePage -1;
            $prevHref = $URL .$prev;
            $prevPagers = $activePage > 2 ? "<a class='page-numbers prv' href='{$URL}1'>1</a><span class='page-numbers dots'>...</span>":""; 
            array_unshift($pagerArray , "<a class='page-numbers prev' href='{$prevHref}/'>prev</a>{$prevPagers}");
            }

            //show next button if offset is true

            if ($numOfPagesLeft) {
            //next pagers
            $next       = $activePage +1;
            $nextHref   = $URL.$next;
            $lastPager  = $totalPages;
            $PagerLast  = $URL.$lastPager;
            $active     = ($activePage == $lastPager ? "current":"random");
            $pagerS     = $lastPager ? "<a href='{$PagerLast}' class='page-numbers {$active}'>{$lastPager}</a>":"";
            array_push($pagerArray , $totalPages != $tab_id ? "{$pagerS} <a class='page-numbers prev' href='{$nextHref}/'>next</a>":"{$pagerS}");

            }

            //implode pager
            return  join('',$pagerArray);
            }

          }

          $maxPageRecords = 20; //since we fetching 20 records/page
          $tab_id = isset($_GET['tab_id']) && (int)$_GET['tab_id']  > 0 ? (int)$_GET['tab_id']:1;
          $offset = $tab_id == 1 ? 0:($tab_id > 1 ? ($tab_id-1) *$maxPageRecords:0);//calculate offset for SQL
          $SQL    = "SELECT * FROM $tags_table LIMIT {$maxPageRecords} OFFSET {$offset}";

          $Q    = queryDB($SQL);
          $NR   = mysqli_num_rows($Q);// number of rows returned
          if ($NR) {
            while ($row = mysqli_fetch_assoc($Q)) {
            $tag_id  = $row['tag_id'];
            $details = $row['details'];
            $TagName = $row['tag_name'];

            $HTML_temp = "<div class='grid-view tag-cell'>
            <div class='excerpt-header'><a href='#' class='post-tag'>{$TagName}</a></div>
            <div class='excerpt'>{$details}</div>
            </div>";
            ECHO $HTML_temp;
            }
          }else{
          ECHO "No tags found in database";
          }

          //echo $offset;

          //GET current domain name
          $LOCALIP     = array('127.0.0.1','::1');
          $sitename    = "school";
          $HostStatus   = !in_array(GET_IP(), $LOCALIP)? TRUE:FALSE; //hosting local or remote server
          function DOMAIN(){global $HostStatus,$sitename;if (isset($_SERVER['HTTPS']) && ($_SERVER['HTTPS'] == 'on' || $_SERVER['HTTPS'] == 1) ||isset($_SERVER['HTTP_X_FORWARDED_PROTO']) &&$_SERVER['HTTP_X_FORWARDED_PROTO'] == 'https') { $domain = 'https://'.str_replace(array('https://www.','http://www.','www.'), '', ($_SERVER['SERVER_NAME'])).($HostStatus ? '':'/'.$sitename);}else {$domain = 'http://'.str_replace(array('https://www.','http://www.','www.'), '', ($_SERVER['SERVER_NAME'])).($HostStatus ? '':'/'.$sitename);} return $domain;}function GET_IP(){if (!empty($_SERVER['HTTP_CLIENT_IP']))  {$ip=$_SERVER['HTTP_CLIENT_IP'];}elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {$ip=$_SERVER['HTTP_X_FORWARDED_FOR'];}else{$ip=$_SERVER['REMOTE_ADDR'];}return $ip;}
          ?>

HTML:
Lets call pager() function

<div class='pager'>
<div class='col-lg-12 no-padding'>
<div class='col-lg-2'></div>
<div class='col-lg-4'></div>
<div class='col-lg-6 no-padding'>
<div class="pager rfloat">

<?PHP 
//call pager() on the button of the page u want to show pagwer boxes
ECHO pager(array(
    'maxPageRecords'=>20,
    'NumOfRecords'=>countTags(), //records could be any number e.g. 400,200,52,12..make sure its coming from DB
    'maxPager'=>5));
?>

</div>
</div>
</div>
</div>

CSS:

.pager a{
    display: inline-block;
    padding: 4px 8px;
    font-size: 12px;
    color: #848d95;
    border: 1px solid #e4e6e8;
    border-radius: 3px;
    background-color: transparent;
    margin-left: 5px;
}

.page-numbers.current,.page-numbers:hover{
  transition: all ease-in-out .2s;
  color: #FFF;
  background-color: #ff7308;
  border-color: transparent;
}
.page-numbers.dots{
    color: #848d95;
    background-color: transparent;
    border-color: transparent;
    box-shadow: none;
    padding:0 15px;
    letter-spacing: 3px;
}

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.

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;
}

Related Topics

Fatal Error: Maximum Execution Time of 30 Seconds Exceeded

How to Maintain a "Version For the Server" - With Only Config Files Changed, in Git

Ajax Upload Image

How to Extract Data from CSV File in PHP

If Block Inside Echo Statement

How to Solve PHP Error 'Notice: Array to String Conversion In...'

Detect Browser Language in PHP

Make All Words Lowercase and the First Letter of Each Word Uppercase

PHP File_Get_Contents() and Setting Request Headers

What Is the Best Ide For PHP

Asynchronous Shell Exec in PHP

Filter/Remove Rows Where Column Value Is Found More Than Once in a Multidimensional Array

Warning: a Non-Numeric Value Encountered

MySQLi_Query() Expects At Least 2 Parameters, 1 Given

PHP 7.2 Function Create_Function() Is Deprecated

How to Get a Hex Dump of a String in PHP

PHP Domdocument Loadhtml Not Encoding Utf-8 Correctly

Warning: MySQL_Connect(): [2002] No Such File or Directory (Trying to Connect Via Unix:///Tmp/MySQL.Sock) In


Leave a reply



Submit