1 Reply Latest reply on Jan 16, 2007 9:29 PM by kidvolt

    Random?  It nearly killed him!

      Once upon a time when I ran CF ver. 5 and 6, this code worked just fine:

      <CFQUERY Name="GetBlogUser" Datasource="#session.DSN1#" Maxrows="1" >
      SELECT *
      FROM BlogTable
      ORDER BY rnd(BlogAuthor)

      I'd get a random row returned every time. Life was good.

      Once I moved to CF 7 it no longer worked. It instead returned the very same row each time. A cache problem they would tell me so I'd empty the cache and sure enough the next time it actually would be a different order. Next time however it was stuck on the new order.

      Recently I have moved to a new server and upgraded my miserable Access table to MySQL. Now, that same code gives me the following:

      ------------------------------------------------------------------------------------------ -------------------------------

      General error: FUNCTION kidvoltage.rnd does not exist

      The error occurred in C:\Websites\106624de3\TSDRoadRally\index.cfm: line 50

      48 :
      49 :
      50 : <CFQUERY Name="GetEvents" Datasource="#session.DSN1#" MAXROWS="1" cachedwithin="#CreateTimeSpan(0, 0, 0, 0)#">
      51 : SELECT *
      52 : FROM Events

      SQL SELECT * FROM Events WHERE (EventDate >= {d '2007-01-16'} AND EventDate <= {d '2007-01-26'}) ORDER BY rnd(EventDate)
      SQLSTATE S1000

      ----------------------------------------------------------------------------------------- ------------------------------------

      So what is the solution that will both satisfy my desire for a random return upon each query as well as satisfy the MySQL beast?