4 Replies Latest reply on Nov 26, 2006 11:07 PM by azadisaryev

    Help with stopping a loop..and more :)

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


        • 1. Re: Help with stopping a loop..and more :)
          Dan Bracuk Level 5
          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.
          • 2. Re: Help with stopping a loop..and more :)
            Level 7
            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)


            • 3. Re: Help with stopping a loop..and more :)
              Dan Bracuk Level 5
              If you start with the 2nd query, slightly modified of course, you have already determined that the record exists and that active = 1.
              • 4. Re: Help with stopping a loop..and more :)
                azadisaryev Level 1
                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>