Now that we have successfully set up a data source that ColdFusion can connect to, we can perform queries to access data located within the database. To perform these queries we are going to use the <cfquery> tag.
If you have worked with any other server side technologies you may know that most of them require several lines of code. For example, in ASP you would have a line of code to perform each of the following:
- Declare the variable that will hold the connection object, connection string and SQL statement
- Create an ADO connection object
- Declare the recordset variable
- Define the connection string, specify the database driver and location
- Define the SQL statement
- Open the connection to the database and execute the SQL statement
- Loop through the recordset and output the information
- Close the object
In ColdFusion, everything is done under the hood for you. You do not need to declare any connection objects, strings or recordsets. Instead, you simply wrap your SQL statement inside a <cfquery> tag that you will provide a name value for and a data source (that you created previously).
<cfquery name="GetCourses" datasource="DBTest"> SELECT courseid, coursename FROM CourseList </cfquery>
As you can see, this is a much less complicated way of accessing a database and doesn’t require you to remember the many steps required in other languages.
What about authentication?
When we setup our original data source you may have noticed that we provided a username and password in the initial values. By providing that information, it will always be available to any connection to that data source without specifically stating the username and password in the actual query. However, if you aren’t able to include those details in the data source setup, but still need to provide them to be able to connect, you can set their values in the <cfquery> tag as shown:
<cfquery name="GetCourses" datasource="DBTest" username="myusername" password="mypassword"> SELECT courseid, coursename FROM CourseList </cfquery>
In the above example, we have selected all the data from the table without any filter or criteria to be processed. If we want to retrieve data that meets a specific criteria, we would use a standard SQL WHERE clause to pass a certain condition to the recordset being returned. Using ColdFusion, we can take advantage of filters on a dynamic level and filter recordsets based on a user specified selection.
For example, if instead of returning all the course names for all courses, let’s only return the course name for the record that has a courseid of “2”.
<cfquery name="GetCourses" datasource="DBTest"> SELECT courseid, coursename FROM CourseList WHERE courseid = 2 </cfquery>
Now for the dynamic aspect. This time let’s pass the value of courseid as a variable from a previous page. This time we would pass the value as a variable to the query.
<cfquery name="GetCourses" datasource="DBTest"> SELECT courseid, coursename FROM CourseList WHERE courseid = #courseid# </cfquery>
As you can see by the above SQL statement we have now created a filter for the recordset that can be changed depending on the value that you pass to it.
Displaying Your Data
Now that we have sent the query to our database and it has sent us back a recordset, we need a way to display that data to the user. ColdFusion makes this incredibly easy with the <cfoutput> tag. You should remember this tag from the previous section on variables, only this time we will be passing an extra parameter to it to display our data. In the query we showed above, we passed a “name” to the cfquery tag and called it “GetCourses”. We are going to pass this same name to the <cfoutput> tag through the query attribute.
<cfoutput query="GetCourses"> #courseid# - #coursename#<br /> </cfoutput>
As you can see from the code above, we are using the <cfoutput> tag to loop through our returned recordset and are using the “#” sign around each column name just as we did when we were displaying the values of variables.
Using a Query Loop
There may be instances where using the <cfoutput> tag isn’t practical to use to output the recordset to the user. For example, if you are already using the <cfoutput> tag to do other processing, you can’t put another <cfoutput> tag within it. In these cases, you can still output results within a <cfoutput> tag by using the <cfloop> tag.
The best part about using the <cfloop> tag for queries is that the syntax isn’t all that different from the <cfoutput> tag.
<cfloop query="GetCourses"> #courseid# - #coursename#<br /> </cfloop>
The important thing to remember when using <cfloop> is that because you are using “#” signs around variable names to display to the screen, they must reside inside of a <cfoutput> tag.