MYSQL Query to Drop down list

[homer09001]

ok i want to put the results from a query into a drop down list:

my code so far:

<select name="aircraft">
<?php
include "config.php3";
include "dbconnect.php3";


$q = "SELECT Reg AS reg, Type AS type FROM $table4 GROUP BY Type";
$result = mysql_query($q)or die(mysql_error());

while ($row = mysql_fetch_assoc($result)) {
    $reg = $row["reg"];
    $type = $row["type"];

$options .= "<optgroup label=\"$type\"><option value=\"$reg\">$reg</option>";

echo $options;
}

?>
</optgroup>
</select>

there are 6 rows in the database, its pulling all 6 and sticking the type for each aircraft seperatly with the reg of the first row of that aircraft

where as i want it to put all reg's from the type or aircraft in each aircraft group
i cant work out how to do that though?

[NogDog]

If I understand the problem correctly, I think this might give you what you're looking for:

$sql = "SELECT Type AS type, GROUP_CONCAT(DISTINCT Reg ORDER BY Reg DESC SEPARATOR '|') 
AS reg FROM $table4 GROUP BY Type";
$result = mysql_query($q)or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
   echo "<optgroup label='{$row['type']}'>\n";
   $regs = explode('|', $row['reg']);
   foreach($regs as $reg) {
      echo "<option value='$reg'>$reg</option>\n";
   }
   echo "</optgroup>\n";
}

[homer09001]

all i get from your code is a blank drop down box?

[NogDog]

If you copy-and-pasted my code, I named my query string $sql, but copy-and-pasted your code with mysql_query($q); so make sure you change one or the other so they are the same in both places.

[homer09001]

ohh yeah derrrrr

ive got an excuse its very early in the mornin and ive just woke up lol

[homer09001]

ahhh your a lifesaver it works perfect just how i want it.

Thanks soo much

[NogDog]

Glad that was the only problem. (Had me worried for awhile until I saw what I'd done.)