PHP MySQL paging

You want paging on your website ? Well, thats not so hard as you might think it is…..
This tutorial will be based on a visible query string in the address bar which should look like this:

This is all there is needed to provide your customers paging.

First, we will try to get the requested page from the query string, if the page key isset within the URL.

<?php
// Get the page number request
if ( isset( $_GET['page'] ) )
                $page = $_GET['page'];

Now we assigned the page request (integer) to the variable $page.

Right after that we will check if the given value is numeric. If it’s numeric, we will assign the absolute value to $Page otherwise we will assign 1 to it.

// Declare the request page
$Page = ( !is_numeric( $page ) ) ? 1 : abs( $page );

The function abs() will give us the absolute number of the input. If “funny” customers rty to enter page=-5 or page=+5 in the address bar, this nice function will delete the minus/plus sign (-/+), so we only get the number. Second of all, when your visitors try to put letters or other characters behind the page=, it will be set to page 1.

Then we will set a default amount of records, which will be shown per page.

// Amount Per page
$PpP = 3;

After that, We will check how many records the database contains for this particular query and assigns the result to $FetchResult.

// Query -> total amount of rows
$TotalRowQuery = mysql_query( "SELECT COUNT(*) FROM persons" )
                                            or die( 'Check your query' );

// Query -> Get number total amount of rows
$FetchResult = mysql_result( $TotalRowQuery, 0, 0 );

The first one will retrieve the total amount of records while the second one picks the number from the result set.

Then we will calculate how many pages we should have in total.
This would be $FetchResult (e.g 15) / $PpP (3) = 5

// Total amount of pages. Round all up (ceil())!
$TotalPages = ceil( $FetchResult / $PpP );

Then we build in a little security detail for customer which will change the address bar into page=415613521561.
If this happens, this piece of code will notice that and changes the number into the highest page thats possible.
That issue is solved now as well!

// If $Page is more then Total pages, set to highest page
$Page = ( $Page > $TotalPages ) ? $TotalPages : $Page;

Now we will set the OffSet, which means where the MySQL query will start picking records.
The calculation is as followed: $Page – 1 (Requested page) * $PpP (Amount per page)

// Start record @ DB
$OffSet = ( $Page - 1 ) * $PpP;

Now, we will actually run your query. $OffSet -> Start at record…… $PpP -> Show us x records

// Query -> Run the query. $OffSet and $PpP are set above here.
$Query = mysql_query( "SELECT * FROM persons LIMIT $OffSet,$PpP" )
                                            or die( 'Check your query!' );

After that, we are at the famous while loop to show the result!

// While loop to get through the dataset
while ( $r = mysql_fetch_array( $Query, MYSQL_ASSOC ) )
{
	echo $r['person_name'] . '<br />';
}

At last, but not least….. The Navigation!

// Page navigation;
echo ( $Page > 1 ) ? '<a href="?page=' . ( $Page - 1 ) . '">Previous Page</a>' : '';
echo $Page . ' of ' . $TotalPages;
echo ( $Page < $TotalPages ) ? '<a href="?page=' . ( $Page + 1 ) . '">Next Page</a>' : '';
?>

The whole script

<?php
// Get the page number request
if ( isset( $_GET['page'] ) )
                $page = $_GET['page'];

// Declare the request page
$Page = ( !is_numeric( $page ) ) ? 1 : abs( $page );

// Amount Per page
$PpP = 3;

// Query -> total amount of rows
$TotalRowQuery = mysql_query( "SELECT COUNT(*) FROM persons" )
                                            or die( 'Check your query' );

// Query -> Get number total amount of rows
$FetchResult = mysql_result( $TotalRowQuery, 0, 0 );

// Total amount of pages. Round all up!
$TotalPages = ceil( $FetchResult / $PpP );

// If $Page is more then Total pages, set to highest page
$Page = ( $Page > $TotalPages ) ? $TotalPages : $Page;

// Start record @ DB
$OffSet = ( $Page - 1 ) * $PpP;

// Query -> Run the query. $OffSet and $PpP are set above here.
$Query = mysql_query( "SELECT * FROM persons LIMIT $OffSet,$PpP" )
                                            or die( 'Check your query!' );

// While loop to get through the dataset
while ( $r = mysql_fetch_array( $Query, MYSQL_ASSOC ) )
{
	echo $r['person_name'] . '<br />';
}

// Page navigation;
echo ( $Page > 1 ) ? '<a href="?page=' . ( $Page - 1 ) . '">Previous Page</a>' : '';
echo $Page . ' of ' . $TotalPages;
echo ( $Page < $TotalPages ) ? '<a href="?page=' . ( $Page + 1 ) . '">Next Page</a>' : '';
?>

Attachments 1 image(s) & 0 video(s) found.

• 

Mail this!