0

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

Khaled
  • 154
  • 2
  • 8
  • Well, what have you tried so far? – Flukey Feb 27 '12 at 16:17
  • 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 Feb 27 '12 at 16:17
  • 2
    Duplicate: http://stackoverflow.com/questions/2453964/mysql-procedure-to-update-numeric-reference-in-previous-rows-when-one-is-updated – Flukey Feb 27 '12 at 16:18
  • 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 Feb 27 '12 at 17:06

1 Answers1

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;
webbiedave
  • 48,414
  • 8
  • 88
  • 101
  • 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. – Khaled Feb 27 '12 at 17:08
  • Right. You can simply substitute your variables in for the literals in the above SQL. – webbiedave Feb 27 '12 at 17:17