[thelist] Doh! question for MySQL

[chris at fuseware.com]

Good tip, but you need to *always* lock application scoped variables.  And
since you don't want to exclusively lock a varaible if you can help it, this
is one of the few places you don't want to use CFPARAM.

So a modifed version might look like this:

<!--- Set a local variable to determine whether we need to load our
application var --->
<cflock timeout="30" throwontimeout="No" type="READONLY" NAME="GetStates">
	<CFIF NOT IsDefined("application.states")>
		<CFSET GetStates = TRUE>
	<CFELSE>
		<CFSET GetStates = FALSE>
</CFLOCK>

<!--- this is to make sure the query only runs once --->
<cfif GetStates>
	<!--- here is the query --->
	<cfquery name="get_states" datasource="yourdb">
	select * from states
	</cfquery>

	<!--- save the query --->
	<cflock timeout="30" throwontimeout="No" type="EXCLUSIVE" NAME="GetStates">
		<cfset application.states=get_states>
	<CFLOCK
</cfif>

-----Original Message-----
<deletia>

<tip type="cold fusion & saved queries" author="walker">
If you have queries that should only be run once, and don't change for all
users in your application (like a query that pulls the states for your
contact form drop down list) - then save the entire query as an application
variable....

For example:
in your application.cfm

(initialize the application variable)
<cfparam name="application.initialize" default="0">

(this is to make sure the query only runs once)
<cfif application.initialize is 0>

         (here is the query)
         <cfquery name="get_states" datasource="yourdb">
         select * from states
         </cfquery>

         (save the query)
         <cfset application.states=get_states>

         <cfset application.initialize=1>
</cfif>

then in your contact form (or elsewhere):

<select class="formbody" name="state">
<cfoutput query=application.states>
<option value=#state_id#>#state_name#</option> (where state_id and
state_name are columns in the states table)
</cfoutput>
</select>


This also works with session variables - if you want to save all of the
information about a user, etc....

For example, after a login has been validated:

         (here is the query)
         <cfquery name="get_user_info" datasource="yourdb">
         select * from users where user_id=#user_id#
         </cfquery>

         (save the query)
         <cfset session.user=get_user_info>

and then in your application:

You are logged in as: #session.user.username# (username can be swapped with
any other column in the users table)

</tip>

_________________________________________
walker fenton
walker at sdproductions.com
303.722.5473


---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !