The results have to be ordered in the order the user selected the items (using a web interface). I don't think there's a way to do that directly with an SQL ORDER BY clause.

I thought that too, and then I discovered this:

SELECT * FROM table ORDER BY FIELD( foo, 4, 2, 5, 1, 3 );
[download]

I'm not sure which versions of MySQL it works in, but definitely in 5.

Note there's a bug where you can't have a space between FIELD and the bracket.