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

Question

I beat my brains in this, and I did not make progress. I am not very good at SQL, so I hope the answer is simple, which I just don’t know.

I have 2 tables: one called “images”, which contain the data I need, organized using the primary key “imageID”. The other is called "productPhotos" and it contains two fields (plus primary key): imageID and productID. Thus, there can be several images for a given product, and this image can be used to represent several products.

While it works fine, but there is a problem. I have a dynamically generated list of product identifiers, and I want to download all the images that represent these products. And I not only want to download them, but also want them to be in the order of the products on my list.

So, if my product list is “5,2,4”, I want all the images for product 5 to be listed on my result list, then all images for product 2, then all images for product 4. I don’t really care about the order in subgroups.

Getting the right images is pretty easy, but getting them in the right order is disgusting. I figured out how to get the ids of the images I want in the correct order, but actually getting these images without a separate MySQL call still eluded me. I tried to create a temporary table, keeping the identifiers I want in @variable, and a lot of different options for ORDER BY FIELD () and ORDER BY FIND_IN_SET ().

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

, , . , , PHP ( , - ), ( ) , .

.

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);