First off, you need to be able to write SQL queries before you work with them in ColdFusion. Once you are able to write them, it is just a matter of putting them in the right ColdFusion Tags, and adding the right arguments. The starting tag for queries is <cfquery> and the closing tag is </cfquery>. A few arguments can be added, but we will stick with the simple ones. First, your awesome query needs a name for later reference. You can use name=”” where your name variable will be within those quotes to set the query name in ColdFusion. Next, we need to know where to find the database. Now, I can’t tell you exactly how to do this. You need to figure out how to create a connection string to a database. It is somewhat complicated and varies based on what database you are wanting to use. You can use the power of Google to figure that out. Once you figure it out, set that equal to the variable myDatabase and we will just use that variable to reference our database.
Returning Data from a Database
Notice how when I was using the <cfloop>, I return #myQuery.column1# and #myQuery.column2#, these are both column names in the someTable table.
Inserting Data into a Database
Now, I don’t want to destroy your database, so I am just going to show you an example for the INSERT. While not as dangerous as a DELETE, always try to be careful that you don’t insert a million junk rows. We are going to input some variables in our ColdFusion code into the database.
Since you already know some SQL, you could tell the INSERT query was going to be a little different than the SELECT query. Normally, you will not have the <cfset goodReason = “Chasing Squirrels”> in the statement because it should be set somewhere else by the user input. Our <cfparam name=”goodReason” default=”” type=”string”> is searching for GET and POST data that might contain our variable goodReason, if it cannot find it then goodReason‘s value is set to “” (nothing). The type is important to help stop users from switching what it is supposed to be. While it isn’t that helpful with a string, consider if goodReason was supposed to be a number, but the user switched it to a string. Obviously, that wouldn’t be good for business. The same thing is happening down in the actual query. The <cfqueryparam value=”#goodReason#” cfsqltype=”CF_SQL_VARCHAR”> is the last line of defense against SQL injections attacks in ColdFusion. It makes sure that the value is what type of value we want it to be.
Deleting Data from a Database
I highly recommend that you back this data up somewhere else before deleting it or consider making a column in the database that is something like “isVisible”, where you could get the records. The idea would be that you would only show records that have isVisible set to 1, and the records set to 0 would show you past records so you don’t lose any data. But, if you must annihilate the data, we can cover that too. Again, I am not going to destroy our precious database, but I will show you an example.
Again, the <cfset goodReasonGoneWrong = “Chasing Squirrels”> will usually not be there. What is the point in setting a variable right before inserting it into a database? We would be better off just putting VALUES(‘Chasing Squirrels’) instead. Now that I have beaten that dead horse, you probably guessed that the DELETE query would also be different from the rest. We set the column column1 equal to our <cfqueryparam value=”#goodReasonGoneWrong#” cfsqltype=”CF_SQL_VARCHAR”> to escape SQL injection. So, this query actually deletes anything in our table that has ‘Chasing Squirrels’ in the column1 column.