0

I have a MySQL database full of products, Each product has a category. I want the owner of the store to be able to change the order in which the products are displayed on the site as he likes to tinker with order a lot.

My initial thought is to have a product order column but there is lots of issues with this as you'd need each category of products to have a separate ordering and all the products are in the one table i.e. Would need to have a Chair in Position 1 and also a Table in position 1, this is definetely achievable however the bigger issue lies with If i want to change the positioning of chair2 to position 1 then i'd have to update every other products column too. This is computationally expensive having to run potentially 100s of queries every time I change the order.

There must be an easy way to do this. I'm using MySQL and PHP. Any help is greatly appreciated.

Thanks.

Ben
  • 342
  • 5
  • 13
  • It looks you have a Many-to-Many relation between the categories & products. why you don't put the order in the intermediate table – Shady Atef Aug 29 '16 at 13:09
  • That makes sense, but it still doesn't stop this issue of having to update each products order in category x when you change order does it? @ShadyAtef – Ben Aug 29 '16 at 13:11
  • No Badly it doesn't. – Shady Atef Aug 29 '16 at 13:16
  • Similar question asked in SQL SERVER. http://stackoverflow.com/questions/8607998/using-a-sort-order-column-in-a-database-table/8608085#8608085 – xQbert Aug 29 '16 at 13:28

1 Answers1

1

You could make a priority system, where the owner can grant items a +1 or a -1 (or +10, +.1 etc.) in order to have them bubble up or down the list. This is not perfect, as it makes exact positioning of items harder (especially further down the list) but it will decrease the amount of computation needed. (You could even let the owner directly view and edit the priority score of products to improve exact positioning.)

Iain
  • 387
  • 2
  • 12
  • It seem like there is no computationally cheap or easy implementation way of doing this , I just found a similar question asked on here and no real answer was found. Thanks for your answer. – Ben Aug 29 '16 at 13:24
  • I think you will either have to do absolute identifiers (which are expensive but precise), or relative identifiers (which are cheap but fuzzy). If you do ever find a real answer keep me posted. – Iain Aug 29 '16 at 13:28
  • Will do Iain, Thanks! – Ben Aug 29 '16 at 13:32