×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!
  • Students Click Here

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

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Database (MySQL)

Data paging by sleipnir214
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:
  1. modify the $mysql_* variables to match your environment,
  2. change $count_query and $data_query to something meaningful to your database schema and
  3. 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 '&nbsp;';
}
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 '&nbsp;';
}
print '</td></tr></table><body></html>';
?>

Back to PHP FAQ Index
Back to PHP Forum

My Archive

Close Box

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close