Help with stopping a loop..and more :)

[Newsgroup_User]

I have a web site called MyNextPet.org. I want to have a featured pet on the
home page that is Random
I am attempting to write a function that does this:

Selects the total pets in the database ('Pets') and generate a RecordCount
Create a variable that is random from 1 to the RecordCount of 'Pets'
Attempt to select information about that pet based on the generated number
(that must match the auto incremented 'pettag' number)
If the number does not match any of the pets, loop through an do it again
If the number does match, stop and output the information selected

I winged this function so I am sure I did something wrong and the fact that
I am posting it proves that I did. I am a little (or a lot) confused on how
to break out of a loop if the condition is met.
Help!

Code I have:
<cffunction name="GetFeaturedPet" access="public" returntype="query">
<cfquery name="Pets" datasource="#Request.MainDSN#">
SELECT * FROM pets
</cfquery>
<cfloop>
<cfset featured = RandRange(1,#Pets.RecordCount#)>
<cfquery name="GetFeatured" datasource="#Request.MainDSN#">
SELECT P.name, p.age, p.gender, p.breed, R.org
FROM pets P LEFT OUTER JOIN rescues R
ON p.username = R.username
WHERE pettag = #featured# AND active = 1
</cfquery>
<cfif #GetFeatured.RecordCount# EQ 0>
</cfloop>
</cfif>
<cfelse>
<cfabort>
<cfreturn GetFeatured>
</cffunction>
--
Wally Kolcz
Developer / Support

[Dan_Bracuk]

You don't need a loop and you only need one trip to the database.

Start by running your 2nd query with two modifications.
1. put pettag in the select clause.
2. take pettag out of the where clause.

Then do that randrange thing on this query.
Then use listgetat to get the petttag you want.
Then do a query of queries to get your final answer.

[Newsgroup_User]

Thank you for the things to look into.
I am a little confused with the QofQ.
I just want one random record from the database to display on the front page
of the web site.
I just need to make sure that both the record number exists (unless the pet
was deleted) and that the 'active =1' (if the pet hasn't either been adopted
or destroyed)

[Dan_Bracuk]

If you start with the 2nd query, slightly modified of course, you have already determined that the record exists and that active = 1.

[azadisaryev]

Try this modified function. The first query will pull all active pets from your db. The second QoQ ("getPet") will pull one random pet from the first query.
<cffunction name="GetFeaturedPet" access="public" returntype="query">
<cfquery name="GetFeatured" datasource="#Request.MainDSN#">
SELECT p.pettag, P.name, p.age, p.gender, p.breed, R.org
FROM pets P LEFT OUTER JOIN rescues R
ON p.username = R.username
WHERE active = 1;
</cfquery>
<cfset featured = RandRange(1,#GetFeatured.RecordCount#)>
<cfquery name="getPet" dbtype="query">
SELECT * FROM GetFeatured WHERE pettag = #featured#
</cfquery>
<cfreturn getPet>
</cffunction>