Günter Schenk :: Tutorials Corner
Adobe Dreamweaver Developer Toolbox

     

Dynamic Lists: exploring the Filter Conditions

Author: Günter Schenk, Added: 21.01.2008, Updated: 23.01.2008

Table Of Contents

Title	Version
Introduction	n/a
Overview and basic examples	n/a
Apply a "Range" search on just *one* column	n/a

1. Introduction

The Dreamweaver Developer Toolbox "Dynamic Lists" come with a pretty great feature :: adding a variety of standard SQL "comparison operators" to a numeric or date/datetime search field will help you narrowing down the results even further.

In case you´re already familiar with this feature, the first chapters of this tutorial might seem boring to you, all the more you certainly do know how to apply e.g. an ">=" (greater than) operator to whatever search field -- but wait, I´m sure to have some pretty interesting stuff up my sleeves for even the seasoned Dreamweaver Developer Toolbox enthusiast :: I´m also going to answer a burning question which should be of notably more interest, as one special scenario has never been officially documented :: how can I add a "date range" or a "price range" search to my Dynamic List ?

As this tutorial is also aimed at the less experienced ADDT user who´d like to know more about the benefits of using those comparison parameters, let´s start with a brief explanation on what you can do with them

2. Overview and basic examples

2.1. available Comparison Operators


2.2. Supported column types

The abovementioned Comparison Operators will work with with a variety of "date" and "numeric" table columns. The following italic examples represent the standard syntax used by the MYSQL database

• Date Type :: date (YYYY-MM-DD) and datetime (YYYY-MM-DD HH:MM:SS) columns
• Numeric Type: integer (e.g. 100) and floating point columns (float(6,2)) are equally supported

2.3. Basic examples and live demos

The following two examples are - technically speaking - nothing to write home about, and it´s all something you can easily apply to your existing ADDT Dynamic List and without modifying any code.

2.3.1. a certainly pretty basic example would be to search for a price that´s greater than (>) 700 bucks, but has been submitted in 2005 or earlier (<=)...



...what will return a refreshed list that´s showing only the matching products:




2.3.2. if you like it a little more sophisticated, you can also have the Dynamic List perform a "price range" and/or "date range" search against two numeric "Price" and two date columns...



...what will, once again, return a refreshed list that´s showing only the matching products:



All "price" and "date" fields used in this example will have to reference separate table columns.



So far, so good -- all this might have been exactly what you already know, so now let´s move on to some advanced stuff you most probably have been struggling with so far...

3. Apply a "Range" search on just *one* column

Just recently a Dreamweaver Developer Toolbox user posted a truly interesting question on the Adobe ADDT forums, asking "How do I filter by date range ? (ie 1-6-2007 to 31-1-2008)". Well, this specific request got me pretty curious, because I always thought it were indeed cool if ADDT´s Dynamic Lists allowed me to search against just *one* date column by passing individual dates from separate "Starting Date" and "Finish Date" fields.

It took me approx. one hour to find out if & how the default Dynamic List´s code can be tweaked in order to make this possible -- and as this experiment happened to turn out fine, I thought to myself "wow, this definitely deserves writing an in-depth tutorial...", and here it comes:

3.1. Create a blank PHP page

...and save it in the desired site directory. This page is meant to contain an ADDT Dynamic List

3.2. Add a standard Dreamweaver Recordset

Unlike a regular Dynamic List this very one will have to retrieve the required data from a given Dreamweaver Recordset -- why this ? It´s because we´ll later have to manually modify the "data source" references a little, and as the structure of ADDT´s "Get data from: Table" recordset is usually exceedingly complex and hence hard to "hack", we´ll rather resort to a regular Recordset at this point.

Name this Recordset mainquery, select your own Connection, have it point to the table "addt_tutorial_filters" and select the columns "id", "productname", "price_min" and "date_min", as displayed next:



3.3. Modify the created Recordset

As you´ll first need to modify the generated Recordset "mainquery" in order to have the "Dynamic List Wizard" pick up the column names *you* need, switch the page to CODE and locate the following code fragment that can be found near the top of the page:

$query_mainquery = "SELECT id, productname, price_min, date_min FROM addt_tutorial_filters WHERE {$NXTFilter_mainquery} ORDER BY {$NXTSort_mainquery} ";

