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">
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.

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

Page 1 of 1