Jump to content


Storing products in order within multiple categories

  • Please log in to reply
2 replies to this topic

#1 mallen

  • Members
  • PipPipPip
  • Advanced Member
  • 313 posts

Posted 12 October 2017 - 02:10 PM

I have a list of products that appear in multiple categories. And they also appear in a custom order using a "order" column.


My table is called "category_assoc" and stores the following.

cat_assoc_id,  prod_id, cat_id, p_order

I will try to type it out so you can see how its displayed. For example, one product in two categories and is at position 1 and position 8.


cat_assoc_id  |  prod_id | cat_id |  p_order  |

001                  123          4           1

002                  123          9           8

Is this the best way to record items that appear in multiple categories? I didn't want to post my PHP code yet since this is part of the database organizing.

#2 Barand

  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 12 October 2017 - 02:51 PM

I would need to know more about the custom "p_order" to give an answer, How is it applied - it's not possible to see with only two records?

Do products have a different custom order for each category, or are they listed in that order regardless of category?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 gizmola

  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 12 October 2017 - 08:21 PM

The table looks relationally sound.  It solves the problem of associating a product with a category and a specific order.  Seems like a lot of work to maintain that for lots of products, but if the number of products is small that's not a big deal.


If the ordering is exception based, or the case of "featured" product or "featured category product" then I'd have a slightly different structure, but if your use case is as simple as implied, then it looks good.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users