In case you gave the corresponding Online Demo a try by now, you might be asking yourself "I can´t detect any columns within this query code, which seem to be referencing the List´s search fields Maximum Price and Finish Date -- couldn´t I rather include the existing table columns price_max and date_max in the Recordset ?". Well -- no ! ;-) I have left out those columns on purpose for several reasons:

• we´re just going to search on *one* price column and *one* date column, means the other ones are dispensable in this context
• *if* we included those redundant columns, we´d have a hard time deleting all references to them later on

However, you´re right nonetheless -- the search fields for "Maximum Price" and "Finish Date" obviously *must* have an internal pointer to defined table columns, otherwise the search will not be able to take those fields into account. So, what are we going to do to add the "missing" columns ?

Well, let´s resort to a widely used "alias column" trick, as displayed next:



Here´s the copy & paste version:

$query_mainquery = "SELECT id, productname, price_min AS price_low, price_min AS price_high, date_min AS date_low, date_min AS date_high FROM addt_tutorial_filters WHERE {$NXTFilter_mainquery} ORDER BY {$NXTSort_mainquery} ";

How does it work in simple terms ? AS will tell MySQL that the following alias column "price_low" has to be treated as real one -- and as the modified query actually defines two "alias" columns for each existing one (price_min AS price_low, price_min AS price_high), you´ll later see that the ADDT "Dynamic List Wizard" will detect those alias columns just fine.

Alright, let´s now...

3.4. Create the Dynamic List

Invoke ADDT´s "Dynamic List Wizard" and specify the required information as displayed in the following screenshots:

Step 1/4



Step 2/4



Here´s where you can see that the ADDT Dynamic List Wizard indeed detects the "alias" columns you defined in the modified Recordset.

Step 3/4



Step 4/4




3.5. Modify the List´s "Filter" and "Sorter" code

It´s no secret that the Dreamweaver Developer Toolbox "Dynamic Lists" basically perform two actions :: the list of displayed records may get "filtered" and "sorted". The Dynamic List of course contains two related code blocks providing an array of control commands -- which basically create a reference to the table columns used for the respective list.

Usually you´d never need to "hack" those control commands, but as this specific "Apply a Range search on just one column" example requires us to both bypass (the Filter code) and deactivate (the Sorter code) certain components, I´m going to show you where to change what:

a) modify the "Filter" code:



The abovementioned modifications certainly deserve some explanation:

a.a.) As we´re going to perform a search on the "price_min" column only, our alias columns "price_low" and "price_high" will both have to point to this existing column.

a.b.) The same rule applies to our alias columns "date_low" and "date_high" -- they will both have to point to the existing "date_min" column, as this is the data source for our Date Range search.

Well, you might wonder what happens when all this doesn´t get modified :: the Dynamic List will initially load just fine, but when clicking the List´s "Filter" button, the reloaded page will be blank, and MySQL will return an error message saying "column price_low not found".

Let´s now...

b) modify the "Sorter" code:



I guess you´ll get the point :: is it really required to sort the Dynamic List on e.g. an alias column "price_high", which will of course be displaying the same values as the alias column "price_low" ? Certainly not, and that´s why I suggest to "comment" (//) aka deactivate the "price_high" and "date_high" Sorters -- in order to simply get them out of the way, because we don´t need them.


3.6. Let´s have a look at the Dynamic List

When viewing the modified Dynamic List in a browser, it definitely looks promising -- but some things are certainly not the way you´ll prefer them to be:




wouldn´t you rather like to see it this way ?




The List´s "Maximum Price" and "Finish Date" table columns should really not do anything except to provide a search field -- means we´ll need to get rid of the duplicated values and the sortable header:

a) Delete the sortable links from the "Maximum Price" and "Finish Date" headers

Click one of the displayed table headers and switch to CODE -- and just delete the links, as displayed next:



This should return the following result:




b) Do away with the duplicated values

This is also a pretty easy task :: click one of the table cells containing a data placeholder, switch to CODE, and delete the redundant "price_high" and "date_high" cells:




Now there´s just one modification left to do :: set the colspan of the "price_low" and "date_low" cells to 2:



Believe it or not -- you´re done !! :-)