Notice

- This package is no longer maintained - I have also removed the License so you're free to do whatever you like with the code in any fashion you want.

PDO and MySQLi PHP Pagination Class

PDO MySQLi Pagination class is a PHP pagination class that will work with either PDO, or MySQLi. The outputted pagination is served in an Unordered List, and the options are extensive.

Options

All you need to do is include the file, set the options, and then you can work with the data as is. Here's the different options that are available:

<?
/* 
 * This is a list of all of the available options with the defaults: 
 */  
$options = array(  
    'results_per_page'              => 10,  
    'max_pages_to_fetch'            => 1000000,  
    'url'                           => '',  
    'url_page_number_var'           => '*VAR*',  
    'text_prev'                     => '&lsaquo; Prev',  
    'text_next'                     => 'Next &rsaquo;',  
    'text_first'                    => '&laquo; First',  
    'text_last'                     => 'Last &raquo;',  
    'text_ellipses'                 => '...',  
    'class_ellipses'                => 'ellipses',  
    'class_dead_links'              => 'dead-link',  
    'class_live_links'              => 'live-link',  
    'class_current_page'            => 'current-link',  
    'class_ul'                      => 'pagination',
    'current_page_is_link'          => true,
    'show_links_first_last'         => true,  
    'show_links_prev_next'          => true,  
    'show_links_first_last_if_dead' => true,  
    'show_links_prev_next_if_dead'  => true,  
    'max_links_between_ellipses'    => 7,      // This MUST be an odd number, or things will break :/
    'max_links_outside_ellipses'    => 2,  
    'db_conn_type'                  => 'pdo',  // Can be either: 'mysqli' or 'pdo'
    'db_handle'                     => 0,  
    'named_params'                  => false,  
    'using_bound_params'            => false,
    'using_bound_values'            => false
);
?>

Here's the description for each of those options:

results_per_page: The total number of results to show per page
max_pages_to_fetch: You can limit the number of pages to fetch in total here
url: The base URL to use in links
url_page_number_var: The variable which will be replaced with the page numbers in URL's
text_prev: The text for the 'Prev' link
text_next: The text for the 'Next' link
text_first: The text for the 'First' link
text_last: The text for the 'Last' link
text_ellipses: The text to use for the ellipses between the links
class_ellipses: The CSS class to apply to the ellipses elements
class_dead_links: The class to apply to dead links (i.e, if your on page 1, this would be applied to the 'Prev' link)
class_live_links: The class to apply to any regular links
class_current_page: The class to apply to the current page link
class_ul: The class to apply to the whole UL
current_page_is_link: If set to false, the number for the current page will not be a link
show_links_first_last: Set to 1 to show the 'first/last' links
show_links_prev_next: Set to 1 to show the 'prev/next' links
show_links_first_last_if_dead: If the 'first/last' links are dead, set to 1 to still show them
show_links_prev_next_if_dead: If the 'prev/next' links are dead, set to 1 to still show them
max_links_between_ellipses: The total number of links to show on the indise of the ellipses (MUST be an odd number)
max_links_outside_ellipses: The total number of links to show on the outside of the ellipses
db_conn_type: Either 'pdo' (Default) or 'mysqli'
db_handle: The database handle object
named_params: An array for named params (See below)
using_bound_params: Set to true if you are using bound params in PDO
using_bound_values: Set to true if you are using bound values in PDO

Using the class

To use the class, you must pass at least 2 options if using PDO, or 3 if using MySQLi. The URL for the pagination link, and the Database Object (Handle). If you are using MySQLi instead of PDO, you must also define that in the options.

When using PDO you can call the class using a regular query, or you can use named params or bind params. After the full example below, you can see how to use the named params and bound params styles.

Here's a full example on how to use the class, using PDO:

<?
/* 
 * Include the class 
 */  
require_once('path/to/pagination.php');  
  
  
/* 
 * Set the current page number 
 */  
