#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,576
    Rep Power
    171

    Random order every 2 hours?


    Hi;

    I need to make the query results to be sorted randomly every 2 hours. For example for now until next 2 hours show one set of results (any set of ids,, for example 1,5,7,2,9,...), after 2 hours show another set of results (11, 64 ,22,9236).
    How can I do that?
    Code:
     SELECT hotelname,
           allotments_new.minimum_number_of_nights AS mn,
           destinationname,
           day_rate,
           packages.type                           AS PT,
           packages.id                             AS PID,
           allotments_new.status                   AS ***,
           hotels.hotel_id                         AS HID,
           hotel_photos.photo
    FROM   allotments_new
           INNER JOIN hotels
                   ON hotels.hotel_id = allotments_new.hotel_id
                      AND hotels.status = 'active'
           INNER JOIN packages
                   ON allotments_new.package_id = packages.id
                      AND packages.status = 'active'
           INNER JOIN destinations
                   ON hotels.destination_id = destinations.destination_id
           INNER JOIN hotel_photos
                   ON hotel_photos.hotel_id = hotels.hotel_id
    WHERE  day_date = '2013-05-22'
           AND number_of_rooms > 0
           AND allotments_new.status = 'b'
    GROUP  BY allotments_new.hotel_id
    ORDER  BY package_order
    LIMIT  8
    Thanks
  2. #2
  3. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,754
    Rep Power
    9397
    If you do the randomness in SQL with an ORDER BY RAND() then you can see the RNG yourself: generate a random number in PHP and feed that to RAND() like
    Code:
    ...ORDER BY RAND(12345)
    That will return the same results every time for the same seed number.

    As for getting that number you can use YYMMDDHH, or some other combination of those digits, or more RNG seeding. Or a cache if it's hit frequently enough.
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,576
    Rep Power
    171
    Thanks requinix

    Now there is a new thing (related) I want to learn and I am not sure whats the best approach! I want to make sure all the results get seen. Like a loop. So at first, show 8 out of total. Then show the second 8, next hour show the 3rd eight and so on and so far.

    Any suggesitons on that?

    Thanks
  6. #4
  7. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,754
    Rep Power
    9397
    Then I'd say that, while possible, you shouldn't do it inline.

    Have a cronjob running every two hours (=however often the results change). Inside it inspects a table that's dedicated to this particular feature of the site:
    1. If the table has <=16 (=2 * number per page) rows then do an INSERT... SELECT with a brand new set of randomized results. Don't need to do any seeding - just a regular SELECT... ORDER BY RAND().
    2. Regardless of that, delete the top 8 results.

    Then the script you have simply does a SELECT... LIMIT 8. The table should be just the bare minimums of course; you probably wouldn't need anything more than the IDs stored in it.

    The key point is making sure you have >=8 results in the table at all times. Even while the cronjob is in the middle of running, don't let the table ever go below 8 results.

IMN logo majestic logo threadwatch logo seochat tools logo