order mysql query via input order in an IN() clause

Forum Moderators: open

Message Too Old, No Replies

pseudu example provided

5:47 am on Mar 27, 2008 (gmt 0)

Hello Webmasters,

for a Select * from XX where YY in (123,2,331,224) statement, is it possible to order the result in this format?

col1: result of id# 123
col2: result of id# 2
col3: result of id# 331
col4: result of id# 224

If not, then I myself can only think of 2 server-intensive solutions of:

1) Calling the statement indivudually (4 calls to database) and assign result into an $array[];

2) Using 4 select statements with UNION

select ... where id=123
UNION
select ... where id=2
UNION
select ... where id=331
UNION
select ... where id=224

Which is quite chunky and not sure if there are any other better methods, for a MySQL 4.013 Ver. server?

Thanks in advance for the enlightenment.
Urban

4:57 pm on Mar 27, 2008 (gmt 0)

UNION should not be server-intensive. Fast too. Here is a relative thread that you may find interesting.

If you don't like the UNION you could use an ORDER BY POSITION

2:24 am on Mar 28, 2008 (gmt 0)

Thank you very much coopster. That thread will solve other related, old problems I've always wanted to tackle.

For others that might be interested, in PHP I exploded (123,2,331,224) into an array, then

foreach (x in $y)
$carrier[$y] = array();

then during the mysql_fetch_assoc operations in the mysql_query call, I did

while (not end of loop as $rows)
$carrier[$rows['id']] = $rows;

to have arranged the data towards the order by that I wanted.

Thank you again for the thread, I'm going to test if union all will have a performance issue, when doing that towards hundereds of UNIONs.

Urban