Random Query

[KeyserSoze1877]

I have a table of 70+ records of picture information. A gallery website I have I only want to display 15 pictures at once.
How do I select all 70+ records and randomly choose 15 out of the entire recordset? So each time the page is viewed, the gallery changes?

 

[CFHub]

Which database? For Access you have to do the work in CF, but some (MSSQL/Oracle) have built in Rand() functions.

For Access 2000:

http://cfhub.com/forum/index.cfm?FuseAction=Thread&TopicID=1163

For MS SQL something like this may work for you.

SELECT TOP 5 item_id, newid() as new_id
FROM base_table
ORDER BY new_id

 

[mikeshort]

Here's a way that will work in CF 5:

<!--- Select some stuff to display randomly
      ORDER BY is just there to prove it works 
 --->

<cfquery name=&quot;test&quot; datasource=&quot;#Variables.Datasource#&quot;>
   SELECT LAST_NAME
   FROM PERSON
   WHERE SSN like ('000%')
   ORDER BY LAST_NAME
</cfquery>

<!--- Create an array of random numbers, one for
      each record in the record set --->

<cfset aRanArray = ArrayNew(1)>
<cfloop from=&quot;1&quot; to='#test.RecordCount#' index=&quot;i&quot;>
	<cfset aRanArray[i] = Rand()>
</cfloop>

<!--- Add the random numbers as a column to the query --->

<cfset temp = QueryAddColumn(test, &quot;Rannum&quot;, aRanArray)>

<!--- Query from the query, ordering by the random number 
--->

<cfquery name=&quot;testran&quot; dbtype=&quot;query&quot;>
	SELECT LAST_NAME, RANNUM
	FROM test
	ORDER BY RANNUM
</cfquery>

<!--- Do what you need to do to display the first 15 --->

<cfloop startrow=&quot;1&quot; endrow=&quot;15&quot; query=&quot;testran&quot;>
   <!--- do your thing --->
</cfloop>

Hope this helps.

Mike

 

[tleish]

You could do something like:

=== START CODE EXAMPLE ===
<CFQUERY NAME=&quot;qImages&quot; DATASOURCE=&quot;mydaatasource&quot;>
SELECT imgPath
FROM RecCenter
</CFQUERY>

[COLOR=666666]<!--- Set Looping Variables --->[/color]
<CFSET randList = &quot;&quot;>
<CFSET c = 1>

[COLOR=666666]<!--- Loop until we have 15 items --->[/color]
<CFLOOP CONDITION=&quot;True&quot;>

<CFSET r = RandRange(1, qImages.RecordCount)>
[COLOR=666666]<!--- If random number isn't in the list --->[/color]
<CFIF NOT ListContains(VARIABLES.randList, VARIABLES.r)>
<CFOUTPUT>
[COLOR=000080]<IMG SRC=&quot;#qImages.imgPath

[

r

]

#&quot;>[/color][COLOR=000080]<BR>[/color]
</CFOUTPUT>
[COLOR=666666]<!--- Increment the count --->[/color]
<CFSET c = c + 1>

[COLOR=666666]<!--- Add the random number to the list --->[/color]
<CFSET randList=ListAppend(VARIABLES.randList, VARIABLES.r)>

[COLOR=666666]<!--- Exit loop at 15 items --->[/color]
<CFIF c GT 15>
<CFABORT>
</CFIF>
</CFIF>

</CFLOOP>
=== END CODE EXAMPLE === - tleish