Pagination With Sorting Order
I have a catalog of products that each can exist in multiple sub_categories. The products are displayed across several pages with pagination. The problem is that there is a sort order associated with each product (1, 2, 3, etc...), but there are duplicate sort numbers. For instance, many products may have a sort number of 5, or 0. Without pagination it works fine, but with pagination I'm getting products repeated across pages, and some products not showing up.
Here are the two tables being referenced:
id, product_name, sort
id, product_id, category, sub_category
Here's the SQL statement that takes place after pagination determines the total number of rows:
As is now, several products are repeated on subsequent pages and an equal number of other products don't show at all.
$query = mysql_query("SELECT sub_categories.product_id, products.sort, products.product_name, FROM sub_categories, products WHERE sub_categories.category = '$category' AND sub_categories.product_id = products.id ORDER BY products.sort ASC LIMIT $offset, $rowsperpage");
Any suggestions on making this work or maybe modifying the sorting method?
$query = mysql_query("SELECT DISTINCT sub_categories.product_id, products.sort, products.product_name
FROM sub_categories, products
WHERE sub_categories.category = '$category' AND sub_categories.product_id = products.id
ORDER BY products.sort ASC LIMIT $offset, $rowsperpage");
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)