consolidation script

[TechBoyJK]

I'm writing a photo management tool to host photos for a trading site.

I don't need super granular functionality, just something to allow users to upload photos and get basic management features. I'm writing this in CFML and hosting on Bluedragon 7.0. They have a nifty tag called CF_image that I'm using to resize uploaded photos and make them more web friendly. That all works, but now I'm trying to make it so the photo
order consolidates after a user adds to or deletes a photo from a group. Right now users can upload up to 15 photos per item, so if they were to delete, for example, photo 12, the photo order needs to consolidate so that photo 13 becomes 12, photo 14 becomes 13, etc.

In the database, I have a column called photo_position so, for example, photo 13 would have an entry of "13". Pretty simple. Any time an action is done on a photo, I need a script to run afterwards that will make sure the photo_position count is in order and not missing any steps.

The way records are kept is that each photo has its own record, with a column for the item_id along with the photo_position number. The problem would be if I had for example, 3 records of photos for a particular item, but the position entries werent 1,2, and 3.. If they were maybe 1,2, and 5. This might happen when someone deletes photos 3,4.

If any body has any ideas on how to approach this script (logic, etc) it would be greatly appreciated..

 

[ppdes]

Why not just always fix them when you add or delete?

E.g., whenever you run the equivalent of:
DELETE FROM photos
WHERE item_id = :item_id
AND photo_position = :delete_photo_position

Also run:
UPDATE PHOTOS
SET photo_position = photo_position - 1
WHERE item_id = :item_id
AND photo_position > :delete_photo_position

Personally, I'd just record order rather than position. There are ways to do that where you would only ever have to update one row. Using a real number, or a numerator and denominator, would always allow an insert between two other photos without updating them, for example. It's easy to convert order to position on read, using an order by and the rownum pseudo column in Oracle, for example.

 

[TechBoyJK]

recording order seems like a better idea. Your logic in your script is good, but it still leaves me with a problem when there are multiple deletes.

I'm thinking I can just create a table with three columns, a primary key, foreign key, and photo_order. (probably a varchar 255)

In photo_order I can just keep a CSV based record using the individual photo_id that is created in the photo database.

It would like like

215,216,217,230,254,202

and I would just have to write a script to break that apart and assign a local position variable.. basically, the first is 1, second is 2, etc.

The nice thing about this, and thanks, is that there is no ceiling. (except for how much text per record I can keep, which is adjustable)