This tutorial will show you how to update or insert a record without first checking to see if the record is in the database.

When it comes to the question of "Is the record in the database, if so, update, else insert", there has to be an easy way do write it with less code.

Well of course there is!

So lets look at the old way of doing this.

In this example we are keeping a record of each page a user visits and how many times they have visited it.

#CGI.REMOTE_ADDR# will give us the users I.P. Address.
#CGI.SCRIPT_NAME# will give us the page the user is on.
And we use cfqueryparam tag to combat SQL Injection attacks.

<!--- first lets check to see if record is in the database --->
<cfquery name="FindRecord" datasource="#REQUEST.dsnodbc#" username="#REQUEST.dsnodbc_user#" password="#REQUEST.dsnodbc_password#"> Select page_hit_count FROM page_hits WHERE page_name = <cfqueryparam value="#CGI.SCRIPT_NAME#" cfsqltype="cf_sql_varchar"> AND IP_address = <cfqueryparam value="#CGI.REMOTE_ADDR#" cfsqltype="cf_sql_varchar">
</cfquery>
	<!--- If record is in database, then update it --->
	<cfif FindRecord.recordcount EQ 1>
		<cfquery name="Update_Record" datasource="#REQUEST.dsnodbc#" username="#REQUEST.dsnodbc_user#" password="#REQUEST.dsnodbc_password#">
			UPDATE 	page_hits
			SET 	page_hit_count  = <cfqueryparam value="#(page_hit_count+1)#" cfsqltype="cf_sql_integer">
			WHERE 	page_name = <cfqueryparam value="#CGI.SCRIPT_NAME#" cfsqltype="cf_sql_varchar">
			   AND IP_address = <cfqueryparam value="#CGI.REMOTE_ADDR#" cfsqltype="cf_sql_varchar">
		</cfquery>
	<!--- If record isn't in database, insert it --->
	<cfelse>
		<cfquery name="insert_record" datasource="#REQUEST.dsnodbc#" username="#REQUEST.dsnodbc_user#" password="#REQUEST.dsnodbc_password#">
			INSERT page_hits(page_name, IP_address, page_hit_count)
			VALUES( <cfqueryparam value="#CGI.SCRIPT_NAME#" cfsqltype="cf_sql_varchar">, <cfqueryparam value="#CGI.REMOTE_ADDR#" cfsqltype="cf_sql_varchar">, 1)
		</cfquery>
	</cfif>


This is a pretty basic piece of code. But we can improve upon this and make it just a bit better by using the Result Attribute of the CFQUERY tag.

The Result Attribute when added to the CFQUERY tag looks like this.

<cfquery name="insert_record" datasource="#REQUEST.dsnodbc#" username="#REQUEST.dsnodbc_user#" password="#REQUEST.dsnodbc_password#" result="Result_Name">

You can name the Result pretty much any thing, but to keep things straight I usually name it what ever the query name is _result.

Example:

<cfquery name="insert_record" datasource="#REQUEST.dsnodbc#" username="#REQUEST.dsnodbc_user#" password="#REQUEST.dsnodbc_password#" result="insert_record_result">

The Result Attribute gives you the following information...

Variable name

Description

result_name.sql

The SQL statement that was executed.

result_name.recordcount

Number of records (rows) returned from the query.

result_name.cached

True if the query was cached; False otherwise.

result_name.sqlparameters

An ordered Array of cfqueryparam values.

result_name.columnList

Comma-separated list of the query columns.

result_name.ExecutionTime

Cumulative time required to process the query.

result_name.IDENTITYCOL

SQL Server only. The ID of an inserted row.

result_name.ROWID

Oracle only. The ID of an inserted row. This is not the primary key of the row, although you can retrieve rows based on this ID.

result_name.SYB_IDENTITY

Sybase only. The ID of an inserted row.

result_name.SERIAL_COL

Informix only. The ID of an inserted row.

result_name.GENERATED_KEY

MySQL only. The ID of an inserted row. MySQL 3 does not support this feature.


But in this example we are going to focus on "recordcount" that will let us know if any records were found in the database.
But wait! RecordCount is already an options in the query results (queryname.recordcount). Very true. But not when updating or inserting.

Using recordcount from the Results Attribute we can now make some improvements to our code from above.

Instead of checking to see if the record is in the database THEN updating the record or inserting it, we will just try to update it.
If the record is in the database it will update it. If not, we will know from the Results.recordcount and then just insert the data.

	<!--- If record is in database, then update it --->
	<cfquery name="Update_Record" datasource="#REQUEST.dsnodbc#" username="#REQUEST.dsnodbc_user#" password="#REQUEST.dsnodbc_password#" result="Update_Record_Results">
		UPDATE 	page_hits
		SET 	page_hit_count  = <cfqueryparam value="#(page_hit_count+1)#" cfsqltype="cf_sql_integer">
		WHERE  	page_name = <cfqueryparam value="#CGI.SCRIPT_NAME#" cfsqltype="cf_sql_varchar">
		    AND IP_address = <cfqueryparam value="#CGI.REMOTE_ADDR#" cfsqltype="cf_sql_varchar">
	</cfquery>
	<!--- If record isn't in database, insert it --->
	<cfif Update_Record_Results.recordcount EQ 0>
		<cfquery name="insert_record" datasource="#REQUEST.dsnodbc#" username="#REQUEST.dsnodbc_user#" password="#REQUEST.dsnodbc_password#">
			INSERT page_hits(page_name, IP_address, page_hit_count)
			VALUES( <cfqueryparam value="#CGI.SCRIPT_NAME#" cfsqltype="cf_sql_varchar">, <cfqueryparam value="#CGI.REMOTE_ADDR#" cfsqltype="cf_sql_varchar">, 0)
		</cfquery>
	</cfif>

We removed several lines of code and a database query. It is a small but good way of improving code and very useful for many situations.

About This Tutorial
Author: Jeff Mendelsohn
Skill Level: Intermediate 
 
 
 
Platforms Tested: CFMX
Total Views: 56,181
Submission Date: April 21, 2011
Last Update Date: April 21, 2011
All Tutorials By This Autor: 3
Discuss This Tutorial
Advertisement

Sponsored By...
Mobile App Development (IOS, Android, Cordova, Phonegap, Objective-C, Java) - Austin, Texas Mobile Apps - Touch512, LLC.