Pagination in PHP

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

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

Pagination MySQL PHP

You'd be better off thinking about the principles of how it works rather than just looking at the code.

Firstly, you need to know:

• How many records are there in the table? You can do that with COUNT()
• How many entries to show per page? You may fix this, to say 10 per page, or any number.

You then need to understand how LIMIT works. If you do something such as LIMIT 50, 10 it means use 50 as the starting point ("from the 51st record in the table" - remember the indexes start at 0) and get the next 10 rows. The latter number, 10, is the number of rows you want to show per page.

In terms of constructing the links the easiest method is to make the ?page= parameter in the URL the first value for the LIMIT query because this changes per page (e.g. 50 in the example above), where as you know the other number (10) is constant. You can produce these links by doing a ceil on the number of records in the table, divided by the number of records per page. This will output the appropriate numbers for the URL.

Let's say you had 362 records in your database and wanted to show 10 per page. This would produce the URL's:

$per_page = 10;
$records_in_db_table = 362; // You must calculate this from a COUNT() query
$n = ceil($records_in_db_table / $per_page);

for ($x=0; $x<$n; $x++) {
    $page = ($x * $per_page);
    echo '?page=' . $page;
}

The above code outputs:

?page=0
?page=10
?page=20
?page=30
// ...
?page=360

Then you just feed these in to your query's LIMIT condition, e.g.

• ?page=10 == LIMIT 10, 10
• ?page=20 == LIMIT 20, 10
• ?page=30 == LIMIT 30, 10
• etc...

It's also worth noting that you don't need to care about what happens if you're trying to LIMIT to more records than exist. For example the last URL (?page=360) will do LIMIT 360, 10. There are only 362 records in your database so you might assume this won't work as there are only 2 records that could be returned from that query. However, it will just return the last 2, no problem.

Equally if you try and use a number which is way beyond the total number of records (?page=99999 which gives LIMIT 99999, 10) it will just return an empty result set, not an error. Some people code things in to do a simple check, e.g.

if ((int)$_GET['page'] > $records_in_db_table) { 
    // Display error message
}

Remember that you should sanitise $_GET['page'] or at the very least cast it to an integer. Do not inject anything from $_GET directly into your query, and use parameter binding (e.g. PDO) if possible.

