This section moves beyond the basic database manipulation techniques we discussed in earlier chapters. Here, we’ll cover methods for creating dynamic SQL, creating and modifying database tables using SQL, using aggregate and scalar functions, performing table unions and joins, and several other database-manipulation techniques. These are the kinds of operations that allow you to interact with databases at a higher level. Most advanced applications such as shopping carts, threaded discussion lists, and business-to-business applications use one or more of the techniques described in this section.
An extremely powerful feature of ColdFusion is the ability to generate dynamic SQL queries based on a variety of inputs. In Chapter 3, you learned how to pass a single dynamic value in an SQL statement:
SELECT Name, Title, Department FROM EmployeeDirectory WHERE ID = #ID#
this concept a bit in Example 11-9 to allow for
completely dynamic SQL in the
WHERE clause of a
Example 11-9. HTML form for searching database records
<!--- Query the EmployeeDirectory table for a list of departments ---> <cfquery name="GetDepartments" datasource="ProgrammingCF"> SELECT DISTINCT Department FROM EmployeeDirectory ORDER BY Department </cfquery> <h2>Locate a User</h2> <form action="search.cfm" method="post"> <table> <tr> <td>Name:</td> <td><input type="text" name="Name" size="20" maxlength="80"></td> </tr> <tr> <td>Title:</td> <td><input type="text" name="Title" ...