Most of the time it is required to show results on per page basis. Instead of showing all the records at once, it is better to show results page by page so that it is easy for the user to find relavant records and will have a quick response from the server. Paging is mostly based on mysql's limit clause. The limit keyword in mysql allows to fetch a desired number of records from the database, takes two parameters i.e. where to start and how many records to fetch. Consider the following mysql query:
<?
$start=13;
$total=9;
$result=mysql_query("select * from table_name limit $start,$total");
?>
The above mysql query will fetch 9 records from the database(if available) starting from record 13. The first record starts at 0, so if you are to fetch first 5 records you will use
limit 0,5.
The technique discussed here for partitioning results into pages has two main parts. The first part fetches records for the current page and calculates total number of pages. The second part shows page numbers to jump to a different page.
Lets now have a look at the first part
Part 1 - Fetch records for this page & do page calculation
<?
$current_page=intval($_REQUEST['page']); //Receive the page number as parameter
if($current_page<1) //If page number is missing set it to 1
$current_page=1;
$records_per_page=intval($_REQUEST['num']); //Number of records per page
if($records_per_page<10 || $records_per_page>20) //Set the limits for records per page
$records_per_page=10;
$start=($current_page-1)*$records_per_page; //Starting record ( will be used in mysql query)
$query="select * from table_name limit $start,$total";
$result=mysql_query($query);
$records_on_page=mysql_num_rows($result);
$total_result=mysql_query("select count(*) as Total from table_name"); //Find total records to find total pages
$total_row=mysql_fetch_array($total_result);
$total_records=$total_row['Total'];
$total_pages = ceil($total_records/$records_per_page);
?>
The ceil function used in the last line tackles the last page. To understand its working, consider the following scenario:
Total Records: 43
Results per page: 10
How many pages should there be?
Answer is 5, because the last page will have only 3 records. When we divide 43 by 10, we get 4.3 and applying the ceil function to 4.3 gives 5 which is what we want.
The second part displays the pages calculated in the first part. In addition to displaying page numbers, the code below also keeps track of the following:
- Do not show more than 10 page numbers, even if there are more than 10 pages
- Page numbers change dynamically i.e if you are on first page, you will see page numbers from 1 to 10. But if your are viewing page 27, you will have page numbers from 23 to 32
- The current page has no link
Part - 2 Display page numbers
<?
$page=1;
if($current_page>=5)
$page=$current_page-4;
$count=$page+9;
while($page<=$count && $page<=$total_pages){
if($page==$current_page){
echo '$page'; // no link, current page is not clickable
}
else{
echo '<a href="results.php?page=$page>$page</a>';
}
$page++;
}
?>
