objSQL Helper Method: Paging

The obj_paging() helper method allows you to facilitate recordset paging queries with minimal SQL markup and is called from the objSQL class.

Common uses include online catalogs where the user can view multi-page results by specifying a price range, color, material, etc., as well as display the results by ordering by price or availability.

• The $table name argument is required.
• The optional $cols argument specifies which columns to return and is entered as a comma delimited string. Entering an empty value is equivilent to an asterisk (*).
• The optional $where argument allows you to limit which rows are selected and only requires the column and its value. You can use any of the normal operators used in SQL statements:
  • "color IN ('blue','red')"
  • "color='red' AND material='leather'"
  • "brand LIKE 's%'"
  • "price BETWEEN 200 AND 400"
• The optional $order_by argument is used to sort the resultset. SQL Server 2005 and 2008 require an order by argument and will throw an exception if not supplied.
• The $limit and $offset arguments default to 1 if not supplied. $limit is the number of records to display per page. The $offset argument is normally supplied by the global $_GET variable.

Returns: Array

• element[0]: Result resource/object or false on failure
• element[1]: Unsigned integer - Number of last page

<?php

//usage: $rs = $dbh->obj_paging( $table, $cols, $where, $order_by, $limit, $offset )
//usage: $result = $rs[0]
//usage: $last_page = $rs[1] 

try
{
    $output = '';
    $limit  = ( isset( $_GET["limit"] ) ) ? $_GET["limit"] : 20;
    $offset = ( isset( $_GET["page"] ) && $_GET["page"] > 0 ) ? $_GET["page"] : 1;
        
    $rs = $dbh->obj_paging( "mytable", "id,first_name,last_name", "", "id", $limit, $offset );
    $result = $rs[0];
    $last_page = $rs[1];
    
    if ( $dbh->obj_error() )
        throw new Exception( $dbh->obj_error_message() );
    
    while( $row = $result->obj_fetch_object() ) 
        echo "{$row->id}: {$row->first_name} - {$row->last_name}<br />";

    if ( $offset == 1 ) 
    {
        $output .= "&lt;&lt;&nbsp;&nbsp;&lt;&nbsp;";
    } 
    else 
    {
        $output .= "<a href=test_paging.php?page=1>&lt;&lt;&nbsp;&nbsp;</a>";
        $prev    = $offset - 1;
        $output .= "<a href=test_paging.php?page=$prev>&lt;&nbsp;</a>";
    }

    $output .= " [ Page $offset of $last_page ] ";

    if ( $offset == $last_page ) 
    {
        $output .= "&nbsp;&gt;&nbsp;&nbsp;&gt;&gt;";
    } 
    else 
    {
        $next    = $offset + 1;
        $output .=  "<a href=test_paging.php?page=$next>&nbsp;&gt;</a>";
        $output .=  "<a href=test_paging.php?page=$last_page>&nbsp;&nbsp;&gt;&gt;</a>";
    }

    echo "<p>$output</p>";
    
    $result->obj_free_result();
    
    
    /** Displays:
    
    1: Stephanie - Parker
    2: Estaban - White
    3: Xavier - Nichols
    4: Betty - Jefferies
    5: Stephanie - Clark
    6: Greg - Pipes
    9: Ezra - Yontz
    11: Marcia - Raymer
    13: Andrew - Jordan
    14: Ian - Jenkins
    15: Howard - Dixon
    16: Bonnie - Ward
    17: Louis - Pipes
    18: Orson - Schroeder
    19: Tiffany - Darden
    20: Xavier - Clark
    21: George - Raymer
    22: Cheryl - Yontz
    24: Percy - Cavenaugh
    25: David - Ingram
    
    <<  <  [ Page 1 of 23 ]  >  >>
    
    */
    
}
catch ( Exception $e ) 
{
    //log error and/or redirect user to error page
} 

?>