PHP and MySQL: Dynamic position/order update

Question

I am building some sort of a CMS in PHP and i ran into an issue, hoping you guys can help me out with it.

I'll post an example of what my database looks like and what i need to be done.

ID | Name | Order
 1 | A1   | 1
 2 | A2   | 3
 3 | A3   | 4
 4 | A4   | 2

What i need is a way to edit the 'order' column of DB dynamically, as in if i was to change the order of 'A2' to '2', it will automatically change the order of 'A4' which was originally '2' and update it to whatever order 'A2' was, which is '3'

If this wasn't clear enough, i would be more than happy to screen shot my database to clarify it more.

Thanks in advance

I think the way this is usually achieved is you submit the order of every new element back to the server and update them all at once. So, when the sorting is changed, the new position value and id of every impacted element is submitted back to the server. — menacingly
– menacingly, Commented Feb 27, 2012 at 16:17
Well Flukey, i checked that page you linked, and yes this guy seems to have the same issue that i have. The problem is i did not understand half of the stuff in there. I got a couple ideas of how to do it, but that's about it. Thanks for your help tho, much appreciated. — Khaled
– Khaled, Commented Feb 27, 2012 at 17:06

webbiedave · Accepted Answer · 2012-02-27 16:43:31Z

1

change the order of 'A2' to '2', it will automatically change the order of 'A4' which was originally '2' and update it to whatever order 'A2' was, which is '3'

UPDATE Orders SET `Order` = (SELECT `Order` FROM Orders WHERE id = 2) WHERE `Order` = 4;
UPDATE Orders SET `Order` = 3 WHERE id = 2;

answered Feb 27, 2012 at 16:43

webbiedave

49k8 gold badges92 silver badges104 bronze badges

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

2 Comments

Khaled Over a year ago

See, this would be fine, if i was dealing with known numbers, which am not, because i have more than 20 rows in that table already, and i want the whole (2, 3, 4 ..etc) to be dynamic using variables or something.

webbiedave Over a year ago

Right. You can simply substitute your variables in for the literals in the above SQL.

Collectives™ on Stack Overflow

PHP and MySQL: Dynamic position/order update

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related