MySql result from array in a specific order.

[jecasc]

I have an array with product ids. The product ids are already sorted by relevance.

Now I want to get the products name, description, image and all the other information from the products database.

What I did was create a sql query with a that looks something like this:

SELECT name, description, image_url FROM products WHERE products_id = 00004 OR products_id = 00233 OR products_id 00002

However the result is in no particular order. The question is: How do I get those results in the exact order of my array? Or do I have to make a seperate query for all products_id:

SELECT name, description, image_url FROM products WHERE products_id = 00004

SELECT name, description, image_url FROM products WHERE products_id = 00233

SELECT name, description, image_url FROM products WHERE products_id = 00002

[mark_roach]

A quick and dirty solution although it is not neccessarily very efficient is to union the 3 seperate queries like so

SELECT name, description, image_url, '1' as sortorder FROM products WHERE products_id = 00004
UNION
SELECT name, description, image_url, '2' as sortorder FROM products WHERE products_id = 00233
UNION
SELECT name, description, image_url, '3' as sortorder FROM products WHERE products_id = 00002
order by sortorder

I am assuming you have an index on products_id so the individual queries will be fairly quick. If you have more than handful of possible "products_id"s I would look for a more elegant solution.

I don't know PHP but could you not include products_id in your original query and then sort the returned array on that field ?

If that is not possible you could populate a temporary table with the products_id and the sort order and then join the two tables in a single SQL query.

[jecasc]

The orginal array of products_ids comes from an external search index I am using. Basically it works like this:

The user enters search keywords, the keywords are transmitted to an external search engine that returns an array of products_ids sorted by relevancy. I am using this external search solution because it provides features like fuzzy search which the original search engine that comes with the shopping cart does not.

What I have to do is then populate the search result with pictures, description, price and so on. However the original sort order must be maintained or the purpose is defeated. I'll try with UNION like you suggested and see how it performs.

[coopster]

You can use an ORDER BY FIELD():

$ids = implode(',', $ids); 
... and in your query ORDER BY ... 
"ORDER BY FIELD(productID, {$ids})" 

[jecasc]

You can use an ORDER BY FIELD():

That seems to be exactly what I was looking for. I didn't even know that existed.