INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
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
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
"...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..."
Where in the world do Tek-Tips members come from?
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,
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:
You may also have to tweak the various table tags output by the code to get everything to line up right.
- 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
/* 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);
//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 ($_GET['page'] > 1)
if ($_GET['page'] > $max_pages)
$current_page = $max_pages;
$current_page = $_GET['page'];
$current_page = 1;
$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 '<td align="center" width="50%">' . $word_data['pkID'] . '</td>';
print '<td align="center" width="50%">' . $word_data['word'] . '</td>';
//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>';
print ' ';
//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>';
print ' ';
Back to PHP FAQ Index
Back to PHP Forum
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close