Dream.In.Code > Random Db Records

supernova333

20 Dec, 2004 - 01: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.

CODE

<cfquery datasource="DATASOURCE_HERE" name="getItems">
SQL_QUERY_HERE
</cfquery>


<cfif getItems.recordCount>

<cfset showNum = 10>

<cfif showNum gt getItems.recordCount>
<cfset showNum = getItems.recordCount>
</cfif>

<cfset itemList = "">
<cfloop from="1" to="#getItems.recordCount#" index="i">
<cfset itemList = ListAppend(itemList, i)>
</cfloop>

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

<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:

CODE

SELECT url, image
FROM Banners

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

CODE

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:

CODE

<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

18 Nov, 2005 - 05:48 PM

lifesaver man... sheer lifesaver

UnholyStar

24 Jan, 2007 - 12: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.