Updating table with only one columns data that changes

Question

I have a table in mysql which I am trying to change the position of a row

|Id    |Content      |Position
------------------------------
|101   |"Yellow"     |1
|201   |"Red"        |2
|301   |"Blue"       |3 
|401   |"Black"      |4

To be this

|Id    |Content      |Position
------------------------------
|101   |"Yellow"     |2
|201   |"Red"        |3
|301   |"Blue"       |1
|401   |"Black"      |4

I update this table using PHP the only data I have is the changed position e.g If I want to change the position of Yellow to 3 the only data I have is 3 so I want to be able to reorder everything under 3 to be 1,2 and everything over 3 to be 4,5.. The only data I have is to do this change is 3. how would I do this? Should it be done using PHP script or a mysql query? Which would be easier to implement?

I have tried

"UPDATE table SET position = 3 WHERE position = 2"
"UPDATE table SET position = 2 WHERE position = 3"

This will not use an ORDER BY in the statment as the positions basically swap. Prehaps I must create a tmp variable in mysql to store the old position?

I updated my api end point so I basically get the order from JavaScript and used the sql https://stackoverflow.com/a/15635201/4960941.

and it works.

I was using only position as a means to keep the ajax request as small as possible but this works for now. I'll try optimizing later when I have time.

do you often change positions? if so you can do the update query on your PHP file. — alil rodriguez, Jun 09 '17 at 10:54
Yes the positions constantly change. The only data I can access is the position that has changed via PHP. What I am having issues with the PHP is that there will be to rows with the same position and I need to move one row over or under the other row. — Matthew du Plessis, Jun 09 '17 at 11:00
if you change Yellow to 3 then what about current element i mean "red"? will it swapped or do anything else? — Ahmed Ginani, Jun 09 '17 at 11:06
Yes element Red position will change to Yellows position before it was changed. — Matthew du Plessis, Jun 09 '17 at 11:10

score 0 · Accepted Answer · answered Jun 09 '17 at 11:32

Maybe I get the question wrong, but why don't you simply do this:

update your_table set Position = 0 where Position = 3;
update your_table set Position = Position + 1 where Position < 3;

There is initially no record with Position <= 0, right? Then this gets your desired result.

Updating table with only one columns data that changes

1 Answers1