How to order the images in a mysql database?

Question

Hy!

Here is my problem: i have a profile and this profile has for example 100 images.

When someone visit this profile the first image will be the first result of my query that sorts by updated_at.

But if the user would want to put the image with the (id = 8) to be the first, then (id = 22) then (id = 88) and so on?

I need to put another field or is really necessary a new table to make a custom sort on images? and maybe someone explain this to me how to do?

thanks, i appreciate your help ;)

EDIT: (i appreciate if could be made with a really small amount of rows affected) for example if i have 100 images, and i swap the 4th with the 89th will be affected only 2 rows

if i have to delete the 1st image, 99 images should be updated? thats really a bad situation. Or the field could begin with number 2?(the first image had the order_field = 1)

EDIT2: if i have 100 images, with the order_field from 1 to 100, and then the user want to add a new image and to be first, then all them should be changed, right? this is something i dont want...

Answer 1

Create other column/field in the same table called position or order.

In this case you'll store the image position in this column. When you retrieve the images for the user profile, you'll know beforehand in which position to show the user's images.

This of course requires that the user specify the image relevance (position). This column in the DB table can even be null. If it's null you can show this image in whatever position. The images that have this field filled should appear in the specified order.

Just and idea...

Answer 2

If you want the user to be able to control the sort order for the images, then yes, you'll need some kind of criteria that the user can affect in order to achieve that.

It sounds like you have a column updated_at which is probably a timestamp. I'm guessing this represents some kind of update operation to the image, and probably won't work as a sort field. Without knowing more about your application, I could imagine a situation whereby a user swaps two images, and both get updated with the same timestamp, in which case the sort is ruined.

The best solution is probably to have a column that indicates the user's preferred sorting order, and this column accepts updates from the user's use of the app.

Answer 3

I've had a similar scenario for new products on a website. Usually, you'll want new product to appear first in the list. But sometimes you want to reorganize. Here is the solution that worked really well for me (applied to your scenario): id, img, sort_position, timestamp

1. Display images where sort_position is null and order by date
2. Display images where sort_position is not null and order by sort_position

At first, images will just display in the order from newest to oldest. Without maintenance, this is ideal. Once you assign sort_position (hopefully done automatically by serializing the images and their positions with a scripting language), the position you want for all items will be set. The first rule no longer applies, but the sort order you set persists. But because the first rule is in place, you can now continue to add images without needing to maintain the sort positions. New images will show up top/first, and then your organized grouping will show below. Then if you want to sort, the script just re-serializes the positions. It maybe a little overkill for what you want, but it certainly helped me cut down maintenance on products in categories with over 100 items in it.

If you have more specifics on your environment, I can provide more specifics on this type of solution.