Forum Moderators: coopster
I have two tables: "products" and "product_cat".
The "products" are products, each with a unique ID number, description, price, etc.
the other table, "product_cat", references categories (also numbered) and puts products into them. It looks like this:
prcatID product category
1 4 4
2 4 2
3 5 3
4 6 3
5 7 3
6 8 3
In the example above, you see there are 4 items in category 3, one in category 2, and one in category 4. Product 4 appears twice, belonging in both categories 2 and 4.
This is how I'm doing it now:
SELECT * FROM product_cat WHERE category=".$cat.""
while ($row=[each itemID found]){
SELECT * FROM products WHERE ID=".$row['ID']
// print out the product info
}
And it's working OK like that, although I suspect I'm not using the most efficient method...
Now, I need to ORDER the products using a new field in "products" called "preferredplacement". in the "preferredplacement" field, products are given a float number between 0 and 1 (0 is the default), so that preferred products will jump to the top of the list.
Apparently, I can't just ORDER BY the placement score using the method above... I'd like the database to cross-reference categories and sort the products.
I hope I've explained this well enough. Can you help?
SELECT
*
FROM product_cat
INNER JOIN products
ON (product_cat.prodid = products.prodid)
WHERE category = $category
ORDER BY preferredplacement
;
