EasyCFM.COM ColdFusion Forums / Good Coding Tips! / Create a Random List

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Create a Random List

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

ktaisia
02-17-2007 @ 11:54 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 146
Joined: Jun 2005

If you look at the dates, you will find that I was having  an issue with lists today so when all has been sorted I would like to paste a snippet of what I learnt. It may not be the easiest, but it works

This Code gets the ID field in the table and creates a List
Then it creates another list of the ID numbers randomly picked
Then it retrieves the records from the database according to the IDs in the list.
You can specify how many records you want to retrieve.

<!---Set List Length for the number of records you wish to get randomly--->
<cfset listLength = 10>
<!---Do the Query for the ID field--->
<cfquery name="qGet" datasource="#request.dsn#">SELECT id FROM table</cfquery>
<!---Set 2 Arrays to to be used--->
<cfset myArray = ArrayNew(1)>
<cfset myArray2 = ArrayNew(1)>
<!---Convert them List format--->
<cfset myList = ArrayToList(myArray,",")>
<cfset myRanList = ArrayToList(myArray2,",")>
<!---Append ID values to list--->
<cfoutput query="qGet">
     <cfset myList = ListAppend(myList,"#qGet.id#")>
</cfoutput>

<!---Now Create a Randomized List--->
<cfoutput>
     <cfloop index="i" from="1" to="#listLength#">
          <cfset myRanCalc = ListGetAt(myList,RandRange(1,#qGet.RecordCount#),",")>
          <cfset myRanList = ListAppend(myRanList,myRanCalc,",")>
          <cfset i = i + 1>
     </cfloop>
</cfoutput>

<!---Run a query to retrieve the records--->
<cfquery name="qRanList" datasource="#request.dsn#">
     SELECT field-a,field-b FROM table WHERE id IN (#myRanList#)
</cfquery>

<!---Output the Recordslist:--->
<cfoutput query="qRanList">
     #field-a# - #field-b#<br />
</cfoutput>


Many Thanks to those who helped in the request I placed in the forum. Maybe you can tidy this up a bit more.

Cheers

kevsarg18
03-21-2007 @ 1:43 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 428
Joined: Jun 2004

not tested, but here is the same code, fixed up.


<!---Set List Length for the number of records you wish to get randomly--->
<cfset listLength = 10>
<!---Do the Query for the ID field--->
<cfquery name="qGet" datasource="#request.dsn#">SELECT id FROM table</cfquery>
<!---Set 2 Lists to to be used--->
<cfset myList = valueList(qGet.id)>
<cfset myRanList = "">

<!---Now Create a Randomized List--->
<cfloop index="i" from="1" to="#listLength#">
     <cfset myRanCalc = ListGetAt(myList,RandRange(1,qGet.RecordCount),",")>
     <cfset myRanList = ListAppend(myRanList,myRanCalc,",")>
</cfloop>

<!---Run a query to retrieve the records--->
<cfquery name="qRanList" datasource="#request.dsn#">
     SELECT field-a,field-b FROM table WHERE id IN (#myRanList#)
</cfquery>

<!---Output the Recordslist:--->
<cfoutput query="qRanList">
     #field-a# - #field-b#<br />
</cfoutput>


My CFML Scripts.

kevsarg18
03-21-2007 @ 1:44 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 428
Joined: Jun 2004

if you are using MYSQL, you can do it with just a query Smile


<cfquery name="qGet" datasource="#request.dsn#">
     SELECT id,field-a,field-b FROM table
     ORDER BY RAND()
     LIMIT #listLength#
</cfquery>


My CFML Scripts.

This message was edited by kevsarg18 on 3-21-07 @ 1:45 PM

CJ
03-26-2007 @ 5:16 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4262
Joined: Oct 2002

and if you are using SQL Server as well....

<cfquery name="qGet" datasource="#request.dsn#">
     SELECT TOP #listLength#
          id,
          field-a,
          field-b
     FROM
          table
     ORDER BY
          newID()
</cfquery>


:)

-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com

Teachers open the door. You enter by yourself.
—Chinese Proverb


Website Designed and Developed by Pablo Varando.