PHP - Forms & Drop Down boxes help

[Andyt_uk]

Hi all,

Little while since i've done any PHP but am needing to create a couple of pages for a project.

Basically what I want to do is to have 2/3 drop down boxes on a page which are all populated from entries in 2/3 different tables.

When a value in the first drop down box is selected, it'll pull the relevant fields from the next table and put them into the next drop down box and so on.

Possibly a little easier to explain in context. Drop down 1 will select an Office, once selected the applicable buildings for that office are available in Drop down 2. From those options, once a selection is made, the applicable floors for that building are shown in drop down 3.

Hopefully someone can understand that and point me in the right direction as i've never really looked at nested drop-downs etc.

Cheers

Andy

 

[psyr33n]

What's your database structure?

 

[Andyt_uk]

at the moment its 4 different tables, sites / buildings / floors / ports

Sites - siteID, siteName
Buildings - buildingID, siteID, buildingName
Floors - floorID, buildingID, floorName
Ports - port ID, floorID ......lots more detailed fields

eventually all the relevant info in the ports field will be displayed once the selections have been made from the sites/buildings and floors drop-down boxes.

It may be that that db structure needs re-working, its been a good few years since i covered DB design

Cheers

 

[psyr33n]

Your db is normalised which is good.

You have 2 options here, imo:

1) Pull all the data from the db using joins, then use this data to populate the drop-downs when one drop-down is selected. This also opens up possibilities for caching etc. if you want to go down that route.
2) Pull each piece of relevant data when a certain drop-down is selected, e.g.

<select name="site">
<?php

// All untested, just here to illustrate methodology

$siteQuery = mysql_query('SELECT `siteID`, `siteName` FROM `Sites`');
while($sites = mysql_fetch_assoc($siteQuery))
	echo '<option value="', $sites['siteID'], '">', $sites['siteName'], '</option>';

?>
</select>

<?php

// Using $_POST values as an example, read the comment at the bottom for more info

if(isset($_POST['site']))
{
	$site = $_POST['site']; // Sanitise this!

	echo '<select name="building">';

	$buildingQuery = mysql_query('SELECT `buildingID`, `buildingName` FROM `Buildings` WHERE `siteID` = \'' . $site . '\'');
	while($buildings = mysql_fetch_assoc($buildingQuery))
		echo '<option value="', $buildings['buildingID'], '">', $buildings['buildingName'], '</option>';

	echo '</select>';
}

// Now do the same for the other drop-downs
// As there is more than 1 drop-down, you can't necessarily rely on $_POST values as only one can be held at a time. You may want to rely on $_GET values or temporary sessions

?>

Hope this helps, I'd personally go for the first method as it's less intensive.