When it comes down to it, 99% of the time, we're processing, analyzing, tweaking to get data into both the datatype, and validity. However as time goes by, we learn different tricks to help prevent the common errors we had of the past: SQL Hacking, User Error, Scope Error and a few more I'll remember as I go on.
In my own preparations of the application, I think about what kind of data is coming, and what priorities different scopes have, and what kind of validation measures i have to stand by. So to help me, I've come up with means of setting that all up, to reduce the number of data errors.
I always use cfparam using a new name for the scope of the application itself. I simply create a cfparam of local_variablename, to help make sure that I have absolute control of what scope priority.
For example, in the past I can remember using the same variable name for url, form and local, and there was a lot of confusion as to which one was populating the variable in the application.
So let's say you have a variable called movie_id, first i determine what kind of datatype is that going to be? Most likely with id as past of the name, it's going to be a number. So i type in like this.
1. cfparam name=local_movie_id default=0 type=mumeric
2. What possible locations for data do I have?
cfif isdefined("url.movie_id") cfset local_movie_id = url.id
cfif isdefined("form.movie_id") cfset local_movie_id = form.movie_id
now i could re-arrange the above, into which source i want to come from.
3. Simplify the checking of incoming data
cfif local_movie_id then take action, else ignore and show error of no data sent from previous page.
4. Why is this better?
Because instead of multiple if's, checking each possible scope for it's existance and then having repetitions of the same exact actions, it's much easier to just have one if statement. Was the data passed, if so take action, otherwise error.
It's much easier to read, you just have to do the setup.
5. Use cfqueryparam
Now for different databases, cfqueryparam can improve performance, it depends if it has the feature called BIND Variables, which it treats the queries with different where values, etc to be the same, which means it caches the query and just changes what results come back.
The other major reason I always use it, unless i have to cache the query in other means, is for it's data validation.
I really don't care how simple or complex an application is, I always make sure the datatype is validated before being sent to the database. It makes a great practice, and makes sure you don't waste time, having to go back to fix your code after you thought you were done.
Now, maybe 99% of the time it never happens, or it's just some maladjusted user, playing with url variables, but you have to protect your database against that.
Now that we've made sure the data, is in the right datatype and is validated, what things can we do to make it more organizable and efficient?
It really depends on the complexity of the data and how many different ways you need to organize it.
I tend to like creating fake queries or as we call them, query of queries, because it makes it much easier to do all kinds of whacky where's/sorting/order by etc. They are really easy to set up...
1. First you create the query
cfset myQuery = QueryNew("first, last, address, city, state, zipcode, email, acct_balance")
2. Then you want to populate the data, and this is where it can get really fun.
Such as if you have this datafeed or report that is a file in a .csv format, you can convert it into a query of queries. I have done this quite a few times, and a lot of fun.
Or it can be a structure object being sent as an application, url or form variable, that you just need to turn into some kind of report.
Wherever the source is, it's going to be in the form of a looping mechanism to add row by row to the query object, called myQuery.
cfloop however your data is getting imported
cfset temp = QueryAddRow(myQuery)
cfset temp = querysetcell(myQuery, first, "#firstname#")
cfset temp = querysetcell(myQuery, last, "#lastname#")
the basic syntax is cfset temp = querysetcell(queryname, field, value)
Now you've created this query object, now you can play with it to help produce the results you want. And remember, depending on what variety of reports or results, to only grab the data once, unless you update the datasource frequentally.
Now we get into report generation, first what kind of data and in what order do you want?
For example, if you wanted the names, email of those who had a big account balance of over $100, you can do something like this:
cfquery name=bigbadclients dbtype="QUERY"
select first, last, email
where acct_balance > 100
Then you can do your normal cfoutput's, laying the data into table structures. OR for example you can export it into a csv or excel file.
Now you are starting to see, that once you have a handle on the best ways to handle data, you can do anything you want with them.
Have fun with your data, and share any of your best tips here at ColdFusion Purists.