Hello
I have this challenge:
a table called "images"
id | rel_id_owner | pic_name | pic_display_order | pic_info |
1 10 1 1 some info
2 10 2 3 some info
3 10 3 2 some info
4 10 4 5 some info
5 10 5 4 some info
6 10 6 6 some info
7 10 7 8 some info
8 10 8 7 some info
9 10 9 9 some info
This table holds information on every user picture and the order in wich they are arranged.
A user should be abe to change the order the pictures are shown so for example if they want to move a picture from the 8-th place to 3-rd place, the old picture that was on the 3-rd place will step down to 4-rd place and so on until it reaches 8-th place.
For this case i'm using 3 queries :
1.
UPDATE images SET pic_display_order=0 WHERE pic_display_order=8 AND rel_id_owner=10
2.
UPDATE images SET pic_display_order=pic_display_order+1 WHERE (pic_display_order BETWEEN 2 AND 8) AND rel_id_owner=10
3.
UPDATE images SET pic_display_order=3 WHERE pic_display_order=0 AND rel_id_locatie=10
Is this a dumb sollution? Is there a better way to do it?
Thanks.
