1

I'm trying to create a database where products might have more than one category assigned to them and ordered in a set way, but I'm struggling to come up with a solid way to display the results, for example:

I have a database which contains product data and it includes columns - prod_cat_1, prod_cat_2, prod_cat_1_order, prod_cat_2_order

prod_cat_1 = primary category
prod_cat_2 = secondary category

prod_cat_1_order & prod_cat_2_order = the order for each column

The above columns are set to INT(11).

So say I have this data:

prod_name    prod_cat_1    prod_cat_2    prod_cat_1_order    prod_cat_2_order
---------    ----------    ----------    ----------------    ----------------
product 1    cat 1         cat 2         2                   2
product 2    cat 1         cat 3         3                   1
product 3    cat 1                       1                   
product 4    cat 2         cat 1         2                   2
product 5    cat 3                       2                   
product 6    cat 3         cat 2         3                   1
product 7    cat 2         cat 3         1                   2
product 8    cat 1                       4                   
product 9    cat 3         cat 1         1                   1

I want to order by prod_cat_1_order first and then by prod_cat_2_order.

I'm aware of:

ORDER BY prod_cat_1_order, prod_cat_2_order

But this outputs it incorrectly, as example for cat 1 below:

product 3
product 9
product 1
product 4
product 2
product 8

Also another issue I have faced is if I query cat 1 it will take all values in prod_cat_1_order into consideration, for example it would output like this:

product 3
product 9
product 1
product 4
product 2
product 8

I want to order all products assigned with cat 1 by prod_cat_1 first so all the records for cat 1 are displayed in the order of prod_cat_1_order first and then order by any order set in prod_cat_2_order afterwards.

So the output would look like this:

product 3
product 1
product 2
product 8
product 9
product 4

And then if sorting cat 2, the order would be:

product 7
product 4
product 6
product 1

And then if sorting cat 3, the order would be:

product 9
product 5
product 6
product 2
product 7

I'm not sure if what I want to do is an easy fix or something quite complicated but I'd be extremely grateful of any help or advice you could give me.

Many thanks in advance.

Improve this question

1 Answer 1

Reset to default
1

If one product can have more than one category, your database design needs to reflect it better (what if you needed a 3rd category, would you add two columns?).

The common way is to have a Junction table, which would contain foreign keys referencing both your Product and Category tables, having another column to store that product ranking you want to achieve.

Then filtering and sorting by Product should be straightforward, by joining your Product table and that junction one.

Would that be acceptable?

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Maybe I should have been clearer in my question. My database does contain two tables, one with product data (where categories are assigned by a number) and another category table that contains the cat id numbers and names that are reflected by them. I use an INNER JOIN to display the products by category, is what you've suggested any different?
My answer suggests the creation of a 3rd table, that represents the junction between the 2 you already have. Its purpose is to replace the prod_cat* fields of the Product table, to reflect the many-to-many relationship between Product and Category. That way, if one product has several categories, it is represented by several lines in that junction table. It avoids NULL values in the prod_cat_2 column when a product has only one category, and will help a lot when achieving what you intend with the category ranking per product. Give it a try :)
Thanks, I've had quick look at the link you provided and I see what you mean. Still not 100% sure how I'll go about implementing it. I'll see if I can find some more info relating to junction tables and go from there. Thanks again, it was the little bit of info I needed to get me going in the right direction.
Feel free to ask if you need more help. If you think you have enough information, please mark this answer as best so that the community knows this question has been resolved. Thank you :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.