What you're looking for is called paging, and can be as easy or as complicated as you make it. There are tons of tutorials on this subject, but I'm guessing you've read some and they didn't make much sense. I'll do my best to explain the logic behind paging.
When splitting hits (read: database-results) over several pages, you have to define two values. The values represent the interval of the hits that should be returned from the DB. You define via
LIMIT $offset, $results where $offset is how many results you skip, and $results how many results you want. For instance, if you want to display the first page, and you want to display the first 20 results, the $offset is 0, and $results is 20. You use these two values in the DB-query, and intelligent as MySQL is, it returns the results. This works in a similar fashion for all the pages you want to display the results on. For the second page, you'd use
LIMIT $offset, $results, where $offset is 20, and $results is still 20. MySQL skips the first 20 results, and displays the next 20 results. For page 3 the $offset is 40. I think you get the picture.
There is a second way to do this, if you don't like the first way. MySQL has, as you probably know, a nice feature called
OFFSET, which tells MySQL how many results to skip before pumping out the results. For page 1 you'd use
LIMIT $results OFFSET $offset, where $offset again is 0. For page 2 $offset would be 20, for page 3 it would be 40, and so on.
And how to made the system know what results to display on each page? That is where the tutorials come in, they all show ways to define them. Normally you define to display page 1 by default, but if a page is specifically asked for, you define the variable after the $_GET-value. For instance:
Code: Select all
// by default we show first page
$page = 1;
// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$page = $_GET['page'];
}
You can do the same with how many results per page you want to display, but that's beyond the basics. Maybe you've come across a forum or a site which allows you to choose how many resulsts / posts you wish to display per page via a drop-box. It's done in the same fashion as the code above.
The query that you use would be something in the lines of:
First way
Code: Select all
$sql = mysql_query("SELECT value FROM table LIMIT $offset, $resultsPerPage")
Second way
Code: Select all
$sql = mysql_query("SELECT value FROM table LIMIT $results OFFSET $offset")
In order to display the links to the appropriate pages, you count the
values in
table with count(), and use ceil($total_amount_of_rows_in_table / $results_per_page). If you have 20 results in total, that would equal to one page. If you have 30, that would equal to two pages. If you have 40, that would again equal to two pages.
Now that you know the amount of pages to display, you simply loop them out. You can use a while- or a for-loop. In the case of a while-loop, it can look something in the lines of:
Code: Select all
$page = 1;
$total_pages = ceil($total_amount_of_rows_in_db / $results_per_page)
while ($page <= $total_pages){
echo "link here to $page ";
$page++;
}
You can decide to either use page.php?page=x, where x equals the number of the page, or you can use the $offset instead, with page.php?display=x, or something in a similar fashion. It's all down to your personal prefference.
Some reading material, which I used as a refference while I wrote this (two years since last time I wrote a paging system):
Paging using LIMIT $offset, $results_per_page
Paging using LIMIT $results_per_page OFFSET $offset
edit:
The offset value can be calculated with
Code: Select all
$offset = ($pageNum - 1) * $rowsPerPage;
For page 1, that would be 0 * 20 ( = 0 ), which means page 1 skips 0 results.
For page 2, that would be 1 * 20 ( = 20), which means page 2 skips 20 results.
etc.
All codes are based on, or copied from
here.