How do I sort into a dynamically generated specific order in mySQL?

Question

I've been beating my brains in over this and I'm making no progress. I'm not very good with SQL so I'm hoping the answer is something simple that I just don't know.

I have 2 tables: One called "images" that contains the data I want, organized with a primary key "imageID". The other is called "productPhotos", and it contains two fields (plus a primary key): imageID and productID. So for a given product there might be several images, and a given image might be used to represent several products.

So far it's working great, but here comes the problem. I have a dynamically generated list of product IDs, and I want to load all of the images that represent those products. And not only do I want to load them, but I want them in the order of the products in my list.

So if my list of products is "5,2,4", I want my output list to consist of first all the images for product 5, then all the images for product 2, then all the images for product 4. I don't particularly care about the order within the subgroups.

Fetching the right images is pretty simple, but getting them in the right order is abominable. I figured out how to get the IDs of the images I want in the right order, but actually fetching those images without a separate MySQL call has thus far eluded me. I've tried setting up a temporary table, storing the IDs I want in a @variable, and lots of different variations on ORDER BY FIELD() and ORDER BY FIND_IN_SET().

Here's what I've got so far: SELECT imageID FROM productPhotos WHERE productID IN (5,2,4) ORDER BY FIELD(specificProductUID, 5,2,4);

That successfully returns the set of imageIDs in the right order, but I've been stymied in getting from there to pulling the data for those imageIDs from the images table. I know that I could do it pretty easily by just using two separate database calls in PHP (one to get the imageIDs and another to load the images) but (I think) that would mean a big hit performance-wise and it just seems like there should be a better way to do it.

Thanks in advance for any help.

Answer 1

SELECT images.*
FROM images
INNER JOIN productPhotos ON images.imageID = productPhotos.imageID
WHERE productID in (5, 2, 4)
ORDER BY FIELD(specificProductUID, 5,2,4);