Order By a list?

I am making a gallery script and need to retrieve image info.

Can you make an sql statement similar to:

SELECT * FROM photos WHERE collection='something' ORDER BY ID (4,6,2,7,3,5)

Here is my database structure:

photos - ID, name, description, collection (that the photo belongs to) collections table - ID, name, order (the order of the images in it as a string with some delimiter between each image ID)

For the page where the user edits the collection I need to get the order of the images in the collection (which I do by selecting the specific collection from collections) and then get all the info for the images according to their ID. As I mentioned above, what I want to do is select the images by collection and then order them by the array which PHP has created from the order string. So if the array is {4,6,2,7,3,5}, then I need the photos selected to be order by ID in that order.

Help appreciated.

I was thinking some sort of view?

Unfortunately, I can't think of any way to do this with pure SQL. Someone else here might know. An alternate solution would be to use the SQL to pull all of the rows you need, and then create a PHP function called sortto(mysqlarray, sortby); that takes the mysqlarray and maps each ID into a new array in the order designated by the array sortby. While it's not a pure SQL solution, it would easily do the trick with just a few lines.

I am thinking of doing that right now. Thanks. I was also sort of thinking if there is a better way to store the order. I was thinking of having each photo in the photo table have the order in a column, but updating the order would require having a query for each photo, and if the photos go into the hundreds, the program becomes not scalable.

You'll have to create a third table that links the image ID and the place it has in the list. How are you storing your pictures btw? base64?

But even if I make that third table, it would need to update all the photos in a specific collection once the order is changed? Wouldn't that require hundreds of queries if there are hundreds of photos?

Also, I store the photos as files, if that's what you're asking. Should I be doing it otherwise?

ranma wrote: But even if I make that third table, it would need to update all the photos in a specific collection once the order is changed? Wouldn't that require hundreds of queries if there are hundreds of photos?

What?

For example, you have this table

ID | Name | Order

1 | cat | 3 2 | tree | 2 3 | everest | 1

To update the order, you would need a separate query for each item to change its order to the new value.

EDIT:

I found this, which might work, but makes for long queries:

$array = array(3,5,4,2,1);
$list = implode(',', $array);
$orderbylist = 'ORDER BY '; // initialize
foreach ($array as $id) {
$orderbylist .= "id=$id DESC, ";
}
$orderbylist = rtrim($orderbylist, ', ');
$sql = "SELECT * FROM table WHERE id IN ($list) $orderbylist";

EDIT 2:

Found a solution: I can use mysql_data_seek() to go to the correct ID.

But still, I'd like to ask why keep the image in a DB and not in the file system.

ranma wrote: EDIT 2:

Found a solution: I can use mysql_data_seek() to go to the correct ID.

But still, I'd like to ask why keep the image in a DB and not in the file system.

Unless Spyware is aware of something regarding this that I am not, I don't necessarily think there is a benefit to storing in the DB. It's actually a huge performance hit on both the DB and the server instead, as compared to direct filesystem access. On top of requiring additional coding, it can get messy when/if you encounter scalability issues. I really think the best method is to simply store file paths in the DB instead of the actual images.

It's almost never a good idea to store the images in the database as base64, I was just asking.

Whew, ok, then, thanks :)