A non alphabetic order I choose myself ?

[MichaelHe]

I have a musician database with names, instruments and instrument group.

I am listing the names alphabethicly in each instrument group like this:

mysql_query("select * from PeopleWork GROUP BY InstrGroup ORDER BY InstrGroup");

after this I get the names an other info from another table

So it outputs

Keyboard:
  name 1 (organ)
  name 2 (rhodes)
  and so on..

Guitar:
  name 7 (acoustic)
  name 8 (electric)
  and so on..

Drums:
  name 13
  name 14
  and so on..

But how do I ORDER the group in an order I choose myself.
ex. if I want guitar first, keyboard second and drums third.
A non alphabetic order I choose myself ?

Michael

[devinemke]

you can either spool the results in an array and then echo the array in whatever order you wish (using the array keys) or if you want to do this at the table level then you will need to add an extra integer field to your table that indicates the order you want.

[MichaelHe]

I'm not an expert in php programming yet :o)
But I'm learning all the time, but right here I think I lost you :o))

Spool the result ? echo the array ?

[MichaelHe]

I have in another case where I needed one specific word listed last used this

ORDER BY case when Index = 'the word' then 1 else 0 end, Index

This makes the word 1 and all other words 0. So the 0 word will be listed first and then the 1 words "the word" listed below.

So I wondered if this can't be used with more than one word. I mean by giving the different instrumentgroups a number and make it order by that number, just like the example above.

I have tried in many ways now, but I can't get it to work.

Is it possible. ?

[devinemke]

here is a simple example. i'm just guessing here at your exact data structure:

$result = mysql_query('SELECT * FROM PeopleWork') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$array[$row['InstrGroup']][] = $row;}

echo 'here are the guitars:<br>';
foreach ($array['guitar'] as $value)
{
	print_r($value);
	echo '<br>';
}

echo '<hr>';

echo 'here are the keyboards:<br>';
foreach ($array['keyboard'] as $value)
{
	print_r($value);
	echo '<br>';
}

echo '<hr>';

echo 'here are the drums:<br>';
foreach ($array['drums'] as $value)
{
	print_r($value);
	echo '<br>';
}

[MichaelHe]

I have found a solution.

I just type

mysql_query("select * from PeopleWork GROUP BY InstrGroup ORDER BY FIELD(InstrGroup,'Guitar','Keyboard','Drums' and so on...)");