INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...It's fun to see others going through the same stuff I did and be able to help. It's also a way for me to stay sharp and not lose the stuff I've learned..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > Web Development > PHP > FAQs

PHP FAQ

Database (MySQL)

Data paging

faq434-5244

Posted: 28 Jun 04 (Edited 11 Jan 06)

Here is an example of a data-paging script.  The "trick" to writing such a script when fetching data from MySQL is to use the "LIMIT" clause of the "SELECT" query.  This allows the script to calculate which records to fetch.

The script, as written, uses a table on my system that consists of the standard Linux word list ("/usr/share/dict/words").  The table structure can be replicated by the query:

CREATE TABLE words
(
    pkID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(40) NOT NULL,
    KEY (word)
)

The data was loaded into the table using the query:

LOAD DATA INFILE '/usr/share/dict/words'
INTO TABLE words (word);

This script will display a page of records each time it is run -- the number of records in a page is defined by the variable $records_per_page.

The script expects a value named "page" to be input on the URL.  That number denotes which page of $records_per_page records to display.  If no input is available on the URL, the script defaults to outputting the first page.  If the "page" input variable's value is larger than the maximum possible page size for the given number of $records_per_page, the script will use instead that maximum page size.

The script also provides at the bottom of each display page "previous" and "next" links back to itself with calculated page input values.  It will only display these links if doing so is meaningful -- for example, it will not show a "previous" link if it is displaying the first page of data.

In order to get this script to work with your table, you will, at a minimum, have to:

modify the $mysql_* variables to match your environment,
change $count_query and $data_query to something meaningful to your database schema and
change the print statement in the while loop just below the comment "output the required records" to something meaningful to your table structure

You may also have to tweak the various table tags output by the code to get everything to line up right.

CODE

<?php
/* Data paging script
   2006-01-11 by sleipnir214
   This script is in the public domain.

   CAUTION:  This script works on my system -- but it could blow yours to
   smithereens.  Therefore, no waranty is expressed or implied as to how
   safe it will be for you to use.

   Use this code with trepidation and circumspection.
*/

//variables for connecting to MySQL
$mysql_host = 'localhost';
$mysql_user = 'test';
$mysql_pass = 'test';

$mysql_db   = 'test';

//set the number of records per page
$records_per_page = 10;

//connect to MySQL
mysql_connect ($mysql_host, $mysql_user, $mysql_pass);
mysql_select_db ($mysql_db);

//find out how many records are in the table
$count_query = "SELECT count(*) from words";
$rh = mysql_query ($count_query);
list ($record_count) = mysql_fetch_array($rh);

//calculate the maximum "page" that can be displayed.
$max_pages = floor($record_count / $records_per_page);

//This logic takes care of reacting to input.
if (isset($_GET['page']))
{
    if ($_GET['page'] > 1)
    {
        if ($_GET['page'] > $max_pages)
        {
            $current_page = $max_pages;
        }
        else
        {
            $current_page = $_GET['page'];
        }
    }
    else
    {
        $current_page = 1;
    }
}
else
{
    $current_page = 1;
}

$limit_start = ($current_page - 1) * $records_per_page;

//query the database for the required records
$data_query = "SELECT * FROM words LIMIT " . $limit_start . ", " . $records_per_page;
$rh = mysql_query ($data_query);

print '<html><body><table width="100%" border="1">';

//output the required records
while ($word_data = mysql_fetch_array($rh))
{
    print '<tr>';
    print '<td align="center" width="50%">' . $word_data['pkID'] . '</td>';
    print '<td align="center" width="50%">' . $word_data['word'] . '</td>';
    print '</tr>';
}

//this is the logic for the "previous" link display
print '<tr><td width="50%" align="center">';
if ($current_page > 1)
{
    print '<a href="' . $_SERVER['PHP_SELF'] . '?page=' . ($current_page - 1) . '">previous</a>';
}
else
{
    print ' ';
}
print '</td>';

//this is the logic for the "next" link display
print '<td width="50%" align="center">';
if ($limit_start + $records_per_page < $record_count)
{
    print '<a href="' . $_SERVER['PHP_SELF'] . '?page=' . ($current_page + 1) . '">next</a>';
}
else
{
    print ' ';
}
print '</td></tr></table><body></html>';
?>

Back to PHP FAQ Index
Back to PHP Forum

My Archive

My FAQ Archive