What's the best way to manipulate the way entries in a data base are displayed on my web page?

Question

I have a client I am building a small CMS for. One of the functions is that he would like to be able to pick the order in which images display on a page.

In my mySql Database I have a table called image_info with the fields:
index (auto incremented)
img_url
img_link

I was thinking of adding an additional field called img_order which would be an int that I would refer to to sort the data returned to my php script.

I'm not sure if that is the best way. I feel like I will have to be re numbering every entry in the database just to move one image up or down the list.

Is there a standard procedure for this type of function?

Thanks, -J

Answer 1

I think that adding an img_order column is the easiest way, and it won't be hard be to renumber the fields. For instance, if you're adding a picture at img_order position 15, just insert it with that value, then run the following query:

update image_info set img_order = img_order + 1 where img_order >= 15;

If you're removing an existing image, you'd have to decrease the img_order values for all images after that one:

update image_info set img_order = img_order - 1 where img_order >= 15;

And if you're moving an image from one position to another, you'd have to increment the img_order for all images between the original and new position:

update image_info set img_order = img_order + 1 where 
img_order between (least($old_img_order, $new_img_order), 
greatest($old_img_order, $new_img_order));

Answer 2

You will have to re-number on every position change. But the algorithm isnt too complex you can do it all in two queries.