Store a query result for later use

[DeZiner]

I have a query that pulls city and states from my database, then lists the cities in a list each as a link. I will then pass the city name through the url to pull up business listings, when a city is clicked. My only hang up is "How do I keep the list of cities from the first query? Can I store query results somehow and use them on another output? This will be a 3 page process.

Thanks for any help

 

[Silvano]

The easiest way would be (in my humble opinion) to write procedure using <cfquery... loop...> to go through the query and store it then in the list or array, depending on what exactly you need; you can also use Application or Session variable so the results are available from any page of your application

 

[DeZiner]

Would you happen to have an example of doing such. Or recomend a good book I have mastering coldfuion 4.5 but can't seem to find my answer

thanks!

 

[calista]

Hi, DeZiner!

I think this may give you an idea how to proceed. One piece of information I find it very useful to remember is that the result of a query is an array, and can be manipulated like any other array. Here's a piece of code I use to query the database, sort the results, and put it back into a list. Anyway, at least it's an example of a query loop and putting into a list.

<CFPARAM NAME=&quot;DocNameList&quot; DEFAULT=&quot;&quot;>

<!--- Perform queries --->
<CFQUERY NAME=&quot;GetProduct&quot; DATASOURCE=&quot;#Application.Datasource#&quot; DBTYPE=&quot;ODBC&quot;>
		SELECT 	*
		FROM 	ProductTable
		WHERE	ProdID = '#URL.Product#'
</CFQUERY>

<!--- Now, I am creating a list of the DocType names. --->
<CFLOOP INDEX=&quot;DocTypeIndex&quot; LIST=&quot;#GetProduct.ProdDocTypes#&quot;>
	<CFQUERY NAME=&quot;GetDocTypes&quot;
      		DATASOURCE=&quot;#Application.Datasource#&quot;
      		DBTYPE=&quot;ODBC&quot;>
			SELECT	*
			FROM	DocTypeTable
			WHERE	DocTypeID = #DocTypeIndex#
	</CFQUERY>
	<CFSET DocNameList = &quot;#ListAppend(DocNameList,GetDocTypes.DocType)#&quot;>
</CFLOOP>

<!--- Create an array. --->
<CFSET DocTypeArray = #ArrayNew(1)#>
<!--- Convert list to array. --->
<CFSET DocTypeArray = #ListToArray(DocNameList)#>
<!--- Sort the array. --->
<CFSET TempVar = ArraySort(DocTypeArray,&quot;textnocase&quot;,&quot;asc&quot;)>
<!--- Convert the array back to a list. --->
<CFSET SortedDocList = #ArrayToList(DocTypeArray)#>

Now that I think about it, I could have used ArrayAppend instead of ListAppend and saved a couple of conversions. Keep in mind that my variable &quot;DocTypeList&quot; could just as easily be &quot;Session.DocTypeList&quot;, and would, therefore, be available throughout the session. Hope I helped. You've helped me out a time or two, so I'm glad to return the favor if I can. Calista :-X
Jedi Knight,
Champion of the Force