Handling Pagination (PHP/MySQL)

Posted by admin at January 1, 2017

In this tutorial, I am documenting how I handle pagination of viewed records.

Again we are going to use a large table, the international airports table `tbl_airports`.


--
-- Table structure for table `tbl_airports`
--

DROP TABLE IF EXISTS `tbl_airports`;
CREATE TABLE IF NOT EXISTS `tbl_airports` (
  `airport_id` int(11) NOT NULL AUTO_INCREMENT,
  `airport_code` varchar(50) DEFAULT NULL,
  `airport_desc` varchar(255) DEFAULT NULL,
  `airport_iata_code` varchar(50) DEFAULT NULL,
  `airport_notes` varchar(100) DEFAULT NULL,
  `airport_city` varchar(100) DEFAULT NULL,
  `airport_country_id` int(11) NOT NULL DEFAULT '0',
  `status` int(11) DEFAULT '0',
  `createdby_id` int(11) NOT NULL DEFAULT '0',
  `date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modifiedby_id` int(11) NOT NULL DEFAULT '0',
  `date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`airport_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Next we are going to display all the records in the table. Below is the read segment


<?php //*********************************************READ ?>
    
<hr>
    
<strong>Read Airports</strong><br><br>
    
<?php

   // build the read query
   $sql = get_read_sql('tbl_airports')
            // remove all record marked 3 : DELETE
            .' AND `status` <> "3"'
            
            // arrange by code
            .' ORDER BY `airport_code` ASC'
            
            ;

   // process the sql for table
   $mysql_res = send_sql($sql, $conn);

   // get the first row
   $row = $mysql_res['first_row'];

?>
    
<?php // display the table ?>
<table width="100%">
        <thead class="fw_b" valign="top">
            <tr>
                <td width="5%">SN</td>
                <td width="10%">CODE</td>
                <td width="10%">IATA</td>
                <td>Airport Name</td>
                <td>City</td>
                <td>Country</td>
                <td width="10%">Actions</td>
            </tr>
        </thead>
        <tbody valign="top">
        <?php
            // loop from beginning to end of the records
            for ($i=0; $i<$mysql_res['total_rows']; $i++)
            {
        ?>
            <tr>
                <td><?php echo $i+1; ?></td>
                <td><?php echo $row['airport_code']; ?></td>
                <td><?php echo $row['airport_iata_code']; ?></td>
                <td><?php echo utf8_encode($row['airport_desc']); ?></td>
                <td><?php echo utf8_encode($row['airport_city']); ?></td>
                <td><?php echo utf8_encode($row['airport_notes']); ?></td>
                <td>
                    <a href="index.php?a=d&rid=<?php echo $row['airport_id']; ?>">delete</a>
                </td>
            </tr>
            <?php $row = mysql_fetch_assoc($mysql_res['rs']); ?>
        <?php } ?>
        </tbody>
</table>
        
<?php // display the table ?>
    
<?php //*********************************************READ ?>

Now we have all the record being displayed on a single page.

Pagination

Next, add the Pagination functionality

Links

We add a code that will display pagination links depending on the parameters submitted to GET.


/**
*
* This function generates the pagination links for the READ page of a submission table
*
* IN
* $tbl is the mysql table
* $conn is mysql connection resource
*
* OUT : Array
* 'page_link_html' HTML First Previous Next and Last links
* 'page_num' current page number
* 'page_rows' number records in page
* 'start_row' => the row index to start counting records
* 'stop_row' => the row index to stop counting records
* 'total_pages' => total number of pages for the current rows per page
*
*/

function get_pages($tbl, $conn)
{
    
    // get the page number form GET
    $page_num = mysql_real_escape_string($_GET['page_num']);
    // make default page 0
    $page_num = ($page_num > 0) ? $page_num : 0;

    // get rows per page form GET
    $page_rows = mysql_real_escape_string($_GET['page_rows']);
    // make default rows per page 20
    $page_rows = ($page_rows > 0) ? $page_rows : 20;

    
    // total number of records in the table
    {
        // the query for total number of records in table
        $sql_total = 'SELECT COUNT(*) FROM '.$tbl.' WHERE 1';
        
        // submit the query
        $rs = mysql_query(sql_total, $conn) or die(mysql_error());

        // fetch the first 
        $row = mysql_fetch_assoc($rs);

        // total rows is found in the COUNT(*) field
        $total_rows = $row['COUNT(*)'];
    }
    
    // calculate total pages
    $total_pages = floor($total_rows / $page_rows);
    
    // the conditions for first and last pages
    $is_first_page= ($page_num <= 0);
    $is_last_page = ($page_num >= $total_pages);
    
    // calculate start and stop rows
    {
        // loop start row
        $start_row = $page_num * $page_rows;
        
        // loop stop row
        $stop_row = $start_row + $page_rows;
        if ($is_last_page)
        {
            // the stop row should be the last record in the table
            // - 1 is to correct for the loop starting from zero
            $stop_row = $total_rows - 1;
        }
    }
    
    // generate the html links
    {
        $first_link = ($is_first_page) ? 'First' : '<a href="?">First</a>';

        $prev_link = ($is_first_page) ? 'Previous' : '<a href="?'.@page_num.'='.($page_num - 1).'&'.@page_rows.'='.$page_rows.'">Previous</a>';

        $next_link = ($is_last_page) ? 'Next' : '<a href="?'.@page_num.'='.($page_num + 1).'&'.@page_rows.'='.$page_rows.'">Next</a>';

        $last_link = ($is_last_page) ? 'Last' : '<a href="?'.@page_num.'='.$total_pages.'">Last</a>';
    }
    
    // combine strings
    $plinks = ''
        . $first_link.'  '
        . $prev_link.'  '
        . $next_link.'  '
        . $last_link
    ;
    
    $res = array(
        'page_link_html' => $plinks
        , 'page_num' => $page_num
        , 'page_rows' => $page_rows
        , 'start_row' => $start_row
        , 'stop_row' => $stop_row
        , 'total_pages' => $total_pages
    );
    
    // return the result
    return $res;
    
}

The code segment above will generate the First-Prev-Next-Last links as shown below

So we need to add this function to the READ Table and make further adjustments


<?php //*********************************************READ ?>
    
    <hr>
    
    <strong>Read Airports</strong><br><br>
    
    <?php

        $tbl = 'tbl_airports';
        
        // get the pagination links
        $pagination_links = get_pages($tbl, $conn);
        
        // build the read query
        $sql = get_read_sql($tbl)
            
            // arrange by code
            .' ORDER BY `airport_code` ASC'
            
            // implement the pagination using LIMIT
            .' LIMIT '.$pagination_links['start_row'].', '.$pagination_links['page_rows']
            
            ;

        // process the sql for table
        $mysql_res = send_sql($sql, $conn);

        // get the first row
        $row = $mysql_res['first_row'];
        
    ?>
    
    <?php // display the table ?>
    <table width="100%">
        <thead class="fw_b" valign="top">
            <tr>
                <td width="5%">SN</td>
                <td width="10%">CODE</td>
                <td width="10%">IATA</td>
                <td>Airport Name</td>
                <td>City</td>
                <td>Country</td>
                <td width="10%" align="center">Actions</td>
            </tr>
        </thead>
        <tbody valign="top">
        <?php
            // loop from start to stop boundaries of the current page
            for ($i=$pagination_links['start_row']; $i<$pagination_links['stop_row']; $i++)
            {
        ?>
            <tr>
                <td><?php echo $i+1; ?></td>
                <td><?php echo $row['airport_code']; ?></td>
                <td><?php echo $row['airport_iata_code']; ?></td>
                <td><?php echo utf8_encode($row['airport_desc']); ?></td>
                <td><?php echo utf8_encode($row['airport_city']); ?></td>
                <td><?php echo utf8_encode($row['airport_notes']); ?></td>
                <td align="center">...</td>
            </tr>
            <?php $row = mysql_fetch_assoc($mysql_res['rs']); ?>
        <?php } ?>
        </tbody>
        <tfoot>
            <tr align="center">
                <td colspan="7">
                    <?php echo $pagination_links['page_link_html']; ?>
                </td>
            </tr>
        </tfoot>
    </table>
    
    <?php // display the table ?>
    
<?php //*********************************************READ ?>

Notice that we changed the segment


// build the read query
$sql = get_read_sql($tbl)
            
        // arrange by code
        .' ORDER BY `airport_code` ASC'
            
    ;


// get the pagination links
$pagination_links = get_pages($tbl, $conn);
        
// build the read query
$sql = get_read_sql($tbl)
            
        // arrange by code
        .' ORDER BY `airport_code` ASC'
            
        // implement the pagination using LIMIT
        .' LIMIT '.$pagination_links['start_row'].', '.$pagination_links['page_rows']
        
    ;

The first line calls the get_pages() function we defined above to return a number of results including the start and stop rows for the current page of records. The next line defines our READ SQL restricting the number of rows generated in this page, based on the pagination variables.

Also we changed the segment below:


// loop from beginning to end of the records
for ($i=0; $i<$mysql_res['total_rows']; $i++)


// loop from start to stop boundaries of the current page
for ($i=$pagination_links['start_row']; $i<$pagination_links['stop_row']; $i++)

Finaly, we added the pagination link html from the get_pages(); function, to the footer of the table:


<tfoot>
    <tr align="center">
        <td colspan="7">
            <?php echo $pagination_links['page_link_html']; ?>
        </td>
    </tr>
</tfoot>

Putting it together:

Our final result should look something like this

The entire package can be downloaded here

0 likes