Sorting a Query by a Given List

[dwdata]

I have a query that retrieves records from a table:

<?php
$query = "select * FROM Ev_AvVolunteers  where id_Event='$id_Event' ORDER BY Name";
$results = mysql_query($query) or die("Error performing query"); 
?>

It is sorting by Name now BUT I want a different sort. There is another field called "Instrument" which contains what instrument they will be playing. I know I can change the "ORDER BY Name" to "ORDER BY Instruments and ORDER BY Name" which will group the values together BUT I still need a different sort order: The order I need is by Priority. I need it like this:

Leader
Singer
Piano
Synth
A-Guitar
E-Guitar
Bass
Drums
Perc
Sax
Flute
Other

After it sorts by this list, then I want to Sort by Name. So it will produce an example list like this:

Smith, Bill - Leader
Hanster, Tom- Singer
Raller, Jen - Singer
Tombs, Sara - Singer
Joel, Billy - Piano
Taylor, James - A-Guitar
Carlton, Larry - E-Guitar
Flea - Bass
Peart, Neil - Drims

etc...

You get the idea ;-)

I appreciate if someone can show the code on how to get that work.

Thanks!

[cwarn23]

Try this:

<?php
$query = "select * FROM Ev_AvVolunteers  where id_Event='$id_Event' ORDER BY `Name`, `Instrument`";
$results = mysql_query($query) or die("Error performing query"); 
?>

or

<?php
$query = "select * FROM Ev_AvVolunteers  where id_Event='$id_Event' ORDER BY `Instrument`, `Name`";
$results = mysql_query($query) or die("Error performing query"); 
?>

[dwdata]

Try this:

<?php
$query = "select * FROM Ev_AvVolunteers  where id_Event='$id_Event' ORDER BY `Name`, `Instrument`";
$results = mysql_query($query) or die("Error performing query"); 
?>

or

<?php
$query = "select * FROM Ev_AvVolunteers  where id_Event='$id_Event' ORDER BY `Instrument`, `Name`";
$results = mysql_query($query) or die("Error performing query"); 
?>

Won't that ONLY get it in ASC order for each column. See the last part of my post. It has a order or Priority.

[cwarn23]

The order I need is by Priority. I need it like this:

Leader
Singer
Piano
Synth
A-Guitar
E-Guitar
...

Won't that ONLY get it in ASC order for each column. See the last part of my post. It has a order or Priority.

Now I get ya. I don't think that is possible to do in mysql alone. You would need to convert the results into a giant array then sort the array into the order as pointed out in the above quotes. Then it would be possible to loop through the array results in order.

Or if that requires too much memory, you could use WHERE Instrument="Leader" and loop through those results and do the same for each type. Thats how I would do it.

[dwdata]

Now I get ya. I don't think that is possible to do in mysql alone. You would need to convert the results into a giant array then sort the array into the order as pointed out in the above quotes. Then it would be possible to loop through the array results in order.

Or if that requires too much memory, you could use WHERE Instrument="Leader" and loop through those results and do the same for each type. Thats how I would do it.

I think what I will do is define an Instruments table with a SortID and every time they select an instrument I will set the SortID in the "Ev_SchVolunteers" table. Then I can sort by the SortID field.

Thanks!