dynamic optgroup drop down menu using mysql & php

[dm404]

Hi

 

I'm a newbie and have only been learning for four weeks or so. I am practicing what i've read and so am creating my first web application

 

I'm designing a system where venues need to register, I have mostly completed the registration page but am struggling with one particular aspect.

 

The database consists of the tables:

 

venue(Where foreign key=AreaID)

area(AreaID, AreaName, AreaRegion)

 

When venues register they must select an AreaID. At the moment I have dynamically created a drop down box that is populated by querying the area table in mysql. It sorts the option by AreaName and the values correspond to the AreaID.

 

Whilst I was initially happy, I've realised that the sheer number of areas mean it will have to be sorted by AreaRegion. I think optgroup can be used to achieve this. The AreaRegion column is type enum('Scotland', 'Wales',....) with more regions to be added.

 

I've done a little research, but have ended up more confused than before! Any suggestions would be much appreciated. My code at the moment:

 

<p><b>Select Area:</b><select name="areaid">

<?php

$query1 = "SELECT AreaID, AreaName from area ORDER BY AreaName ASC";
$result1=@mysql_query($query1);

while ($row = @mysql_fetch_array($result1)) {
print '<option value="' . $row['AreaID'] . '">' . $row['AreaName'] . '</option>';
}
?>

 

</p>

</select>

 

 

Thanks in advance,

 

Daniel

[ignace]

SELECT * FROM area INNER JOIN venue ON area.AreaID = venue.AreaID ORDER BY AreaRegion DESC

[dm404]

Thanks for the quick reply.

 

I put 'SELECT * FROM area INNER JOIN venue ON area.AreaID = venue.AreaID ORDER BY AreaRegion DESC' but it didn't work.

 

From the examples i've seen on the net (which confused me greatly) I'm guessing my code is going to need a complete revamp, not just the query.

 

Once again thanks in advance, any suggestions are much appreciated.

[dm404]

Is this even possible? Or should i stick to what I already have?

 

Thanks again, any suggestions are much appreciated