[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