mysql ORDER BY

possible to define using an external array

[jamie]

hi,

i am writing a simple search using the levenshtein() function. this is returning an array of nearly-matching IDs which i am using to search the database.

is there any way i can order my select statement in the same way which the levenshtein array is ordered?

at the moment my (simplified) sql is:

SELECT ID, title 
FROM table 
WHERE ID IN($my_imploded_levenshtein_array)

however, this is ordered normally by ID, instead of the same order the levenshtein array is in.

i had thought of creating a temporary table for all the data, but as i am selecting data from 3 joined tables, i think this would be a bit cumbersome.

cheers

[Salsa]

Would it work to:

ORDER BY $levenshtein_array_imploded_on_commas

?

[Salsa]

No, of course that won't work. I must have been thinking of something else... or not at all! It's a bit inelegant, but I suppose you could sort them out in a loop after the query, doing a foreach on the levanshtein array...or put the results in subarrays of the levenshtein while running a while loop after the query--but that's just off the top of my head in this moment of remorse. Sorry for the poor answer. Hopefully someone will bave a better one.

[jamie]

hi salsa,

not to worry, i too have been going round in loops this morning, thinking about how to sort them using php - but as you say, all a bit inelegant ;-)

i then thought to create a temp table holding the correct order and join it to the other tables and use it to ORDER BY, but i am unable to join a temporary table to a real table.

hmmmm...

thanks for input anyway

[jamie]

yes i can join to temporary tables!

i was doing an exit($sql) after the query and manually running the query in phpMyAdmin - of course because the script had finished running, the temp table had already been dropped... doh!

;-)

[coopster]

Would something along these lines work for you? Maybe you won't need the temporary table.

How to make MySQL return an pre-ordered list [webmasterworld.com]

[jamie]

coopster that's perfect!

many thanks

(i knew there had to be a more elegant way ;-)

[killroy]

I think you should do some performance testing to a temp table with your ids and a join on that.

SN

[jamie]

hi killroy,

for the page as a whole, using coopster's ORDER BY method is on average 0.004 seconds quicker.

i haven't however, broken the page into individual queries and tested those.

i suspect the time difference on the page as a whole is due to the INSERT INTO TempTable operations (there are 8). this would mean that the join on the temp table is indeed quicker than the multiple ORDER BY's. i'll remember that thanks.

[killroy]

Also remember you can insert multiple rows with a single insert statement. furthermore I suspect the speed difference of a join and a long order by clause depends on how many rows and how many order by ids are involved. Just consider this if you ever run into scalability issues once the tables grow.

SN

[coopster]

Yep, if you can get to a point where UNION is an option, you'll see better results yet.

[jamie]

thanks both,

>> insert multiple rows with a single insert statement

that's very useful!

coopster, i'll leave UNION for next week, having already learned about temp tables and multiple inserts this week ;-)

[tata668]

I had the same question.

Thanks to all of you!