Session Variable With Query Results

[DeZiner]

How do I get the query results into a variable then output those results from the variable onto the next page, Here is what I am currently using:

<cfquery name=&quot;findIt&quot; datasource=&quot;myDB&quot;>
SELECT city,state
FROM myTable
WHERE state='#form.state#' or city='#form.search#' or zip='#form.search#'
GROUP BY city, state
ORDER BY city

</cfquery>
<cflock TIMEOUT=&quot;30&quot; NAME=&quot;#Session.SessionID#&quot;>
<cfset session.query_results=session.findIt>
</cflock>
***Later in the page

<cfoutput query=&quot;session.findIt&quot;>
<li><a a href=&quot;

http://www.muURL.com/cozy/show_me.cfm?city=#city#&quot;>#city#</a></li>

</cfoutput>

This prints a list of cities on the page. I want to be able to click on a city to list the business' in that particular city while keeping the list of cities that was populated by the query results to remain the same and reprint in a list on the following page.

Page 2

<cflock timeout=&quot;30&quot; NAME=&quot;#Session.SessionID#&quot; TYPE=&quot;ReadOnly&quot;>
<cfoutput>
<li><a href=&quot;

http://www.myUrl.com/cozy/show_me.cfm?city=#city#&quot;>#session.findIt#</a></li>

</cfoutput>
</cflock>

This last bit of code prints on my page findIt rather then what is stored on the variable named findIt

Am I not unserstanding how to set the session variable???
Calista gave me an answer but still not understanding how to actually use the variable with the stored query results later on.

 

[calista]

Hi, DeZiner! Me, again!

Since the results of a query are an array, you have to deal with it as an array. Here is a sample I did to get some query results into a two dimensional array that is a session variable.

<!--- This is, of course, the query. --->
<CFQUERY NAME=&quot;GetPeople&quot;
		DATASOURCE=&quot;NewDatabase&quot;
		DBTYPE=&quot;ODBC&quot;>
		SELECT	PersonFirstName,PersonLastName
		FROM	PersonTable
</CFQUERY>
<!--- Set a variable for the record count(You'll need it later.) --->
<CFSET Session.Counter=&quot;#GetPeople.RecordCount#&quot;>
<!--- Create a new array. --->
<CFSET Session.QueryResults=ArrayNew(2)>
<!--- This is where you populate the array with the query results. --->
<CFLOOP INDEX=&quot;IdxOne&quot; FROM=&quot;1&quot; TO=&quot;#Session.Counter#&quot;>
	<CFLOOP INDEX=&quot;IdxTwo&quot; FROM=&quot;1&quot; TO=&quot;2&quot;>
		<CFSET Session.QueryResults[IdxOne][1]=&quot;#GetPeople.PersonLastName[IdxOne]#&quot;>
		<CFSET Session.QueryResults[IdxOne][IdxTwo]=&quot;#GetPeople.PersonFirstName[IdxOne]#&quot;>
	</CFLOOP>
</CFLOOP>

Here is one way you can output the information anywhere you need it:

<CFLOOP INDEX=&quot;IdxOne&quot; FROM=&quot;1&quot; TO=&quot;#Session.Counter#&quot;>
	<CFOUTPUT>#Session.QueryResults[IdxOne][1]# #Session.QueryResults[IdxOne][2]#</CFOUTPUT>
	<BR>
</CFLOOP>

Hope this makes it a little clearer. Calista :-X
Jedi Knight,
Champion of the Force

 

[DeZiner]

Thanks, That seemed to do it, I wasn't sure what I needed to change to meet my needs compared to what needed to stay the way you had it.

Could you explain this portion:
<CFLOOP INDEX=&quot;IdxOne&quot; FROM=&quot;1&quot; TO=&quot;#Session.Counter#&quot;>
<CFLOOP INDEX=&quot;IdxTwo&quot; FROM=&quot;1&quot; TO=&quot;2&quot;>
<CFSET Session.QueryResults[IdxOne][1]=&quot;#find_bnb.city[IdxOne]#&quot;>
<CFSET Session.QueryResults[IdxOne][IdxTwo]=&quot;#find_bnb.state[IdxOne]#&quot;>

In particular the IdxOne thing what does this mean?
Easy to make this 3d or able to store a path to an image?

Thanks for the help this was huge for me!

 

[calista]

Actually, I realized I was making this harder than it needed to be. (I have a tendency to do that, sometimes.) Here is a simpler version of getting your query results into the arrray:

<CFLOOP INDEX=&quot;IdxOne&quot; FROM=&quot;1&quot; TO=&quot;#Session.Counter#&quot;>
	<CFSET Session.QueryResults[IdxOne][1]=&quot;#GetPeople.PersonLastName[IdxOne]#&quot;>
	<CFSET Session.QueryResults[IdxOne][2]=&quot;#GetPeople.PersonFirstName[IdxOne]#&quot;>
	<CFSET Session.QueryResults[IdxOne][3]=&quot;#GetPeople.PersonEmail[IdxOne]#&quot;>
</CFLOOP>

As you can see, this can be expanded to as many fields as you need. &quot;IdxOne&quot; is just a counter variable. You can call it anything you want when you set up your loop. I always have to keep a note in front of me to remind me that the first dimension (in this case, IdxOne&quot is the row, and the second dimension is the column. Just FYI, you can pass the coordinates of a given element of the array as URL variables if you need to use that element on a subsequent page. Calista :-X
Jedi Knight,
Champion of the Force

 

[DeZiner]

Calista,
Thank you for the second post! Made a big difference in the noggin!
The 3rd dimmension does not work for me, nor does setting all the variables with one loop tag. I tried your above code and had to revert back to your original post. I have worked around the 3rd dimmension but have added an extra query, Hoping it doesn't kill my load time.

Thanks for the help!

 

[strantheman]

hi DeZiner and calista

If I understand your needs D, you don't need an array. Although query results in session parameters may eat more memory, time will be saved generating and looping.

See if this works for you:

<cfquery name=&quot;session.findIt&quot; datasource=&quot;myDB&quot;>
SELECT city,state
FROM myTable
WHERE state='#form.state#' or city='#form.search#' or zip='#form.search#'
GROUP BY city, state
ORDER BY city
</cfquery>
<cflock TIMEOUT=&quot;30&quot; NAME=&quot;#Session.SessionID#&quot;>
<cfset session.query_results=session.findIt>
</cflock>
<!---***Later in the page--->

<cfoutput query=&quot;session.findIt&quot;>
<li><a a href=&quot;

http://www.muURL.com/cozy/show_me.cfm?city=#city#&quot;;>#city#</a></li>

</cfoutput>

Depending on the sessionTimeOut you give in the CFAPPLICATION tag, the query will be accessible on other pages. Use parameterExists(session.findit) to determine if the query results still exist in memory.

Mmmmm I do love arrays, but calista is this more to what he needs?

hope this helps

 

[DeZiner]

This is what I initially tried and with the query named with and with out the session. part

Calista had the idea because the array could store the query results as a list to ptint out a list of cities to the page.

Thanks though!