Handling pagination

Pagination involves providing a limited subset of the results of a database query. This is usually done for display purposes, but could easily apply to other situations. At first glance, it would seem the LimitIterator class is ideally suited for the purposes of pagination. In cases where the potential result set could be massive; however, LimitIterator is not such an ideal candidate, as you would need to supply the entire result set as an inner iterator, which would most likely exceed memory limitations. The second and third arguments to the LimitIterator class constructor are offset and count. This suggests the pagination solution we will adopt, which is native to SQL: adding LIMIT and OFFSET clauses to a given SQL statement.

How to do it...

First, we create a class called Application\Database\Paginate to hold the pagination logic. We add properties to represent values associated with pagination, $sql, $page, and $linesPerPage:
```
namespace Application\Database;

class Paginate
{
    
  const DEFAULT_LIMIT  = 20;
  const DEFAULT_OFFSET = 0;
  
  protected $sql;
  protected $page;
  protected $linesPerPage;

}
```
Next, we define a __construct() method that accepts a base SQL statement, the current page number, and the number of lines per page as arguments. We then need to refactor the SQL string modifying or adding the LIMIT and OFFSET clauses.

In the constructor, we need to calculate the offset using the current page number and the number of lines per page. We also need to check to see if LIMIT and OFFSET are already present in the SQL statement. Finally, we need to revise the statement using lines per page as our LIMIT with the recalculated OFFSET:

public function __construct($sql, $page, $linesPerPage)
{
  $offset = $page * $linesPerPage;
  switch (TRUE) {
    case (stripos($sql, 'LIMIT') && strpos($sql, 'OFFSET')) :
      // no action needed
      break;
    case (stripos($sql, 'LIMIT')) :
      $sql .= ' LIMIT ' . self::DEFAULT_LIMIT;
      break;
    case (stripos($sql, 'OFFSET')) :
      $sql .= ' OFFSET ' . self::DEFAULT_OFFSET;
      break;
    default :
      $sql .= ' LIMIT ' . self::DEFAULT_LIMIT;
      $sql .= ' OFFSET ' . self::DEFAULT_OFFSET;
      break;
  }
  $this->sql = preg_replace('/LIMIT \d+.*OFFSET \d+/Ui', 
     'LIMIT ' . $linesPerPage . ' OFFSET ' . $offset, 
     $sql);
}

We are now ready to execute the query using the Application\Database\Connection class discussed in the first recipe.

In our new pagination class, we add a paginate() method, which takes a Connection instance as an argument. We also need the PDO fetch mode, and optional prepared statement parameters:

use PDOException;
public function paginate(
  Connection $connection, 
  $fetchMode, 
  $params = array())
  {
  try {
    $stmt = $connection->pdo->prepare($this->sql);
    if (!$stmt) return FALSE;
    if ($params) {
      $stmt->execute($params);
    } else {
      $stmt->execute();
    }
    while ($result = $stmt->fetch($fetchMode)) yield $result;
  } catch (PDOException $e) {
    error_log($e->getMessage());
    return FALSE;
  } catch (Throwable $e) {
    error_log($e->getMessage());
    return FALSE;
  }
}

It might not be a bad idea to provide support for the query builder class mentioned in the previous recipe. This will make updating LIMIT and OFFSET much easier. All we need to do to provide support for Application\Database\Finder is to use the class and modify the __construct() method to check to see if the incoming SQL is an instance of this class:
```
  if ($sql instanceof Finder) {
    $sql->limit($linesPerPage);
    $sql->offset($offset);
    $this->sql = $sql::getSql();
  } elseif (is_string($sql)) {
    switch (TRUE) {
      case (stripos($sql, 'LIMIT') 
      && strpos($sql, 'OFFSET')) :
          // remaining code as shown in bullet #3 above
      }
   }
```
Now all that remains to be done is to add a getSql() method in case we need to confirm that the SQL statement was correctly formed:
```
public function getSql()
{
  return $this->sql;
}
```

How it works...

Copy the preceding code into a Paginate.php file in the Application/Database folder. You can then create a chap_05_pagination.php calling program, which initializes the autoloader defined in Chapter 1, Building a Foundation:

<?php
define('DB_CONFIG_FILE', '/../config/db.config.php');
define('LINES_PER_PAGE', 10);
define('DEFAULT_BALANCE', 1000);
require __DIR__ . '/../Application/Autoload/Loader.php';
Application\Autoload\Loader::init(__DIR__ . '/..');

Next, use the Application\Database\Finder, Connection, and Paginate classes, create an instance of Application\Database\Connection, and use Finder to generate SQL:

use Application\Database\ { Finder, Connection, Paginate};
$conn = new Connection(include __DIR__ . DB_CONFIG_FILE);
$sql = Finder::select('customer')->where('balance < :bal');

We can now get the page number and balance from $_GET parameters, and create the Paginate object, ending the PHP block:

$page = (int) ($_GET['page'] ?? 0);
$bal  = (float) ($_GET['balance'] ?? DEFAULT_BALANCE);
$paginate = new Paginate($sql::getSql(), $page, LINES_PER_PAGE);
?>

In the output portion of the script, we simply iterate through the pagination using a simple foreach() loop:

<h3><?= $paginate->getSql(); ?></h3>	
<hr>
<pre>
<?php
printf('%4s | %20s | %5s | %7s' . PHP_EOL, 
  'ID', 'NAME', 'LEVEL', 'BALANCE');
printf('%4s | %20s | %5s | %7s' . PHP_EOL, 
  '----', str_repeat('-', 20), '-----', '-------');
foreach ($paginate->paginate($conn, PDO::FETCH_ASSOC, 
  ['bal' => $bal]) as $row) {
  printf('%4d | %20s | %5s | %7.2f' . PHP_EOL, 
      $row['id'],$row['name'],$row['level'],$row['balance']);
}
printf('%4s | %20s | %5s | %7s' . PHP_EOL, 
  '----', str_repeat('-', 20), '-----', '-------');
?>
<a href="?page=<?= $page - 1; ?>&balance=<?= $bal ?>">
<< Prev </a>&nbsp;&nbsp;
<a href="?page=<?= $page + 1; ?>&balance=<?= $bal ?>">
Next >></a>
</pre>

Here is page 3 of the output, where the balance is less than 1,000:

Table of Contents for
PHP 7: Real World Application Development

Handling pagination

How to do it...

How it works...

See also

Table of Contents for PHP 7: Real World Application Development

Handling pagination

How to do it...

How it works...

See also

Table of Contents for
PHP 7: Real World Application Development