Saving our data

Now if we could save our data, that would be great. We will be using the same database that we used in the last chapter. We can see this book's site at

<cfquery datasource="cfb" name="qryInsert">
INSERT INTO product( name , description , price)VALUES( <cfqueryparam value="#form.name#"> , <cfqueryparam value="#form.description#"> , <cfqueryparam value="#form.price#">) 
</cfquery>

Here, we see the basic code structure of an INSERT query. We can see that the one thing different from the standard queries here is the VALUES section. We have a new ColdFusion tag. The query param tag<cfqueryparam> is used to help make sure that a SQL injection is not used to attack your server. This tag provides additional functionality that is beyond the scope of this example. We just have to remember that for all insert and update queries, we should always use the param tag to protect the data. The queryParam tag is a great tool to block SQL injection attacks. It also automates passing either text or non-text types to SQL for us. That brings us to our UPDATE query:

<cfquery datasource="cfb" name="qryUpdate">
UPDATE product SET name = <cfqueryparam value="#form.name#"> , description = <cfqueryparam value="#form.description#"> , price = <cfqueryparam value="#form.price#"> WHERE ID = <cfqueryparam value="#form.id#"> 
</cfquery>

There are a few obvious differences between the two queries beyond what they accomplish. We see an additional item in this query called WHERE. Be careful using updates because you can change data you don't intend to change if set incorrectly. Without this, you would not be able to update an individual record; instead you would update all the records in your query. So it is important to get it right. You never update the ID field of a record either. This means we do not put the ID in the set section of the query.

Note that we use the<queryparam> tag to protect our data integrity. We need to do one more thing, so that our page knows which is the correct query to execute, on any given page call. When a form processing page is called, we need to check if a form was sent. We will do that by looking for submit in the attributes. We do not look into the form value because there can be times you set the method to get. In this case, the values will show up in ColdFusion as URL variables. Here again, we save time by not having to write a whole bunch of extra page logic to handle how information comes to our page. It is another case of admiring code reuse in action.

<cfif structKeyExists(attributes,"submit")>
</cfif>

Now, we need to know if we should be inserting or updating the record. We are using another one of those simpler techniques here. We set the ID field inside the data object to zero if it is a new record. Therefore, if ID equals zero, we will insert the record into our database. Otherwise, we will update the record with a matching ID value. The following is the modified logic that goes into the middle of the previous conditional statement. It goes in the middle because we only want it to run if the page was submitted from a form. If you are doing this, do not forget to include a form button, and name the button by inserting submit for the name attribute of the input tag, as in the following example. We could name it something else, but we have to remember to name the submit button and check for matching attributes:

<cfif structKeyExists(attributes,"submit")>
<cfif attributes.id EQ 0>
<!--- REPLACE WITH INSERT --->
<cfelse>
<!--- REPLACE WITH UPDATE --->
</cfif>
</cfif>

Let us take a look at the processing part of our page:

<!--- Example: product_edit.cfm --->
datadatasaving<!--- Processing --->
<cfinclude template="request_attributes.cfm">
<cfparam name="attributes.id" default="0">
<cfscript>
objProduct = createObject("component","product").init(dsn="cfb");
objProduct.load(attributes.id);
</cfscript>
<cfif structKeyExists(attributes,"submit")>
<cfif attributes.id EQ 0>
<cfquery datasource="cfb" name="qryInsert">
INSERT INTO product( name , description , price) VALUES( <cfqueryparam value="#attributes.name#"> , <cfqueryparam value="#attributes.description#"> , <cfqueryparam value="#attributes.price#">) 
</cfquery>
<cfelse>
<cfquery datasource="cfb" name="qryUpdate">
UPDATE product SET name = <cfqueryparam value="# attributes. name#"> , description = <cfqueryparam value="# attributes. description#"> , price = <cfqueryparam value="# attributes.price#"> WHERE ID = <cfqueryparam value="# attributes.id#"> 
</cfquery>
</cfif>
</cfif>

We can see that the page has become much larger. Now, it's time to see it working. Let us modify some data and submit the form back to the server. Modify the description by adding and sweetest member, and then click on the submit button: