User-Friendly Data Filtering

Dreamweaver 8 / PHP / MySQL

Dreamweaver makes it relatively easy to extend the power of your website by integrating dynamic content from a database. If you're not intimately familiar with PHP and MySQL, however, making modifications to a basic data page can be rather daunting.

This page explains how to set up a database results page so that users can filter the results. It includes how to:

• Filter the list by category with one or more drop-down lists
• Make the drop-down show the user's choice after selection (rather than defaulting to the first item on the list)
• Execute the drop-down automatically, without having to click "Submit"
• Include an "All Categories" choice

View Example...

This mini-tutorial assumes that you have a basic understanding of how to create web pages with Dreamweaver, and that you have already set up PHP and MySQL on a server; created a database connection in Dreamweaver; and know how to create recordsets.

I am using the "World" sample database from MySQL. We will create a page that displays a list of countries, and then filter that list by continent and region. [View the sample database page]

1. First, get set up.
   • Set up your Dreamweaver site to use PHP/MySQL or another server technology. The process of installing PHP on the server can be unpleasant, but there are many resources out there on the net that can help you out (here are two reasonable places to start).
   • If you haven't already, set up the site with template files, navigation and other usability-conscious features. Hint: create an HTML template and save it with a .php extension, as in "layout.dwt.php". Use this template for all of your new PHP files.
   • In Dreamweaver's Site Definitions dialog, configure the testing server for your site, using the PHP/MySQL server model.
   • Create a database connection (mine is named world).
     
2. Create a new page with a PHP file extension (countries.php). To get started, we'll insert the dynamic data table (no filtering yet).
• Create a recordset for the main table. In the Application panel, click Server Behaviors, then click the + button. Name the recordset (countries), choose the connection you made earlier, and choose which table or query from which the main data table should be built.
  
• Insert a dynamic data table: Insert > Application Objects > Dynamic Data > Dynamic Table. Choose the recordset you created earlier. You can show all records or a certain number of records per page (you'll need to insert pagination code if you choose not to show all records at once).
  
  
• Save and publish the file, and test it on the server. If all is well, you should see a table on the web page that contains all of your data. In the case of the countries table, there are a lot of records, so creating a way to filter the list is an important next step.
3. We are going to filter the list by Continent. First we need to create a recordset that displays just the continents listed in the data table. We'll also set it up to remove duplicates.
   • Create a new recordset (Server Behaviors tab). Name it continents, and base it on the world database connection. From the Countries table, choose the field named Continents.
   • 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 Continent FROM Country. This will collapse the list to show only unique values in the Continents field.
     
4. Next, we need to create the drop-down list that will filter the main data table.
• Above the data table, insert a form. Name the form formSearch. Leave the defaults in the Form Properties panel: action is blank, and method is set to POST.
• Add a drop-down list. In the Properties panel, name it selContinent.
• In the Properties panel, click Dynamic. Choose the recordset continents, and specify Continent for both value and label:
  
5. Now we need to edit the countries recordset so that it is dependent on the drop-down list of continents.
• In the Server Behaviors tab, double-click the countries recordset (click Advanced if necessary).
• At the end of the SQL statement, add a WHERE clause:
  • WHERE Continent LIKE 'varContinent'
• Change the default variable name "colname" to varContinent, with a default value of -1 and a run-time value of $_POST['selContinent']:
  
• For testing, add a Submit button to the form (we'll remove it later with an auto-submit function). Publish and test.
6. The filtering works great! However, there are three usability issues we need to correct:
• First, the submit button requires an extra click that isn't necessary.
• Second, we need to add an "All Continents" choice so the user can view the whole list.
• Third, the drop-down still shows "Asia" when we've filtered the list to "South America". We need to make this drop-down show the user's choice when the list is filtered.
7. To make the drop-down list automatically submit, add the following to the end of the <select> tag:
• onchange="formSearch.submit()"
• The complete line will now read:
<select name="selContinent" id="selContinent" onchange="formSearch.submit()">
  
8. To add an "All Continents" choice:
• Click the selContinent drop-down, then click the Dynamic button.
• Click the + button to add a new static value to the list. Add a value of % and label it "All Continents".
  
• Edit the countries recordset. Change the Default Value of the variable varContinent to %.
9. 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 ($varContinents_countries == $row_continents['Continent']) {echo 'selected';}?>
• "varContinent_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_continents['Continent']?>"
<?php if ($varContinent_countries == $row_continents['Continent']) {echo 'selected';}?>
>
<?php echo $row_continents['Continent']?></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.
10. Publish and test. All done!

Related articles:

• Two-category filter -- continents and region, for example
• Dependent drop-downs -- one list adjusts based on the selection made in another

<- back to index | ©2007 clg.