Page 1 of 1

Random Db Records Display random records from a database Rate Topic: -----

#1 supernova333  Icon User is offline

  • D.I.C Addict

Reputation: 4
  • View blog
  • Posts: 590
  • Joined: 12-March 02

Posted 20 December 2004 - 02:59 PM

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">
<!--- 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>
  <!--- make a list --->
  <cfset itemList = "">
  <cfloop from="1" to="#getItems.recordCount#" index="i">
    <cfset itemList = ListAppend(itemList, i)>
  <!--- 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)>
  <!--- display stuff here --->
  <cfloop from="1" to="#showNum#" index="i">
    <cfoutput>#getItems.COLUMN_NAME[ListGetAt(randomItems, i)]#</cfoutput><br>

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">
    <a href="#getItems.url[ListGetAt(randomItems, i)]#" target="_blank"><img src="/imagepath/#getItems.image[ListGetAt(randomItems, i)]#" border="0"></a>

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Random Db Records

#2 Solitarium  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 25-September 05

Posted 18 November 2005 - 06:48 PM

lifesaver man... sheer lifesaver :music:
Was This Post Helpful? 0
  • +
  • -

#3 Shinilolz  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 238
  • Joined: 30-November 06

Posted 24 January 2007 - 01:22 PM

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.
Was This Post Helpful? 0
  • +
  • -

#4 anniiiiiie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 17-June 11

Posted 17 June 2011 - 01:02 PM

In fact, you could do something even more simpler:

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

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

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!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1