Dependent Drop-Down Lists

Dreamweaver 8 / PHP / MySQL

View Example View Example...

In the first tutorial we reviewed how to filter a data table (country profiles) using one category drawn from the table (continents). Then we looked at how to filter the list by two independent categories.

Now we'll investigate adding two drop-downs that are dependent on one another (one is a subset of the other), for example continents and regions. If I choose the continent "Europe" from the Continents drop-down list, the Regions drop-down list should display only the regions that are in Europe. The second list is dependent on the first.

  1. Begin with a PHP page set up to filter on one category (instructions here).
  2. Create a recordset for the second category:
    • Create a new recordset (Server Behaviors tab). Name it regions, and base it on the world database connection. From the Countries table, choose the field named Region.
    • Click the Advanced button to view SQL editing options. Insert the word DISTINCT after the word SELECT, so that the SQL statement reads: SELECT DISTINCT Region FROM Country. This will collapse the list to show only unique values in the Regions field. You can also add an "order by" clause to sort the list.
      Insert data table
  3. Create the second drop-down list:
    • Add a second drop-down list under the first, and call it selRegion.
    • In the Properties panel, click Dynamic. Choose the new regions recordset. Leave the defaults for Values and Labels (Region field).
    • Click the + button and add a new static option, with a value of % and label of "All Regions " (this creates the wildcard option).
      Insert data table
  4. To make the second drop-down list auto-submit when the user makes a choice, add onchange="formSearch.submit()" to the end of the <select> tag of the selRegion drop-down.
  5. Edit the countries recordset to reflect the new category (on the Server Behaviors panel; double-click Recordset (countries)).
    • Add a new variable named varRegion with a default value of % (to allow for an 'All Regions' choice) and a run-time value of $_POST['selRegion'] (to match the selection in the drop-down named selRegion).
    • Edit the WHERE clause in the SQL statement to read:
      WHERE Continent LIKE 'varContinent' AND Region LIKE 'varRegion'.
    • The dialog will look like this:
      new variable
  6. At this point you can publish and test the page to make sure both drop-downs are correctly filtering the list (though they are not yet dependent).
  7. Next, we need to make the Regions list dependent on the Continents list. Edit the regions recordset:
    • Click the Advanced tab if necessary.
    • Add a new variable called varContList (not varContinents, because we've already defined a variable with that name). Set the default value to % (to allow for all values) and the run-time value to $_POST['selContinent'].
    • Add the following WHERE clause to the SQL statement:
      WHERE Continent LIKE 'varContList'
      new variable
  8. Finally, to make the drop-down list show the user's choice rather than defaulting to the first item on the list:
    • Immediately before the closing > of the <option> tag, add this:
      <?php if ($varRegion_countries == $row_regions['Region']) {echo 'selected';}?>
    • "varGovt_countries" is the name of the variable in the countries recordset, plus an underscore and the name of the recordset.
    • The complete <option> tag will read as follows:
      • <option value="<?php echo $row_regions['Region']?>"
        <?php if ($varRegion_countries == $row_regions['Region']) {echo 'selected';}?>
        >
        <?php echo $row_regions['Region']?></option>
    • Note: if you regenerate the recordset at any time, this modification to the <option> tag will be overwritten. Store the code in a text file somewhere to save yourself the headache of having to do it again.
  9. Publish and test.

Related articles:

 

<- back to index | ©2007 clg.