Ok, here is the code i have for my pagination:
$SQL = "SELECT
cpc.product_id,
cp.product_internal_ref,
cp.product_name,
cpa.product_sale_price,
cpa.is_product_service,
cpa.product_service_price,
cpi.image_name,
cpi.image_ext
FROM catalog_products_categories cpc
JOIN catalog_products cp ON cp.product_id = cpc.product_id
JOIN catalog_products_attributes cpa ON cpa.product_id = cpc.product_id
LEFT JOIN catalog_products_images cpi ON cpi.product_id = cpc.product_id
WHERE cpc.category_id = ".$catID;
// PAGINATOR SECTION
if($paginatorVARS['paginatorACTION'] == "next") {
$SQL .= " AND cpc.product_id > ".$paginatorVARS['paginatorGOID']." ";
$SQL .= "GROUP BY cpc.product_id ORDER BY cpc.product_id ASC LIMIT ".$paginatorVARS['catalogPaginatorPAGEROWS'];
}
elseif($paginatorVARS['paginatorACTION'] == "prev") {
$SQL .= " AND cpc.product_id < ".$paginatorVARS['paginatorGOID']." ";
$SQL .= "GROUP BY cpc.product_id ORDER BY cpc.product_id DESC LIMIT ".$paginatorVARS['catalogPaginatorPAGEROWS'];
}
// END PAGINATOR SECTION
I used the method described here: http://www.slideshare.net/Eweaver/efficient-pagination-using-mysql but i can't seam to find a way to also sort by other columns like for example by cpa.product_sale_price. If i do : ORDER BY cpc.product_id ASC, cpa.product_sale_price DESC/ASC it will break the paginator next results i dont know what happens... Please help!!
