Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data paging

Database (MySQL)

Data paging

by  sleipnir214  Posted    (Edited  )
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:[ol][li]modify the $mysql_* variables to match your environment,[/li][li]change $count_query and $data_query to something meaningful to your database schema and[/li][li]change the print statement in the while loop just below the comment "output the required records" to something meaningful to your table structure[/li][/ol]

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>';
?>
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top