if(isset($_GET['page']))  
{  
    $page = $_GET['page'];  
}  
else  
{  
    $page = 1;  
}  
  
  
/* 
 * Connect to the database and create the handle 
 */  
try  
{  
    $dbh = new PDO('mysql:host=my.database.hostname;dbname=dbname', 'username', 'password');  
}  
catch (PDOException $e)  
{  
    echo 'Error!: '.$e->getMessage();  
    die();  
}  
  
  
/* 
 * Set the options
 */  
$options = array(  
    'url'        => 'http://www.mysite.com/mypage.php?page=*VAR*',  
    'db_handle'  => $dbh  
);  
  
  
/* 
 * Call the class, the var's are: 
 * 
 * pagination(int $surrent_page, string $query, array $options) 
 */  
try
{
    $pagination = new pagination($page, 'SELECT some_column FROM some_table ORDER BY some_other_column', $options);  
}
catch(paginationException $e)
{
    echo $e;
    exit();
}
  
  
/* 
 * If all was successful, we can do something with our results 
 */  
if($pagination->success == true)  
{  
    /* 
     * Get the results 
     */  
    $result = $pagination->resultset->fetchAll();  
      
    foreach($result as $row)  
    {  
        echo '<p>'.$row['some_column'].'</p>';  
    }  
      
      
    /* 
     * Show the paginationd links ( 1 2 3 4 5 6 7 ) etc. 
     */  
    echo $pagination->links_html;  
      
      
    /* 
     * Get the total number if pages if you like 
     */  
    echo $pagination->total_pages;  
      
      
    /* 
     * Get the total number of results if you like 
     */  
    echo $pagination->total_results;  
}
?>

Named Params in PDO

When using named params, you simply need to add the array of parameters to the options array, like this:

<?
$options = array(  
    'url'          => 'http://www.mysite.com/mypage.php?page=*VAR*',  
    'db_handle'    => $dbh,  
    'named_params' => array(':param_a' => 'foo', ':param_b' => 'bar')  
);
?>

Bind Params and Bind Values in PDO

Binding params work's a little differently, you need to tell the class that you'll want to be binding params, and then bind them - the class will not execute the query automatically like it does in all other ways of functioning. I have kept the naming convention of bindParam to help make it easier to use the system without having to remember what the method names are. Once you're done binding, you can then execute the query manually:

<?
$options = array(  
    'url'                => 'http://www.mysite.com/mypage.php?page=*VAR*',  
    'db_handle'          => $dbh,  
    'using_bound_params' => true  
);  

try
{
    $pagination = new pagination($page, 'SELECT * FROM table WHERE field_a = :param_a AND field_b = :param_b', $options);  
}
catch(paginationException $e)
{
    echo $e;
    exit();
}
  
$pagination->bindParam(':param_a', 'foo', PDO::PARAM_STR, 12);  
$pagination->bindParam(':param_b', 'bar');  
  
$pagination->execute();
?>

Bind Values in PDO

Binding values works in pretty much the same way as bindParam, however you'll need to use a slightly different option in the config too:

<?
$options = array(  
    'url'                => 'http://www.mysite.com/mypage.php?page=*VAR*',  
    'db_handle'          => $dbh,  
    'using_bound_values' => true  
);  

try
{
    $pagination = new pagination($page, 'SELECT * FROM table WHERE field_a > ?', $options);  
}
catch(paginationException $e)
{
    echo $e;
    exit();
}
  
$pagination->bindValue(1, 10, PDO::PARAM_INT);  
  
$pagination->execute();
?>

Page links as an array

You can also retrieve the links for the pagaintion as an array. The 'extras' part contains the first/last and prev/next link data. The 'links' part contains the regular paginated links. The links section is further broken down to up to 3 more sections. Each section would be seperated by an ellipses or other type of break depending on how you have set up the class.

To fetch it, use:

<?

$links_data = $pagination->links_array;

?>

The data would look something along the lines of this:

License

No License - feel free to do what you want with the code.

BenGriffiths/pdo-mysqli-pagination