Order by column with multiple values
Posted on 2012-03-22
I have a products table in my db which has a category field which relates to another table.
prodId prodName prodCat
1 test 2
2 test 2 2
3 test 3 1
4 test 4 1
5 test 5 2
The category table has id, name and order:
catID catName catOrder
1 cat1 3,4
2 cat2 1,2,5
The catOrder field is just a collection of the product ID's, what I want is to display the products table but in the order which appears in the catOrder column.
I currently have the following in my SQL:
SELECT store.*, cat.* FROM (store LEFT JOIN cat ON store.prodCat = cat.catID) WHERE store.prodCat = '" . $_GET['cat'] . "'
How would I go about splitting the catOrder field so that I can order by this field?