ColdFusion Day 32:

================

 

ORDERING DYNAMICALLY

 

When presenting a list of information to the user, you usually display the list in a certain order for example, if you query your database for supplier information, you can sort the results by supplier name. See listing 10.3.

 

LISTING 10.3:    ch1003.cfm

    <CFQUERY NAME="SelSupplier" DATASOURCE="sybex">

    SELECT s_name, s_city, s_state, s_phone, s_email

    FROM supplier

    ORDER BY s_name

    </CFQUERY>

 

    <TABLE Border="1">

    <TR>

        <TD>Supplier</TD>

        <TD>City</TD>

        <TD>State</TD>

        <TD>Phone</TD>

        <TD>E-mail</TD>

    </TR>

 

    <CFOUTPUT QUERY="SelSupplier">

    <TR>

        <TD>#SelSupplier.s_name#</TD>

        <TD>#City#</TD>

        <TD>#State#</TD>

        <TD>#Phone#</TD>

        <TD>#E-mail#</TD>

    </TR>

    </CFOUTPUT>

    </TABLE>

 

This template displays the list ordered by the supplier name, as shown at:

http://www.accc.net.au/sybex/ch1003.cfm

 

The list is nice but can be made better. What if you give the user the ability to click the heading to select the primary sort order?  This feature can be useful, especially if the user has to view a long list, because the user could control the order in which the output is viewed. Listing 10.4 provides the user the option to select the order of the information to be displayed.

 

LISTING 10.4:    ch1004.cfm

    <CFPARAM NAME="DefOrder" FEFAULT="s_name">

 

    <CFIF IsDefined("URL.DOrderBy") IS "True">

        <CFSET DefOrder = URL.DOrderBy>

    </CFIF>

 

    <CFQUERY NAME="SelSupplier" DATASOURCE="sybex">

    SELECT s_name, s_city, s_state, s_phone, s_email

    FROM supplier

    ORDER BY #DefOrder#

    </CFQUERY>

 

    <TABLE Border="1">

    <TR>

        <TD><A HREF="ch1004.cfm?DOrderBy=s_name">Supplier</A></TD>

        <TD>><A HREF="ch1004.cfm?DOrderBy=s_city">City</A></TD>

        <TD>><A HREF="ch1004.cfm?DOrderBy=s_state">State</A></TD>

        <TD>><A HREF="ch1004.cfm?DOrderBy=s_phone">Phone</A></TD>

        <TD>><A HREF="ch1004.cfm?DOrderBy=s_email">E-mail</A></TD>

    </TR>

 

    <CFOUTPUT QUERY="SelSupplier">

    <TR>

        <TD>#SelSupplier.s_name#</TD>

        <TD>#s_city#</TD>

        <TD>#s_state#</TD>

        <TD>#s_phone#</TD>

        <TD>#s_email#</TD>

    </TR>

    </CFOUTPUT>

    </TABLE>

 

You create a variable called DefOrder, which will contain the column name to order the SQL query. You also use the URL variable as the dynamic parameter in the ORDER BY clause. By specifying a different hyperlink reference to each column heading, you can dynamically set the order in which to display the information.

 

FILTERING DYNAMICALLY

 

Dynamic filtering is useful if you have a search page that enables the user to set various criteria to limit the result of the query. You dynamically build the WHERE clause of the SQL query based on the criteria that the user has specified.

 

Let's take our supplier table and create a form that enables the user to enter a partial supplier name and a partial city. See listing 10.5.

 

LISTING 10.5:    ch1005.cfm

    <CFIF IsDefined("Form.in_s_name")>

        <CFSET in_s_name = "#Form.in_s_name#">

     <CFSELSE>

        <CFSET in_s_name = " ">

    </CFIF>

 

    <CFIF IsDefined("Form.in_s_city")>

        <CFSET in_s_city = "#Form.in_s_city#">

     <CFSELSE>

        <CFSET in_s_city = " ">

    </CFIF>

 

    <FORM ACTION="ch1005.cfm" METHOD="Post">

    <PRE>

    Supplier:            <INPUT TYPE="Text" NAME="in_s_name"               VALUE="<CFOUTPUT>#in_s_name#</CFOUTPUT>"><BR>

    City:            <INPUT TYPE="Text" NAME="in_s_city"     VALUE="<CFOUTPUT>#in_s_city#</CFOUTPUT>"><P>

    </PRE>

    </FORM>

 

    <CFQUERY NAME="SelSupplier" DATASOURCE="sybex">

    SELECT s_name, s_city, s_state, s_phone, s_email

    FROM supplier

    <CFIF #in_s_name# neq " " or #in_s_city# neq " ">

        WHERE

    </CFIF>

    <CFIF #in_s_name# neq " ">

        s_name LIKE '%in_s_name#%'

    </cfif>

    <CFIF #in_s_city# neq " ">

        <CFIF #in_s_name# neq " ">

        AND

    </cfif> 

        s_city LIKE '%in_s_city#%'

    </CFIF>

ORDER BY s_name      

    </CFQUERY>

 

    <TABLE Border="1">

    <TR>

        <TD>Supplier</TD>

        <TD>City</TD>

        <TD>State</TD>

        <TD>Phone</TD>

        <TD>E-mail</TD>

    </TR>

 

    <CFOUTPUT QUERY="SelSupplier">

    <TR>

        <TD>#SelSupplier.s_name#</TD>

        <TD>#s_city#</TD>

        <TD>#s_state#</TD>

        <TD>#s_phone#</TD>

        <TD>#s_email#</TD>

    </TR>

    </CFOUTPUT>

    </TABLE>

 

If you have a long list of suppliers and would like to view a limited list, you need a restrictive WHERE clause. In listing 10.5, the form prompts the user for a supplier name and/or city. The code builds a SQL query based on the entered information. If only a supplier name is entered, the WHERE clause will be

 

    WHERE s_name LIKE '%#in_s_name#%'

 

If only a city is entered, the WHERE clause will be

 

    WHERE s_city LIKE '%#in_s_city#%'

 

If both a supplier name and the city is entered, the WHERE clause will be

 

    WHERE s_name LIKE '%#in_s_name#%'

    AND WHERE s_city LIKE '%#in_s_city#%'

 

If both the supplier name and the city name are not entered, the WHERE clause is not included in the query.

 

As you can see through dynamic filtering, you allow your users to control the scope of their search. Your code will check for entered values and set conditions within your SQL query by using the CFIF tag.