You may also want to look at DataTables (https://datatables.net/) since this does a lot of what you need without writing any such code and works well with Bootstrap.

How to add PHP pagination in array's

u can use simple PHP function called array_slice()

$menuItems = array_slice( $menuItems, 0, 10 ); 

show first 10 items.

$menuItems = array_slice( $menuItems, 10, 10 );

show next 10 items.

UPDATE:

$page = ! empty( $_GET['page'] ) ? (int) $_GET['page'] : 1;
$total = count( $yourDataArray ); //total items in array    
$limit = 20; //per page    
$totalPages = ceil( $total/ $limit ); //calculate total pages
$page = max($page, 1); //get 1 page when $_GET['page'] <= 0
$page = min($page, $totalPages); //get last page when $_GET['page'] > $totalPages
$offset = ($page - 1) * $limit;
if( $offset < 0 ) $offset = 0;

$yourDataArray = array_slice( $yourDataArray, $offset, $limit );

UPDATE#2:

Example of pagination:

$link = 'index.php?page=%d';
$pagerContainer = '<div style="width: 300px;">';   
if( $totalPages != 0 ) 
{
  if( $page == 1 ) 
  { 
    $pagerContainer .= ''; 
  } 
  else 
  { 
    $pagerContainer .= sprintf( '<a href="' . $link . '" style="color: #c00"> « prev page</a>', $page - 1 ); 
  }
  $pagerContainer .= ' <span> page <strong>' . $page . '</strong> from ' . $totalPages . '</span>'; 
  if( $page == $totalPages ) 
  { 
    $pagerContainer .= ''; 
  }
  else 
  { 
    $pagerContainer .= sprintf( '<a href="' . $link . '" style="color: #c00"> next page » </a>', $page + 1 ); 
  }           
}                   
$pagerContainer .= '</div>';

echo $pagerContainer;

best way to get large data from database with pagination in php?

The best practice and best ways in pagination is using the limit and offset and also optimizing the output.

Also Pear::Pager can help with the output, and to limit the database traffic to only what you need, you can use a wrapper provided in the examples that come with it.

you may take a look at this article too. it's providing data about Optimized Pagination using MySQL, making the difference between counting the total amount of rows, and pagination.

Creating a Cursor Based Pagination System in PHP

The cursors are useful to prevent scan big tables and allow to move in very big sources (files, external resources, etc., etc.). In the majority of the cases, cursors are provided by binary libraries and supported by the core of the related system (mysql, files). If you try to emulate this behavior in not natural way you must take care because you could add overhead and get unexpected results.

In the other hand, is very useful to have a pagination class, but be aware, this class have some problems.

1. getData is very expensive because it performs 3 queries to get a batch of results
2. the class is too verbose, the getters and setters add too much noise
3. the results are sorted using very rare criteria. Remember, if you are in search results you need the same order moving to the next page and moving to the previous page

My suggestions...
Create interfaces

<?php

interface CursorAble {
    
    public function fetchNext ($startingAfter);
    public function fetchPrev ($startingBefore);
    public function getPreviousLink ();
    public function getNextLink ();
}

interface Pageable {
    
    public function getCollectionSize ();
    public function getPageSize ();
    public function getPagesCount ();
    public function getPageLinks ();
}

When you create interfaces you ensures that the classes expose the desired behavior and furthermore delegate the specialized details to the concrete implementations. The concrete implementations can define it dependencies in the constructor, something very good when you relies on dependency injection.

CursorAble implementation example

<?php

class PdoCursorAbleTable implements CursorAble {

    private $pdo;
    private $table;
    private $results;
    private $pageSize;

    public function __construct (PDO $pdo, $table, $pageSize = 100) {
        $this->pdo      = $pdo;
        $this->table    = $table;
        $this->pageSize = (int)$pageSize ?: 100;
    }

    public function fetchNext ($startingAfter) {
        $s = $this->pdo->prepare("select * from {$this->table} where id > :starting_after limit {$this->pageSize}");
        $s->bindValue(':starting_after', $startingAfter, PDO::PARAM_INT);
        $s->execute();
        $this->results = $s->fetchAll() ?: [];

        return $this->results;
    }

    public function fetchPrev ($startingBefore) {

        $s = $this->pdo->prepare("select * from {$this->table} where id < :starting_before limit {$this->pageSize}");
        $s->bindValue(':starting_before', $startingBefore, PDO::PARAM_INT);
        $s->execute();
        $this->results = $s->fetchAll() ?: [];

        return $this->results;
    }

    public function getPreviousLink () {
        return !$this->results ? '' : '?starting_before=' . $this->results[0]->id;
    }

    public function getNextLink () {
        if (!$this->results || count($this->results) < $this->pageSize) return '';

        return '?starting_after=' . $this->results[count($this->results)]->id;
    }
}

And the Pageable example

<?php

class PdoPageableTable implements Pageable {
    
    private $pdo;
    private $table;
    private $pageSize;
    private $collectionSize;
    
    public function __construct (PDO $pdo, $table, $pageSize = 100) {
        
        $this->pdo      = $pdo;
        $this->table    = $table;
        $this->pageSize = $pageSize;
    }
    
    public function getCollectionSize () {
        
        if ($this->collectionSize === null) {
            $s = $this->pdo->prepare("select count(id) from {$this->table}");
            $s->execute();
            $this->collectionSize = $s->fetchColumn('0');
        }
        
        return $this->collectionSize;
    }
    
    public function getPageSize () {
        return $this->pageSize;
    }
    
    
    public function getPagesCount () {
        return ceil($this->collectionSize / $this->getPageSize());
    }
    
    
    public function getPageLinks () {
        $pages = [];
        
        foreach (range(1, $this->getPagesCount()) as $page) {
            $pages[] = '?page=' . $page;
        }
        
        return $pages;
    }
}

The test file

<?php

$pagination = new PdoCursorAbleTable($pdo, 'carousel_image', 2);

echo "<pre>";

$startingAfter = 0;
$results = $pagination->fetchNext($startingAfter);

foreach ($results as $result) {
    // do something
}

echo $pagination->getNextLink();

echo "</pre>";

hide some pages from php pagination system with sql

The solution for your pagination problem is this:

• Get the current page number using the superglobal $_GET,

  // current page
  $current_page = isset($_GET['page']) ? $_GET['page'] : 1;
  

• Find the superset range of pages, like 1-10, or 1-20 etc. For example, if $total_pages = 30 then this superset range would be 1-30. The code for this step is this:

  // superset range of pages
  $superset_range = range(1, $total_pages);
  

• Find the subset range of pages to display, like 1-7, or 3-9 etc. For example, if $total_pages = 30 then this subset range would be 1-7, or 3-9, or 6-12 etc., it can be any consecutive seven pages between 1 and 30. Also, adjust this range whenever necessary. The code for this step is this:

  // subset range of pages to display
  $subset_range = range($current_page - 3, $current_page + 3);
  
  // adjust the range(if required)
  foreach($subset_range as $p){
      if($p < 1){
          array_shift($subset_range);
          if(in_array($subset_range[count($subset_range) - 1] + 1, $superset_range)){
              $subset_range[] = $subset_range[count($subset_range) - 1] + 1;
          }
      }elseif($p > $total_pages){
          array_pop($subset_range);
          if(in_array($subset_range[0] - 1, $superset_range)){
              array_unshift($subset_range, $subset_range[0] - 1);
          }
      }
  }
  

• Finally, display the pagination links and dots accordingly. The code for this step is this:

  // display intermediate pagination links
  if($subset_range[0] > $superset_range[0]){
      echo " ... ";
  }
  foreach($subset_range as $p){
      echo "<a href='index.php?page=$p'>$p</a>";
  
  }
  if($subset_range[count($subset_range) - 1] < $superset_range[count($superset_range) - 1]){
      echo " ... ";
  }
  

So, here's the complete code:

// your code

$stmt = $con->prepare("SELECT * FROM item_template");
$stmt->execute();
$stmt->store_result();

if($stmt->num_rows > 0) {
    $total_records = $stmt->num_rows;
    $total_pages = ceil($total_records / $per_page);

    if($total_records > $per_page){
        echo "<center><a href='index.php?page=1'>First Page</a> ";

        // current page
        $current_page = isset($_GET['page']) ? $_GET['page'] : 1;

        // Superset range of pages
        $superset_range = range(1, $total_pages);

        // subset range of pages to display
        $subset_range = range($current_page - 3, $current_page + 3);

        // adjust the range(if required)
        foreach($subset_range as $p){
            if($p < 1){
                array_shift($subset_range);
                if(in_array($subset_range[count($subset_range) - 1] + 1, $superset_range)){
                    $subset_range[] = $subset_range[count($subset_range) - 1] + 1;
                }
            }elseif($p > $total_pages){
                array_pop($subset_range);
                if(in_array($subset_range[0] - 1, $superset_range)){
                    array_unshift($subset_range, $subset_range[0] - 1);
                }
            }
        }

        // display intermediate pagination links
        if($subset_range[0] > $superset_range[0]){
            echo " ... ";
        }
        foreach($subset_range as $p){
            echo "<a href='index.php?page=$p'>$p</a>";

        }
        if($subset_range[count($subset_range) - 1] < $superset_range[count($superset_range) - 1]){
            echo " ... ";
        }

        echo " <a href='index.php?page=$total_pages'>Last Page</a></center> ";  
    }    
} 

Pagination not working-PHP

You have to use r_query to find no of rows in pagination. Now you are finding the total number of rows in the table

<?php 
            //Pagination code starts here

            $total_records = mysql_num_rows($r_query);  //count number of records
            $total_pages = ceil($total_records / $num_rec_per_page); 

            echo "<a href='product_listing.php?page=1'>".'|<'."</a> "; // Goto 1st page  

            for ($i=1; $i<=$total_pages; $i++) { 
                        echo "<a href='product_listing.php?page=".$i."'>".$i."</a> "; 
            }; 
            echo "<a href='product_listing.php?page=$total_pages'>".'>|'."</a> "; // Goto last page

            ?>

---

Related Topics