|
|
|
|
|
|
| |
Let's chat about it
I read several PHP forums on a daily basis. One of the most common requests I see is "How do I add previous and next links to my pages?". Hence this tutorial. When we get done here, you should be able to add previous and next links to your pages that you show database results with.
For the purposes of this tutorial, I am going to assume that you are already familiar with basic PHP syntax and know how to use PHP to query a database. For the purposes of this tutorial, we will be using mySQL but the theory here should apply to any database.
For some reference material as we go along, you can look at the mysql functions and the SELECT command.
The LIMIT clause
The LIMIT clause of the SQL SELECT statement is going to be our bread and butter here. It is how we are going to retrieve the exact rows that we want from the database.
LIMIT takes either one or two arguments. With one argument, it limits the amount of returned rows by that number. |
|
SELECT * FROM table LIMIT 5
|
|
|
The proceeding SQL statement returns the first 5 rows of the table. If we use LIMIT with two arguments, the first argument specifies what row to start on and the second is how many rows to return. Note that rows in SQL start with 0 (zero), not 1 (one). |
|
SELECT * FROM table LIMIT 5, 5
|
|
|
|
That last SQL statement will return rows 6 through 10 from the database.
The LIMIT clause is not difficult, but it is imperative that you understand how it works in order to accomplish our goal.
Starting off with $start
Ok, so now we now what we are going to use to obtain the proper rows from the database. The question is, how do we know what to limit it by? Simple, we will pass a variable called $start from page to page. Now, let's start putting a little PHP code down. |
|
<?PHP
$query = "SELECT * FROM table LIMIT " . $start . ", 10";
?>
|
|
|
Ok, that's what our query is going to look like. Simple as that. This will select 10 rows from the table starting at whatever position $start is equal to.
When you are handing out your url, you don't want to give someone this: http://myserver.com/?start=0. That's just plain ugly. So, we need to assign an initial value to $start if it doesn't have one.
|
|
<?PHP
if(!isset($start)) $start = 0;
?>
|
|
|
Easy! Now, when someone hits our page, $start will be set to 0 (zero).
Building the Previous and Next Links
Ok, down to the part you've been looking forward to. Let's actually make those Previous and Next links.
First of all, let's think about it for a second. Say you have a variable named $start (we do), so you know the first row someone is looking at and you know how many rows total there are in your result (we will) then you can easily determine if Previous or Next links are needed.
If $start is higher than 0 (zero), then we need to display a Previous Link so that people can see records before $start. If the number of rows in your result is greater than $start plus the number of rows we are displaying (in our examples 10) we need to display a Next link. This is the basis of our code logic. If you got this, writing the code is easy.
|
|
<?PHP
if($start > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 10) .
"\">Previous</a><BR>\n";
if($numrows > ($start + 10))
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 10) .
"\">Next</a><BR>\n";
?>
|
|
|
Putting it all together
Ok, now that we have all the parts that we need, let's put them all together!
|
|
<?PHP
if(!isset($start)) $start = 0;
$query = "SELECT * FROM table LIMIT " . $start . ", 10";
//do database connection
$result = mysql_query($query); //you should do error checking
//display data
//this code was wrong, I did not have the second query. Thanks to Plaggy Pig.
// need another query to get the total amount of rows in our table
$query = "SELECT count(*) as count FROM table";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$numrows = $row['count'];
if($start > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 10) .
"\">Previous</a><BR>\n";
if($numrows > ($start + 10))
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 10) .
"\">Next</a><BR>\n";
?>
|
|
|
Man is that simple stuff or what? Of course, there is a lot you can do with this other than what I have shown. But, this should get you well on your way to displaying Previous and Next links on your website.
|
|
|
|
| Static HTML Generation With PHP
Categories : PHP, HTML and PHP | | | Webstatistics with Redirectors
Categories : PHP, HTML, HTML and PHP | | | Multicolumn Output from a Database with PHP
Categories : PHP, Databases, HTML and PHP, MySQL | | | Sending Form Data in EMail
Categories : PHP, Email, HTML and PHP | | | tracking where and what on your site people are clicking
Categories : PHP, MySQL, HTML and PHP, HTML | | | How To add paging (Pagination) with PHP and MySQL
Categories : PHP, Beginner Guides, Databases, MySQL, HTML and PHP | | | Uploading files to the server with PHP
Categories : PHP, File System, HTML and PHP, HTTP | | | Alternating row colors with PHP and mySQL
Categories : PHP, Databases, MySQL, HTML and PHP | | | Making PHP Forms Object-Oriented
Categories : PHP, HTML and PHP, Object Oriented | | | Working with Dates and Times in PHP
Categories : PHP, Date Time | | | 10 PHP Functions I Bet You Didn't Know About!
Categories : PHP, PHP Functions, Filesystem, Arrays, Errors and Logging | | | Date Arithmetic With MySQL
Categories : PHP, Databases, MySQL, Date Time | | | Using the .NET Assembly in PHP
Categories : PHP, .NET | | | Aspect-Oriented Programming and PHP
Categories : PHP, Aspect Oriented Programming | | | Saving Images in MySQL
Categories : MySQL, PHP, Graphics, Databases | |
| |
|
|
