Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2008
    Posts
    14

    Unanswered: Read data from a MySQL DB un-randomly

    Hi,

    I am a new member here, looking for a solution to a problem I have. I
    am building a PHP web app, and am looking for a means to get data
    from
    a mysql DB in a not random manner (sequentially or however else).
    Think of it as a Quote of the Day feature, with all quotes in the DB
    having equal chances of being shown. It could be a random pick, but
    in
    a way that guarantees fairness. I have thought of just getting the
    data sequentially but I am not sure its the best way, and how best to
    implement this.


    Please Assist.


    Grav

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd have a look at the rand function
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2008
    Posts
    14
    Quote Originally Posted by healdem
    I'd have a look at the rand function
    Healdem,

    I want all the entries in the DB to have an equal chance of being shown, whether sequentially or any other way. So how would rand work?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does this air-code work?
    Code:
    SELECT *
    FROM   quotations
    ORDER
        BY Rand()
    LIMIT 1;
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2008
    Posts
    14
    Quote Originally Posted by georgev
    Does this air-code work?
    Code:
    SELECT *
    FROM   quotations
    ORDER
        BY Rand()
    LIMIT 1;
    Not really, its not fair (some entries get selected multiple times before others are) which is not really what I want..

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so where are your multiple entries coming from. the rand function can be used to select random records. Ive used this technique to display random images.. Ive yet to see duplicate images, admittedly I can't guarantee thee are not duplicates but Ive yet to see them

    is the reason you are seeing duplicate because of your table design is it your data, is it becuase the query needs refining

    have you read the references and examples that can be found on the web

    what is your table definition, where are you seeing multiple rows.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2008
    Posts
    14
    Quote Originally Posted by healdem
    so where are your multiple entries coming from. the rand function can be used to select random records. Ive used this technique to display random images.. Ive yet to see duplicate images, admittedly I can't guarantee thee are not duplicates but Ive yet to see them

    is the reason you are seeing duplicate because of your table design is it your data, is it becuase the query needs refining

    have you read the references and examples that can be found on the web

    what is your table definition, where are you seeing multiple rows.
    You misunderstood me healdem, I am not getting multiple rows but if I run the query 3 times I may get the same result 3 times (since its random), which is not what I want. I want an equal distribution.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ive not seen that either
    as said before Ive used rand to select random images and I have never seen the same images repeating, occasionally you get a duplicate but thats part of the random nature. rand doesn't guarantee you will NEVER get duplicates... it doenst' guarantee that all items will be selected.. however the probability is that you will get all items selected over time

    are you using the function as documented on the MySQL website and elsewhere
    what is the SQL you are using
    are you setting a seed value for the rand function
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    occasionally you get a duplicate but thats part of the random nature. rand doesn't guarantee you will NEVER get duplicates...
    but that's what he wants -- he wants all of them, randomly, but never the same one twice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in which case he needs to maintain a list of items that have been selected (or not selected as the case may be) and then randomly select from that list.


    and thats before we start getting involved on how random a computer random number is
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What about this for a solution

    Pseudo code
    Code:
    Parameters: @current_id
    
    Variable: @new_id
    
    SELECT @new_id = Min(id)
    FROM   quotations
    WHERE  id > @current_id
    
    IF @new_id IS NULL THEN
      SELECT @new_id  = Min(id)
      FROM   quotations
    END IF
    
    RETURN @new_id
    Ultimately it's just cycling through the records based on an id (easily could be a different field)...
    George
    Home | Blog

Posting Permissions

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