[thelist] Reordering result set (PHP/MySQL)
Joshua Olson
joshua at waetech.com
Mon Feb 4 06:37:17 CST 2008
> -----Original Message-----
> From: Robert Douglas
> Sent: Monday, February 04, 2008 4:30 AM
>
> "select * from tracks where id in (3,7,2,1);"
>
> This returns a resultset in numeric order (1,2,3,7) but I want them in
> the original order. Should I be trying to do this in SQL? Or
> reordering the resultset in PHP?
>
> The SQL side, I have no idea about. If PHP is the best/only way, it
> feels a little cumbersome (and slow?). Any recommendations on
> approach?
Rob,
You are in for a rough ride with this little problem.
1. You could do this PHP--this would probably be the better way to go unless
you've got a big result set. If the result set is large, I suggest using
technique #2.
2. You could do this in SQL--one approach: create a temp table with an
identity column and a FK column; iterate through the list inserting into the
temporary table; join against the temporary table (to create the ordered
result set); drop the temporary table. Note: If I was using SQL Server, I
would do this in a stored procedure. On MySQL--not so sure...
Joshua
<><><><><><><><><><>
Joshua L. Olson
WAE Technologies, Inc.
http://www.waetech.com/
Phone: 706.210.0168
Fax: 707.988.0168
Private Enterprise Number: 28752
http://www.waetech.com/design/portfolio/
More information about the thelist
mailing list