I've got a script which allows the user to insert a new item into a list of items and specify where in the list it's placed. There's a row in the database table for each item and one of the fields in each row is the items position in the list. So, when a user inserts an item, all of the items that will fall below it have to have the position field incremented by one and then the new item is inserted.
I've been having problems where on very rare occassions 2 items will end up with the same position number (perhaps the script is interrupted before it is completed?), which causes problems. Is there a better way to do this that will guarantee no duplicate position entries?
I tried making the position a unique index, so that 2 items couldn't have the same position value, but then the query that increments the position produced an error because it was trying to create a duplicate entry.
update $table set position=position+1 where position > $x
I guess MySQL doesn't know enough to increment the row with the highest position first and work its way down and produces an error when it tries to increment an item when the item just above it hasn't been incremented yet.
