Posted by Tyno Gendo on 04/19/07 10:04

Man-wai Chang wrote:
>> I usually do two queries, one to get how many results there would be
>> without pagination, and another to get the results within the page range.
>>
>> Don't know if this is the best way to do it though, i'd be interested
>> in others replies myself.
>
> Mind to publish your codes? :)
>

define('PAGE_SIZE', 5);

$cat_id = @(int)$_REQUEST["cat_id"];
$page = @(int)$_REQUEST["page"];

/*
* find out how many recipes in total for paging, if request
* page>total pages then reset page variable to 0
*
*/
$sql_pagesize = "SELECT COUNT(cr.catID) AS count FROM catrecipe cr,
recipe WHERE cr.catID = recipe.catID AND cr.catID = " . (int)$cat_id;
$ds = mysql_query($sql_pagesize) or trigger_error("Unable to query
database: " . mysql_error());
$dr = mysql_fetch_assoc($ds);
$article_count = $dr["count"];
$pages_count = ceil($article_count/PAGE_SIZE);
if ( $page > $pages_count ) {
$page = 0;
}
@mysql_free_result($ds);
unset($dr);
unset($ds);

/*
* work out the start and end for the SQL LIMIT in the paging option
*/
$start = 0+($page*PAGE_SIZE);
$end = $start+PAGE_SIZE;

/*
* look up the categories for the current page being viewed and display
them
*
*/

$sql = "SELECT cr.catID, cat_title, recipeID, recipe_title
FROM catrecipe cr, recipe
WHERE cr.catID = recipe.catID AND cr.catID = " . (int)$cat_id
.. " ORDER BY recipeID DESC LIMIT {$start}, {$end}";
$ds = mysql_query($sql) or trigger_error("Unable to query database: "
.. mysql_error());

[Back to original message]