|
Combining the power of a mySQL database with the flexibility of PHP allows even
the most amateurish developer to create websites loaded with thousands of pages
of information. But what do you do if you don't want to display all your QUERY
data in one large lump? Can you imagine Google if it showed you 30,000
search results on a single page? To get around this problem we can
use PHP's integral LIMIT function to restrict the number of results displayed on
one page, and then set start and end points to allow previous/next page links to
work. This may initially sound quite difficult given PHP's automatic
nature, but once you understand the concept behind simple pagination you'll
wonder how the technique eluded you in the first place.
Manipulating the LIMIT Function
This entire tutorial hinges on our use of the LIMIT function in mySQL. LIMIT is
an optional instruction that can be added onto the mysql_query() function to
limit the number of results processed. It's format is as follows:
LIMIT [a], [b]
[a] is the number you want to start retrieving records from, and [b]
is the number of records you want to retrieve. LIMIT can also be used without
the [a] operator. i.e.
LIMIT [b]
This effectively just retrieves the first [b] records from the start of
the query, because you haven’t specified [a] as the starting point. But
how can we use this to our advantage? The answer is quite simple - by using PHP
to restrict the number of retrieved results into equal-sized lumps, thus
allowing us to easily paginate our content automatically!
Here’s the method behind the madness: We have one page, and we are going to use
two main variables to control the output of our pagination: “page” and “number”.
Our page variable will determine which page we are viewing, and it will
correspond to our [a] operator in the mySQL statement. Our number
variable will set the number of records to retrieve from the start point, [a],
and will correspond with the [b] operator in mySQL syntax. In our first
example, the [b] value will be hard-coded, but later in the tutorial I
will explain how this can be changed to make your site even more dynamic.
In this tutorial, I will assume that you have already created the database and
specified the code to open the database correctly. This is referred to by the
config.php file included in the code (which you will have to write yourself,
of course!). Now, let’s write a little script which will take ALL of our records
and print them on a single page.
<?
include ‘config.php’;
$query = mysql_query(“SELECT * FROM OurTable”) or die(mysql_error());
while ($row = mysql_fetch_array ($query) ) {
echo ‘<p>’.$row[‘OurData’].’</p>’;
}
?> |
As you can see, this script simply takes everything in the table and throws it
onto one page, albeit on separate lines. This isn't too useful, so let’s
move on to limiting our results with the [b] operand.
In the next example, I’ll show you how to show only a set amount of records on a
page, rather than the whole lot. This can be useful in a variety of
circumstances, such as displaying only the latest entries in your weblog, etc.
The $limit variable below dynamic - In this case, if the URL specifies a
value (i.e. http://blah.com/index.php?number=15), the record limit
will be set to 15, but if its blank or empty, the value will default to 10.
<?
include ‘config.php’;
if ( $_GET[‘number’] == NULL ){
$limit = 10;
} else {
$limit = $_GET[‘number’];
}
$query = mysql_query(“SELECT * FROM OurTable LIMIT ‘$limit’”) or
die(mysql_error());
while ($row = mysql_fetch_array ($query) ) {
echo ‘<p>’.$row[‘OurData’].’</p>’;
}
?> |
Easy Automatic Pagination
Congratulations! You’ve just successfully written a LIMITing script!
We now want to use this technique to paginate our database, which undoubtedly
contains many thousands of entries (if it doesn't, just pretend otherwise ;).
As discussed earlier, this is where the [a] operand comes into
play. Take a look at the example below to see how I have approached the
problem:
<?
include ‘config.php’;
if ( $_GET[‘number’] == NULL ){
$limit = 10;
} else {
$limit = $_GET[‘number’];
}
if ($_GET[‘page’]==NULL){
$start = 0;
} else {
$start = $_GET[‘page’];
}
// the if above, states that if the variable in the URL named
page has a value, $start will be set to it, otherwise by default it
is set to 0, meaning that it will start at the beginning of the
table by default.
if($_GET[‘page’] < 0){
header(“Location: index.php?page=0&limit=”.$limit);
}
// this if above checks that the start variable isn’t negative,
because if it is, our script will screw up. If it finds that it is,
it redirects to the default page where the start is 0.
$query = mysql_query(“SELECT * FROM OurTable LIMIT ‘$start’,
‘$limit’”) or die(mysql_error());
// the query above now has the [a] and [b] operators
incorporated, making our script paginated. All we need to do now is
add the next and previous links.
while ($row = mysql_fetch_array ($query) ) {
echo ‘<p>’.$row[‘OurData’].’</p>’;
}
$previous = $start + $limit;
$next = $start - $limit;
echo '<a href="index.php?start='.$previous.'">Previous Page</a> - ';
echo '<a href="index.php?start='.$next.'">Next Page</a>';
// the set of statements above displays the previous and next
page links
?> |
And that’s it! You’ve successfully just coded yourself a pagination script! Our
thousands of entries are now organized into pages of 10 (by default), or by the
number specified by the user. I haven’t incorporated a section to let the user
choose how many records to display yet, but that’s not hard, just chuck some PHP
enhanced links onto your page like
these:
<?
echo ‘<a href=”index.php?page=’.$start.’&limit=10”>Display 10
Records</a>’;
echo ‘<a href=”index.php?page=’.$start.’&limit=20”>Display 20
Records</a>’;
echo ‘<a href=”index.php?page=’.$start.’&limit=30”>Display 30
Records</a>’;
echo ‘<a href=”index.php?page=’.$start.’&limit=40”>Display 40
Records</a>’;
?> |
And so on. Maybe add
cookies and/or sessions to save user choices if you really want to be
keen! Well, that’s it from me for the moment! This
tutorial was written for Intermediate level PHP programmers not beginners,
so the number functions shown in the next and previous links section
shouldn’t be too hard to understand and adjust according to your precise
needs.
If you have trouble understanding this tutorial or have a problem with any
aspect of it, I am available on the forums, so either click Creative Forums
in the navigation or click my name below and choose Private Message. You may
have to register.
- Tutorial written by Scrowler
|
