See the following:
Pulling Random Records from a Database:
There are occasions when you need to pull random records from a
database--for example, you might have a database of articles from which you
want to display one at random. For the sake of discussion, let's say your
Articles table contains the following fields:
ArticleID, Author, and Content. ArticleID is a unique numerical value, while
Author and Content are both Text fields.
If ArticleID contains sequential numbers, you can simply pull the last
record, get the ArticleID, generate a random number from 1 to that
ArticleID, then pull the record associated with that random ArticleID
number.
Of course, this method will work only if there are no gaps in the numeric
values--if your random number is one that doesn't exist in the database,
your query will return a blank result or throw an error. To get around this
potential problem, however, you can use some of ColdFusion's list
functionality. Let's take a look:
First, query the database and retrieve all the ArticleID values:
<cfquery name="getids">
SELECT articleid FROM articles
</cfquery>
Next, build a list of the values returned and determine the number of items
in the list. Use the RandRage() function to choose the position in the list
from which to pick an ArticleID, and set that value as a variable:
<CFSET List = ValueList(GetIDs.ArticleID)>
<CFSET Position = RandRange(1, ListLen(List))>
<CFSET RandomArticleID = ListGetAt(List, Position)>
Finally, use the randomly selected ArticleID to query the database for that
record:
<CFQUERY name="GetArticle">
SELECT Author, Content FROM Articles
WHERE ArticleID = #RandomArticleID#
</cfquery>
There you have it--a simple way to retrieve a random record from the
database.
I hope the above helps, Gary Groomer
----- Original Message -----
From: "Alii Design" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, June 06, 2001 8:07 AM
Subject: Random records?
> How do i display a random record from a query?
>
> Rich
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
