Multiple pages with PHP
Last update on: 10-25-2008We will take the guestbook as an example and imagine that you have over 1000 messages in your data base, how you can display them in one page (it will be a very very long page).
What we will learn in this lesson is how we can divide these results by 10 results for each page.
The precedent request we used for the guestbook script was like this:
//Connect to the data base server
$db = mysql_connect($mysql_host,$mysql_user,$mysql_password) or die ("server connexion error");
//we select the mysql data base
mysql_select_db($mysql_base,$db) or die ("data base connexion error");
//la requête de sélection des messages
$req = mysql_query("SELECT date,username,email,message from guestbook_tbl Order by date Desc ") or die ("request error ".mysql_error());
$db = mysql_connect($mysql_host,$mysql_user,$mysql_password) or die ("server connexion error");
//we select the mysql data base
mysql_select_db($mysql_base,$db) or die ("data base connexion error");
//la requête de sélection des messages
$req = mysql_query("SELECT date,username,email,message from guestbook_tbl Order by date Desc ") or die ("request error ".mysql_error());
But with this request you will get all the messages in one page, it will be a problem if you have hundreds of messages, so lets fix this
Note: this can be used to any script when we request records from the data base, as the previous directory.
First we count the messages that we have in the data base.
//Connect to the data base server
$db = mysql_connect($mysql_host,$mysql_user,$mysql_password) or die ("server connexion error");
//we select the mysql data base
mysql_select_db($mysql_base,$db) or die ("data base connexion error");
//we select all the guestbook records
$req_limit = mysql_query("Select id from guestbook_tbl");
$result = mysql_numrows($req_limit);//mysql_numrows() give us the result of the request
// now we will use the result to limit the displayed messages
$page_limit = '10'; //we chose the number of messages by page
// here we divide the total by the number of messages we choose
$page_number = $result / $page_limit;
// we round the number of pages to avoid commas.
$total_number = ceil($page_number);
// here we take of one page because the first page will be displyed is number one
$number = $total_number - 1;
// if the variable number_page is equal or defferent to 0
if(isset($_GET['page_number']) || $_GET['page_number'] != '0' )
{
// multiplies the page limit with the current number page on the url
$mysql_limit = $page_limit * $_GET['page_number'];
}
else{ // no variable number_page
$mysql_limit = '0'; // the limit is 0
}
$db = mysql_connect($mysql_host,$mysql_user,$mysql_password) or die ("server connexion error");
//we select the mysql data base
mysql_select_db($mysql_base,$db) or die ("data base connexion error");
//we select all the guestbook records
$req_limit = mysql_query("Select id from guestbook_tbl");
$result = mysql_numrows($req_limit);//mysql_numrows() give us the result of the request
// now we will use the result to limit the displayed messages
$page_limit = '10'; //we chose the number of messages by page
// here we divide the total by the number of messages we choose
$page_number = $result / $page_limit;
// we round the number of pages to avoid commas.
$total_number = ceil($page_number);
// here we take of one page because the first page will be displyed is number one
$number = $total_number - 1;
// if the variable number_page is equal or defferent to 0
if(isset($_GET['page_number']) || $_GET['page_number'] != '0' )
{
// multiplies the page limit with the current number page on the url
$mysql_limit = $page_limit * $_GET['page_number'];
}
else{ // no variable number_page
$mysql_limit = '0'; // the limit is 0
}
Voilà!, We did the hard part, counted the number of the pages and set the display limits, lets go to the output request.
The display request.
//the 1st request we used before
$req = mysql_query("SELECT date,username,email,message from guestbook_tbl Order by date Desc ") or die ("request error");
//the new request updated with limits
$req = mysql_query("SELECT date,username,email,message from guestbook_tb Order by date Desc Limit $mysql_limit , $page_limit") or die ("request error");
$req = mysql_query("SELECT date,username,email,message from guestbook_tbl Order by date Desc ") or die ("request error");
//the new request updated with limits
$req = mysql_query("SELECT date,username,email,message from guestbook_tb Order by date Desc Limit $mysql_limit , $page_limit") or die ("request error");
For the new request, we set limits, with the limit of messages by page ($page_limit) and the limit that will be defined by the counter ($mysql_limit).
The last step is the page links
// If the page number different of 0 and if the page_number is unset
if( $number != '0' && empty($_GET['page_number']))
{
print '<a href="display.php?page_number=1">Next page</a>'; // we set the page_number to 1
}
// in this condition, the variable page_number is set and its value is less than $number
elseif($number !='0' && isset($_GET['page_number']) && $_GET['page_number'] < $number)
{
$next = $_GET['page_number'] + 1; // add 1 to the current page number
print '<a href="display.php?page_number='.$suivant.'">next page</a>'; //The link for the next pages
// go back to the precedent page, we used a java-script code to do it print ' <a href="javascript: history.back();">Previous page</a>';
}
// here, the link that will be displayed when the page number is reched
elseif( $number !='0' && isset($_GET['page_number']) && $_GET['page_number'] >= $number )
{
print '<a href="javascript: history.back();">Previous page</a>';
}
if( $number != '0' && empty($_GET['page_number']))
{
print '<a href="display.php?page_number=1">Next page</a>'; // we set the page_number to 1
}
// in this condition, the variable page_number is set and its value is less than $number
elseif($number !='0' && isset($_GET['page_number']) && $_GET['page_number'] < $number)
{
$next = $_GET['page_number'] + 1; // add 1 to the current page number
print '<a href="display.php?page_number='.$suivant.'">next page</a>'; //The link for the next pages
// go back to the precedent page, we used a java-script code to do it print ' <a href="javascript: history.back();">Previous page</a>';
}
// here, the link that will be displayed when the page number is reched
elseif( $number !='0' && isset($_GET['page_number']) && $_GET['page_number'] >= $number )
{
print '<a href="javascript: history.back();">Previous page</a>';
}
And that set, you have every thing here you need just to insert the code in your guestbook.
It will be similar to
//----------------------------------------------------------------
//
// Page of displying messages
//
//----------------------------------------------------------------
$mysql_host = 'localhost'; // Host name
$mysql_user = 'your login'; // Your login
$mysql_password = 'password'; // Your password
$mysql_base = 'data base name'; // enter the data base name
//----------------------------------------------------------------
//Connect to the data base server
$db = mysql_connect($mysql_host,$mysql_user,$mysql_password) or die ("server connexion error");
//we select the mysql data base
mysql_select_db($mysql_base,$db) or die ("data base connexion error");
//we select all the guestbook records
$req_limit = mysql_query("Select id from guestbook_tbl");
$result = mysql_numrows($req_limit);//mysql_numrows() give us the result of the request
// now we will use the result to limit the displayed messages
$page_limit = '10'; //we chose the number of messages by page
// here we divide the total by the number of messages we choose
$page_number = $result / $page_limit;
// we round the number of pages to avoid commas.
$total_number = ceil($page_number);
// here we take of one page because the first page will be displyed is number one
$number = $total_number - 1;
// if the variable number_page is equal or defferent to 0
if(isset($_GET['page_number']) || $_GET['page_number'] != '0' )
{
// multiplies the page limit with the current number page on the url
$mysql_limit = $page_limit * $_GET['page_number'];
}
else{ // no variable number_page
$mysql_limit = '0'; // the limit is 0
}
/*----------------------------------------------------------------
We insert the links before the messages or after them
------------------------------------------------------------------*/
// If the page number different of 0 and if the page_number is unset
if( $number != '0' && empty($_GET['page_number']))
{
print '<a href="display.php?page_number=1">Next page</a>'; // we set the page_number to 1
}
// in this condition, the variable page_number is set and its value is less than $number
elseif($number !='0' && isset($_GET['page_number']) && $_GET['page_number'] < $number)
{
$next = $_GET['page_number'] + 1; // add 1 to the current page number
print '<a href="display.php?page_number='.$suivant.'">next page</a>'; //The link for the next pages
// go back to the precedent page, we used a java-script code to do it
print ' <a href="javascript: history.back();">Previous page</a>';
}
// here, the link that will be displayed when the page number is reched
elseif( $number !='0' && isset($_GET['page_number']) && $_GET['page_number'] >= $number )
{
print '<a href="javascript: history.back();">Previous page</a>';
}
/*----------------------------------------------------------------
the rest of the guestbook with the new selection request
------------------------------------------------------------------*/
$req = mysql_query("SELECT date,username,email,message from livre_tbl Order by date Desc Limit $mysql_limit , $page_limit") or die ("erreur requête");
//we loop the result, and stock it an array with mysql_fetch_array()
while( $content = mysql_fetch_array ($req))
{
//we display the result
print 'Posted on : '.$content['date'].'<br / >';
print 'By : <a href="mailto:'.$content['email'].'">'.$content['username'].'</a><br / >';
print 'Message : <br />'.$content[message].'<br / >';
print '<hr />';
}
//We close the connexion
mysql_close();
//----------------- End of the SCRIPT --------------------------------
PHP and MySQL's lessons:
Introduction To PHPGet Started With PHP
PHP Variables
PHP Variables Of Environment
PHP Conditions
PHP Looping
PHP Cookies
PHP Working With Dates
PHP Working With Arrays
PHP Working With Files
PHP Play With Strings
PHP And Forms
Send Emails With PHP
The Include Statement
Get Started With MySQL
MySQL Update And Delete
The WHERE Clause
MySQL Functions
Guestbook Script
Websites Directory Script
Multiple Pages With PHP
Create Your Forum With Php