I'm trying to order my result set, which has very few rows (about six maybe), and I know the names of the fields and I'd like to arrange the names so that way when I loop though the result set I get a specific, predefined, order.
If you don't know of a way to do exactly what I'm saying, then anything on ordering mysql result sets would be good.
Thanks for reading. All help is appreciated on this.
[EDIT] I think I found what I'm looking for, although I don't know quite how it works:
Code: Select all
... ORDER BY FIELDS(name, 'Joe', 'Bob', etc.)[EDIT] I see how this works now. Due to a little bit of testing it seems the FIELD function returns an index number of the listed arguments. It takes the first argument and matches it with the ones after it, when it finds a match it returns the index of that match. So for example:
Code: Select all
FIELD('foo', 'bar', 'foo')If FIELD() doesn't find a match it returns 0. Also, if the first parameter can be a column name
So I think I understand how this works now. Is there anything else I missing, or that I need to know?
[EDIT] Is there a better way to do this:
Code: Select all
SELECT category_id, name, type,
FIELD(type REGEXP '^', type REGEXP 'Community|Pets', type REGEXP 'For Sale', type REGEXP 'Housing|Services', type REGEXP 'Jobs') AS `GROUP`
FROM categories
ORDER BY `GROUP`, `type`, `name`