Filter a Data Table on Two Categories

Dreamweaver 8 / PHP / MySQL

View Example View Example...

In the previous tutorial we reviewed how to filter a data table (country profiles) using one category drawn from the table (continents).

Now we'll look at how to filter the list by two categories: continents and form of government.

  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 government, and base it on the world database connection. From the Countries table, choose the field named GovernmentForm.
    • 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 GovernmentForm FROM Country. This will collapse the list to show only unique values in the GovernmentForm 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 selGovt.
    • In the Properties panel, click Dynamic. Choose the new government recordset. Leave the defaults for Values and Labels (GovernmentForm field).
    • Click the + button and add a new static option, with a value of % and label of "All Governments" (this creates the wildcard option).
  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 selGovt drop-down.
  5. Edit the countries recordset to include the new category: Server Behaviors panel; double-click Recordset (countries).
    • Add a new variable named varGovt with a default value of % (to allow for an 'All Governments' choice) and a run-time value of $_POST['selGovt'] (to match the selection in the drop-down named selGovt).
    • Edit the WHERE clause in the SQL statement to read:
      WHERE Continent LIKE 'varContinent' AND GovernmentForm LIKE 'varGovt'.
    • The dialog will look like this:
      new variable
  6. 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 ($varGovt_countries == $row_government['GovernmentForm']) {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_government['GovernmentForm']?>"
        <?php if ($varGovt_countries == $row_government['GovernmentForm']) {echo 'selected';}?>
        >
        <?php echo $row_government['GovernmentForm']?></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.
  7. Publish and test.

Related articles:

 

<- back to index | ©2007 clg.