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>
<!--- 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:
CODE
SELECT url, image
FROM Banners
You would need to set the total number of banners like so:
CODE
<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:
CODE
<!--- 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.
Random Db Records, Display random records from a database
20 Dec, 2004 - 01:59 PM
| 