Let's get jiggy with it and get even simpler... ONE database call:
<CFQUERY name="get_vids2" datasource="#datasource#">
select TOP 1
vow_up_lgnail_file,
vidwee_id,
vow_title
from
vidweek
order by
newid() ASC
</CFQUERY>
This leverages the *randomness* of MS SQL's UUID creator - newid() - and TOP
1.
Not sure if this works on anything but MS SQL server.
.......................
Ben Nadel
www.bennadel.com
-----Original Message-----
From: Everett, Al (NIH/NIGMS) [C] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 23, 2006 12:47 PM
To: CF-Talk
Subject: RE: CFQUERY of non-swequential primary key ids
How very inefficient. How about something like this:
<cfquery name="get_vid_ids" datasource="#datasource#"> SELECT vidwee_id FROM
vidweek </cfquery>
<cfset idList=valueList(get_vid_ids.vidwee_id)>
<cfset idPosition=randRange(1,listLen(idList),"SHA1PRNG")>
<cfset VAL_ID=listGetAt(idList,idPosition)
<CFQUERY name="get_vids2" datasource="#datasource#">
select vow_up_lgnail_file,vidwee_id,vow_title
from vidweek
where vidwee_id = #VAL_ID#
</CFQUERY>
<CFSET lgthbimg = "#get_vids2.vow_up_lgnail_file#">
There is also a function on CFLIB to randomly select rows from a database
table:
http://www.cflib.org/udf.cfm?id=524
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 23, 2006 12:14 PM
To: CF-Talk
Subject: CFQUERY of non-swequential primary key ids
All,
I'm doing a query and randomly displaying images from within each record but
records have been removed so I don't have sequential id values.
PROBLEM:
When I run this code sometime I get images, sometimes I get nothing.
Should I be running a different random'izer? 8-) Any ideas?
CODE:
<!--- GET 2ND VALUE TO BE PASSED TO RANDRANGE --->
<CFQUERY name="get_vids_a" datasource="#datasource#" maxrows="1">
select *
from vidweek
order by vidwee_id desc
</CFQUERY>
<!--- GET 1ST VALUE TO BE PASSED TO RANDRANGE ---> <CFQUERY
name="get_vids_b" datasource="#datasource#" maxrows="1">
select *
from vidweek
order by vidwee_id asc
</CFQUERY>
<cfset VAL_ID = #RandRange(get_vids_b.vidwee_id, get_vids_a.vidwee_id,
"SHA1PRNG")#>
<!--- USE THAT ID VALUE TO SET IMAGE NAME --->
<CFQUERY name="get_vids2" datasource="#datasource#">
select vow_up_lgnail_file,vidwee_id,vow_title
from vidweek
where vidwee_id = #VAL_ID#
</CFQUERY>
<CFSET lgthbimg = "#get_vids2.vow_up_lgnail_file#">
<img src="VideoOfTheWeek/home/<cfoutput>#lgthbimg#</cfoutput>"
width="262" height="231" border="0">
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:250756
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
