[supernova333]

This script will enable you to select a predetermined number of random records from a database without knowing how many total records are in the database and making sure not to display the same record twice. Here's the code, I'll explain a little more below.

<cfquery datasource="DATASOURCE_HERE" name="getItems">
SQL_QUERY_HERE
</cfquery>
<!--- make sure there are records available --->

<cfif getItems.recordCount>
  <!--- number of records you want to show --->
  <cfset showNum = 10>
  <!--- make sure we aren't trying to show
   more than what's in the DB --->
  <cfif showNum gt getItems.recordCount>
    <cfset showNum = getItems.recordCount>
  </cfif>
  <!--- make a list --->
  <cfset itemList = "">
  <cfloop from="1" to="#getItems.recordCount#" index="i">
    <cfset itemList = ListAppend(itemList, i)>
  </cfloop>
  <!--- randomize the list --->
  <cfset randomItems = "">
  <cfset itemCount = ListLen(itemList)>
  <cfloop from="1" to="#itemCount#" index="i">
    <cfset random = ListGetAt(itemList, RandRange(1, itemCount))>
    <cfset randomItems = ListAppend(randomItems, random)>
    <cfset itemList = ListDeleteAt(itemList, ListFind(itemList, random))>
    <cfset itemCount = ListLen(itemList)>
  </cfloop>
  <!--- display stuff here --->
  <cfloop from="1" to="#showNum#" index="i">
    <cfoutput>#getItems.COLUMN_NAME[ListGetAt(randomItems, i)]#</cfoutput><br>
  </cfloop>
</cfif>

All you need to do is plug in your info for DATASOURCE_HERE, SQL_QUERY_HERE, and COLUMN_NAME. Right now this will display up to 10 records, you can change that by changing the "showNum" variable.

Ok, say you want to display 5 banners from the banners table. Pretend this is your SQL query:

SELECT url, image
FROM Banners

You would need to set the total number of banners like so:

<cfset showNum = 5>

Assuming you want to display the banners in a column on the page, your loop under the "display stuff here" comment would look like this:

  <!--- display stuff here --->
  <cfloop from="1" to="#showNum#" index="i">
    <cfoutput>
    <a href="#getItems.url[ListGetAt(randomItems, i)]#" target="_blank"><img src="/imagepath/#getItems.image[ListGetAt(randomItems, i)]#" border="0"></a>
    </cfoutput>
    <br>
  </cfloop>

This is a really basic example; you could add a lot more to the loop like tracking queries, more formatting, etc.

Feel free to email me know if you have any questions.

[Solitarium]

lifesaver man... sheer lifesaver

[Shinilolz]

alright so its a year and 2 months old, i though it was a useful article. So Mr.Author if you ever visit this forum, this was a very helpful article, since i am beggining to learn Coldfusion.

[anniiiiiie]

In fact, you could do something even more simpler:

In your query, you specify your SELECT... FROM.. as usual, but, you add:

ORDER BY  NEWID();

If you want to want to display let's say just 3 random items, you simply say:

SELECT TOP 3 .. name of your fields as usual
FROM NameOfTable
WHERE whatever you want to specify
ORDER BY   NEWID();

And then to show in your page, you add the usual output, ex. if your query name is "GetProductName", you would have:

<output query="GetProductName">#ProductName#</output>

So it's simply:
1- add the sql function NEWID
2- add TOP yournumber if you want to specify a number
3- do your output as usual

Have a nice day!