Making our data query flexible

We set the default value so the page doesn't fail if there is no value for the ID variable passed in. We added the singular getProduct variable. It actually returns a recordset just like rsProducts returns a recordset. The only difference is that this will return a single record after we modify the CFC. Currently the CFC doesn't handle the where argument even though it is part of the method. We have to build in the functionality. This is what the method should look like now. Change the highlighted lines to make this method work correctly. As we are not adding the query type to the <cfoutput> tag inside the if condition where there is only one record, it will not loop through the query. It will only return the first record in the recordset. In this instance there is only one record, so that would be the same. This information is just for future reference, so we will understand why only one row appears on our web page and why we have to declare the query inside the <cfoutput> tag for other occasions:

<cfquery datasource="cfb" name="rsReturn">
SELECT
id, name, description, price
FROM
product<cfif arguments.where NEQ "">
WHERE
1=1
AND
#arguments.where#</cfif>
</cfquery>
<cfreturn rsReturn/>
</cffunction>

This is the SQL for the Where statement. This is also why we called the argument where. Some people may prefer to call this argument a filter. Our goal was to make it more like a query for the sake of learning. You can pass in any legal value now. If you review the calling page, you will see we are passing in url.id now. This means that if there is no value, it will not find any match and we will get what is called an empty recordset. If you pass in an ID that matches a record in the database, then you will get that record back and it will return only one row. It is best practice to put our query values in<cfqueryparam> wrappers to prevent SQL injection attacks. At this time we are learning basic code, so this is safe unless we are planning to hack ourselves!

The last thing we did in our page was to add a table section that shows the values of the requested record only if a match is found. This is the result when you click on one of the links. The individual record is shown below the unordered list. Click through the different items and you will see that the products are retrieved from the database:

Now we will take a moment to look at some of the advantages of using CFCs. Let's think what we would have done if this were two pages and we were adding the<cfquery> section of code to the actual pages. You would have queries on both pages. Now we are just starting to see the power of using an object. The page is much simpler. There is still work to do though; we need to take this example just a little farther. Right now we are still not making full use of the object. We will modify the getRecord part of our code first and rerun this page to show that we actually are getting the same results. Again you will see the rows that were changed have been highlighted. We remembered to add var to our method variable to ensure it is thread-safe. You will also notice that we are calling another one of the methods inside the same object without having to name any object. Anytime you call a method in the same object, you address it just like you would a regular built-in ColdFusion function. Lastly we again take the results that were returned from the getRecordset and store them briefly in this method's myReturn variable. You can name this variable anything you wish; we are using this name because it is descriptive of the variable's purpose.

<cffunction name="getRecord" access="public" output="false">
<cfargument name="id" required="false" />
<cfset var myReturn = getRecordset(where : "id = #arguments.id#")> <cfreturn myReturn /> 
</cffunction>

Now we need to modify the code in the calling page again. This is the only line of code we need to change. Search for the rsRecord line and change it to this new line:

rsProduct = objProduct.getRecord(url.id); 

When you run the page you should see no difference. Correct, functionally there are no significant differences. It was simpler to call the method because we placed the where clause into the getRecord function. What we want to do for our object though is to load the current record into the protected attributes of the object. Let's change the name of getRecord to load. Then we will loop assign the values returned in the recordset to the protected attributes of the object instance. If no record is returned, we will set all of the variables to a default value. We could do this in the same function, but we will create another new function to handle this.

Tip

The double equals sign in the following script is an equality comparison. A single equals sign is an assignment and would not function correctly for a logical check and often will show up as an error if misplaced.

<cffunction name="load" access="public" output="false"> 
<cfargument name="id" required="false" />
<cfscript> var myReturn = getRecordset(where : "id = #arguments.id#"); setAttributes(myReturn); </cfscript> 
<cfreturn myReturn />
</cffunction>
<cffunction name="setAttributes" access="public" output="false"> <cfargument name="record" required="true"> <cfscript> if(arguments.record.recordCount ==== 0){ variables.attributes.page = ""; variables.attributes.description = ""; variables.attributes.price = 0; } else { variables.attributes.name = arguments.record.name; variables.attributes.description = arguments.record.description; variables.attributes.price = arguments.record.price; } </cfscript> </cffunction> 

After we changed the function to load, we moved the internals of that function into a script. It is worth noting that we must do certain things in order or we will get an error when creating methods. First, you must declare all your arguments that may be passed into our methods. Next, we must create var variables to make sure they are thread-safe.

Conditional processing in tags should still use the EQ, GT type testing. In CF, there are functions that work in a manner similar to what you either know from JS and other platforms. As of CF9, it is possible to write nearly 100 percent of code with script if desired. Many developers have found that a mix of tags and script is a matter of personal coding style.