Professional Web Applications Themes

Query + Random Order + Multiple pages = ??? - Coldfusion - Advanced Techniques

I'm building a contest site with CFMX and MySQL. On it is a thumbnail view page that displays all the approved photos in a random order every time you enter a particular page. Here's the SQL code I'm using for this call: <cfquery name="qFindPhotos" datasource="dsn"> SELECT display_photo FROM contest WHERE approved='yes' ORDER BY RAND(); </cfquery> I'm now running into an instance where there are a lot of images to display; too many for one page and I need to split it up among several pages. The problem is the ORDER BY RAND(); - this is random each time the page ...

  1. #1

    Default Query + Random Order + Multiple pages = ???

    I'm building a contest site with CFMX and MySQL. On it is a thumbnail view page
    that displays all the approved photos in a random order every time you enter a
    particular page. Here's the SQL code I'm using for this call:

    <cfquery name="qFindPhotos" datasource="dsn">
    SELECT display_photo FROM contest WHERE approved='yes' ORDER BY RAND();
    </cfquery>

    I'm now running into an instance where there are a lot of images to display;
    too many for one page and I need to split it up among several pages.

    The problem is the ORDER BY RAND(); - this is random each time the page loads,
    so I can't do a simple ORDER BY photo_name DESC or something like that and have
    it work properly along several pages.

    Should I put the results of the CFQUERY into a temporary list and use that as
    my ORDER BY "statement"?

    Any thoughts on this is greatly appreciated.

    Cheers!

    etman Guest

  2. #2

    Default Re: Query + Random Order + Multiple pages = ???

    Use a query of query.

    <cfquery name="qFindPhotos" datasource="dsn">
    SELECT display_photo FROM contest WHERE approved='yes' order by rand();
    </cfquery>

    <cfquery dbtype="query" name="queryofquery">
    select * from qFindPhotos
    </cfquery>

    So when you cfoutput the result of queryofquery it will not re-order the
    result set as you page around it.

    Scott*e Guest

  3. #3

    Default Re: Query + Random Order + Multiple pages = ???

    So then all my subsequent calls would be like this (based on your new cfquery
    name):

    <cfoutput>#queryofquery.photo_name#</cfoutput> etc.

    Nice and simple.

    Thanks, I'll give that a try.

    Cheers!

    etman Guest

  4. #4

    Default Re: Query + Random Order + Multiple pages = ???

    One of the things that comes to mind is cachedwithin, then the query is
    cached for the specified amount of time.
    HTH
    --
    Tim Carley
    www.recfusion.com
    com
    Mountain Guest

  5. #5

    Default Re: Query + Random Order + Multiple pages = ???

    I've tried both a query of a query and cachedwithin; together they get me
    closer, but it's still not quite right. Each time someone new comes to the page
    it needs to be a brand new query with a unique display order for that one
    particular query. However when I try it on several browsers at the same time
    the query does get cached, but it's the exact same query for every instance of
    the page. Plus, if someone is going page by page and the original query times
    out, then the order that the photos are displayed will be displayed
    incorrectly, or worse, a big ugly CF error gets thrown.

    What I think I need to do is somehow save the query as maybe a session
    variable. There won't be a lot of people viewing at the exact same time so
    there won't be a lot of sessions active to maintain. Then once this session
    variable has a value (ie the results from a query) I cycle through that as if I
    were querying a db.

    Any thoughts/hints how to do this? To save the exact results of a particular
    query in memory - keeping in mind that the results will just be a list of
    numbers that can be comma-separated.

    Thanks!

    etman Guest

  6. #6

    Default Re: Query + Random Order + Multiple pages = ???

    I've just been doing a bit of testing with Arrays and Lists - I can easily
    convert a query into a list and store that as a session element; but can I then
    use that list as the base of a query so that my multiple display page calls can
    be easily performed?

    Thanks!

    etman Guest

Similar Threads

  1. random order results from MS access database
    By bawaite in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 8th, 12:40 PM
  2. Setting the Tabbing Order for Multiple Pages
    By Darren_Gant@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 6
    Last Post: April 27th, 03:37 AM
  3. Bad Page order when inserting multiple Pages
    By dmoshea@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 0
    Last Post: April 7th, 05:30 PM
  4. Presenting a list in random order
    By northerncaller webforumsuser@macromedia.com in forum HTML & CSS
    Replies: 2
    Last Post: July 29th, 02:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139