Problems with manually incrementing a mysql field on Update

[thestonefox]

Hi All

 

I'm trying to manually increment a field in a mysql table to update field positions

 

the table looks like this to begin with:

 

image_id|title|position

14|test|0

15|another|1

11 |my second|2

 

now when i delete an image, i want to update the positions of any images with a higher position to decrease the position by 1.

 

So the sql would be

 

UPDATE Image SET position=postion-1 WHERE position>$current_item_position

 

So if the deleted item has the position of 0, then the statement would be

 

UPDATE Image SET position=postion-1 WHERE position>0

 

and the resulting table would look like this

 

image_id|title|position

14|test|0

15|another|0

11 |my second|1

 

 

However! something is going horribly wrong and its actually setting the table to this

 

image_id|title|position

14|test|0

15|another|0

11 |my second|0

 

 

It is because it is literally setting the field and then setting it again and again for every record it finds. So if i do this

 

UPDATE Image SET position=postion+1

i get this

 

image_id|title|position

14|test|2

15|another|3

11 |my second|4

 

and what is happening is this

 

image_id:14 has a position of 0 so + 1 and make it 1

image_id:14 has a position of 1 so + 1 and make it 2

 

image_id:15 has a position of 1 so + 1 and make it 2

image_id:15 has a position of 1 so + 1 and make it 3

 

image_id:16 has a position of 2 so + 1 and make it 3

image_id:16 has a position of 2 so + 1 and make it 4

 

so it does it all again!!

 

Does any one understand why it would be doing this?!

 

I'm using PDO in php to call the statement (if i run the statement directly in phpmyadmin, it works fine)

 

$database_link = new PDO('mysql:host='.$database_host.';dbname='.$database_name, $database_username, $database_password, array(PDO::ATTR_PERSISTENT => true));

 

echo $database_link->exec("UPDATE `Image` SET position=position+1");

 

I'm going out of my mind with this one

 

 

[Illusion]

UPDATE Image SET position=position-1 WHERE position=Any(Select unique position from image where position>$current_item_position)

Oops! It is the very hard of way of doing it.

[Illusion]

and also

 

Suppose that a table t contains a column id that has a unique index. The following statement could fail with a duplicate-key error, depending on the order in which rows are updated:

 

UPDATE t SET id = id + 1;

 

For example, if the table contains 1 and 2 in the id column and 1 is updated to 2 before 2 is updated to 3, an error occurs. To avoid this problem, add an ORDER BY clause to cause the rows with larger id values to be updated before those with smaller values:

 

UPDATE t SET id = id + 1 ORDER BY id DESC;

 

(The above text is from MySQL documentation)