page numbering explained
Moderator: General Moderators
page numbering explained
hey does anyone no any sites that realy explain exactly about page numbering and teach about them realy well using mysql ? any help grate full
-
litebearer
- Forum Contributor
- Posts: 194
- Joined: Sat Mar 27, 2004 5:54 am
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
The term you should search for is "pagination"
http://www.google.com/search?hl=en&ie=U ... help&meta=

http://www.google.com/search?hl=en&ie=U ... help&meta=
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
The implementation of a pager is pretty straightforward. You just need to know the the number of total records, the number of records per page, and the page number you want to show. The number of records per page is something you define:
$records_per_page = 25;
The number of total records you need to get by doing a query (in MySQL) like:
$result = $db->query("SELECT COUNT(*) AS total_records FROM mytable");
$row = $result->fetchRow();
$total_records = $row['total_records'];
The page number is usually passed in the url (e.g. http://www.mysite.com/pager.php?page=2). Then you need to get the page number like (remember you ALWAYS have to filter ALL data from the request):
$page_number = intval($_REQUEST['page']);
Once you have these three values you need to do the calculation to figure out what the first record you want to fetch is:
if ($page_number > 0) {
$first_record = ($page_number - 1) * $records_per_page.
} else {
$first_record = 0;
}
You should add checks to see if you are trying to get records past the last record. I'd recommend calculating what the last page number is and checking $page_number against that.
Finally you need to do your query and fetch the data which is like this:
$result = $db->query("SELECT * FROM mytable LIMIT $records_per_page OFFSET $first_record");
while ($row = $result->fetchRow()) {
... // build HTML row
}
The obvious performance optimization is to only do the "SELECT COUNT(*)" the first time the pager is accessed. Store the total number of records and any other calculated values in the session which is (usually) much faster than the query. You can assume that you only need to do the "SELECT COUNT(*)" when there is no page specified in the request or no value set in the session.
Put it all together, add error checking to the database calls, bounds checking to the calculations and off you go. Building the URLs is easy (e.g. "<a href=\"?page=" .( $page_number + 1) . '">Next Page</a>'). Again, you can do bounds checking to see if there is a previous or next page.
$records_per_page = 25;
The number of total records you need to get by doing a query (in MySQL) like:
$result = $db->query("SELECT COUNT(*) AS total_records FROM mytable");
$row = $result->fetchRow();
$total_records = $row['total_records'];
The page number is usually passed in the url (e.g. http://www.mysite.com/pager.php?page=2). Then you need to get the page number like (remember you ALWAYS have to filter ALL data from the request):
$page_number = intval($_REQUEST['page']);
Once you have these three values you need to do the calculation to figure out what the first record you want to fetch is:
if ($page_number > 0) {
$first_record = ($page_number - 1) * $records_per_page.
} else {
$first_record = 0;
}
You should add checks to see if you are trying to get records past the last record. I'd recommend calculating what the last page number is and checking $page_number against that.
Finally you need to do your query and fetch the data which is like this:
$result = $db->query("SELECT * FROM mytable LIMIT $records_per_page OFFSET $first_record");
while ($row = $result->fetchRow()) {
... // build HTML row
}
The obvious performance optimization is to only do the "SELECT COUNT(*)" the first time the pager is accessed. Store the total number of records and any other calculated values in the session which is (usually) much faster than the query. You can assume that you only need to do the "SELECT COUNT(*)" when there is no page specified in the request or no value set in the session.
Put it all together, add error checking to the database calls, bounds checking to the calculations and off you go. Building the URLs is easy (e.g. "<a href=\"?page=" .( $page_number + 1) . '">Next Page</a>'). Again, you can do bounds checking to see if there is a previous or next page.
i no its called pagination but thanks aan im jus lookin for a realy good tutorial that explains becuase the ones ive found have to much pointless stuff i want it stripeed to its most basic and they never have the original display data either they jus tell you to do it an i dont get it all so if anyone knwos a good tutorial please submit here 
There's an Indexer class here which handles all the arithmetic.
It's intended to be used with a QueryIterator which picks out the rows you want from a full result set. Standard QueryIterators need a minor modification to allow them to seek to an offset.
It's intended to be used with a QueryIterator which picks out the rows you want from a full result set. Standard QueryIterators need a minor modification to allow them to seek to an offset.