I want to have pagination in my php page. I'm getting the results from mysql database and showing them in table. Could anyone please help me how to do that without using javascript or jquery.
Lets say you want 10 results per page, but want to scroll through them.
You could add LIMIT 0,10 into your mysql query.
Then add a link down the bottom of the table to go to the next set of results. (something like table.php?page=2)
On this page, change the query to LIMIT 10,20.
This should be enough information to get you started.
If DB_Pagerisn't available,you can use the pc_print_link( )and pc_indexed_links( )functions shown in Examples 10-2 and 10-3 to produce properly formatted links.
Example 10-2.
function pc_print_link($inactive,$text,$offset='') {
if ($inactive) {
printf('<font color="#666666">%s</font>',$text);
} else {
printf('<a
href="%s?offset=%d">%s</a>',$_SERVER['PHP_SELF'],$offset,$text);
}
}
function pc_indexed_links($total,$offset,$per_page) {
$separator =' | ';
// print "<<Prev" link
pc_print_link($offset == 1, '<<Prev', $offset -$per_page);
// print all groupings except last one
for ($start = 1, $end = $per_page;
$end < $total;
$start += $per_page, $end += $per_page) {
print $separator;
pc_print_link($offset == $start, "$start-$end", $start);
}
/* print the last grouping -* at this point, $start points to the element at the beginning
* of the la
st grouping
*/
/* the text should only contain a range if there's more than
* one element on the last page. For example, the last grouping
* of 11 elements with 5 per page should just say "11", not "11-11"
*/
$end = ($total > $start) ? "-$total" : '';
print $separator;
pc_print_link($offset == $start, "$start$end", $start);
// print "Next>>" link
print $separator;
pc_print_link($offset == $start, 'Next>>',$offset + $per_page);
}
To use these functions, retrieve the correct subset of the data using DB::modifyLimitQuery( )and then print it out. Call pc_indexed_links( )to display the indexed links:
$offset = intval($_REQUEST['offset']);
if (! $offset) { $offset = 1; }
$per_page = 5;
$total = $dbh->getOne('SELECT COUNT(*)FROM zodiac');
$sql = $dbh->modifyLimitQuery('SELECT * FROM zodiac ORDER BY id',
$offset -1,$per_page);
$ar = $dbh->getAll($sql);
foreach ($ar as $k => $v) {
print "$v->sign, $v->symbol ($v->id)<br>";
}
pc_indexed_links($total,$offset,$per_page);
printf("<br>(Displaying %d -%d of %d)",$offset,$offset+$k,$total);
After connecting to the database, you need to make sure $offsethas an appropriate value. $offsetis the beginning record in the result set that should be displayed. To start at the beginning of the result set, $offsetshould be 1. The variable $per_pageis set to how many records to display on each page, and $totalis the total number of records in the entire result set. For this example, all the Zodiac recordsare displayed, so $totalis set to the count of all the rows in the entire table. The SQL query that retrieves information in the proper order is: SELECT * FROM zodiac ORDER BY id Use modifyLimitQuery( )to restrict the rows being retrieved. You'll want to retrieve $per_pagerows, starting at $offset -1, because the first row is 0, not 1, to the database. The modifyLimitQuery( )method applies the correct database-specific logic to restrict what rows are returned by the query. The relevant rows are retrieved by $dbh->getAll($sql), and then information is displayed from each row. After the rows, pc_indexed_links( )provides navigation links.