Charles asks a pretty common question about randomizing the result of a query:
As I said above, this is a fairly common question. The best solution is to randomize the results at the database level. There are various ways to do that depending on your database type. But if you can't do that, and want to do it in CFML, it is fairly straightforward. Let's begin by creating a fake query:I have a simple page that loops a couple queries and randomly issues the question and choices. I have the loop go from 1 to 10; however sense I do not want to have duplicate questions, I did a conditional that does not allow dups to be shown. In turn, instead of giving me my nice 1-10 quiz, I get random amount of questions. I completely understand why this is happening, but have trouble coming up with a solution.
<cfset data = queryNew("id,name")>
<cfloop index="x" from="1" to="10">
<cfset queryAddRow(data)>
<cfset querySetCell(data, "id", x)>
<cfset querySetCell(data, "name", "Name #x#")>
</cfloop>
Now that we have a query, how can we loop through it randomly? There are multiple solutions. Here is one way of doing it.
The first thing I'll do is generate a list of rows. If the query has 3 rows, than I want "1,2,3". If it has 4, then "1,2,3,4".
<!--- generate a list of rows --->
<cfset rowList = "">
<cfloop index="x" from="1" to="#data.recordCount#">
<cfset rowList = listAppend(rowList, x)>
</cfloop>
Why do I do this instead of using a simple valueList()? I'll get to that in a minute. Now that I have a list of rows, I want to randomize the list.
<!--- now randomize --->
<cfset rList = "">
<cfloop condition="listLen(rowList)">
<!--- pick a random position --->
<cfset pos = randRange(1, listLen(rowList))>
<!--- get the row --->
<cfset row = listGetAt(rowList, pos)>
<!--- add to rList --->
<cfset rList = listAppend(rList, row)>
<!--- remove from rowList --->
<cfset rowList = listDeleteAt(rowlist, pos)>
</cfloop>
This code loops over the original rowList. In each iteration, I grab a random value, add it to rList, and remove it from rowList. Once down, rList is a randomized version of rowList. (rowList is destroyed, but I don't need it anymore after this.)
Now I can use the rList list like so:
<cfloop index="row" list="#rList#">
<cfoutput>Name: #data.name[row]#<br /></cfoutput>
</cfloop>
Do you see now why I got a list of rows, not a list of values? Imagine I had gotten a list of primary key values using valueList. In order to display the right data, I'd have to find the row for that primary key. I could do that with a query of query, or a listFind. That would work, but I felt this was a simpler solution. The complete template is listed below.
<cfset data = queryNew("id,name")>
<cfloop index="x" from="1" to="10">
<cfset queryAddRow(data)>
<cfset querySetCell(data, "id", x)>
<cfset querySetCell(data, "name", "Name #x#")>
</cfloop>
<!--- generate a list of rows --->
<cfset rowList = "">
<cfloop index="x" from="1" to="#data.recordCount#">
<cfset rowList = listAppend(rowList, x)>
</cfloop>
<!--- now randomize --->
<cfset rList = "">
<cfloop condition="listLen(rowList)">
<!--- pick a random position --->
<cfset pos = randRange(1, listLen(rowList))>
<!--- get the row --->
<cfset row = listGetAt(rowList, pos)>
<!--- add to rList --->
<cfset rList = listAppend(rList, row)>
<!--- remove from rowList --->
<cfset rowList = listDeleteAt(rowlist, pos)>
</cfloop>
<cfloop index="row" list="#rList#">
<cfoutput>Name: #data.name[row]#<br /></cfoutput>
</cfloop>