How do I store image sequence changes to a mysql database with php

I am trying to build a photo organization table and am looking for advice and/or examples of how to save the display sequence of photos to a database. I'm using php and mysql

Example: If I have 10 photos in an album and decide to change the sequence of images, so I move a photo from seq 2 to seq 7, then the sequence changes to the following.

1, 2, 3, 4, 5, 6, 7, 8, 9, 10 Original Sequence

1, 7, 2, 3, 4, 5, 6, 8, 9, 10 New Sequence

How do I tell the database that the sequence column for all the affected rows has changed? ...in one op开发者_StackOverfloweration.

Do I change multiple rows in the db at once? Can I limit to just the affected rows

the photos table currently has "rowID, sequence, URL...."

UPDATE table SET display_order = CASE WHEN id=first_id THEN second_order ELSE first_order END WHERE id = first_id OR id = second_id LIMIT 2

where you have first_id, first_order, second_id, second_order, this switches their display orders.

You could also just write your table to have next_image_id and previous_image_id, that way all you have to do to to insert an image is change the previous images next_image_id and the next images previous_id, along with that same image.

Consider having the same Sequence in the database and rather than changing sequences inside database overwriting it all the time, use a php random generator like so:

$db=new SQLite3("sqlite.db") or die ("Your Internet Connection Dropped!");
$select_table = $db->query("SELECT * FROM images"); 
while($image=$select_table->fetchArray(SQLITE3_ASSOC)){
  echo "<img src='path/to/file/".$image[rand(1,11)].".png'>";
}

My Code is bad in security, but It should do the job, Hope it helps!