Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Feb 2006
    Location
    Arizona, USA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Best way to select a random entry from a recordset?

    Hi, I'm going to be doing this little task in the next day or two, how would you do it? I know it's pretty simple, but I want to see how many ways it can be done and what might be the best.

    Basically, with PHP and MySQL, I am going to query the DB, and then select one of the records randomly. And then check that one of the fields in this random selection doesn't match a field in another table. If the fields match in the other table, then choose a different random selection. It's kinda like this:

    Select ID, Name from Table where F2 = 10

    Returns:
    22, john
    38, frank
    12, joe
    89, bob
    312, lucy


    Then I need to just pick one as randomly as possible. Say it picks 38, frank. Then I check a different table:

    Select ID from Table2 where name = frank

    If it returns 1, go back and choose a different record.

    How would you go about it? How best can I be sure it is random?

    Thanks

  • #2
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Use a query like this:

    Code:
    SELECT
     t1.ID
    ,t1.Name
    ,t2.ID
    FROM
     t1
    LEFT JOIN
     t2
    ON
     t1.ID = t2.ID
    WHERE
     t2.ID IS NULL
    ORDER BY
     RAND()
    LIMIT 1
    That will pull every record from t1 that doesn't have a match in t2. The ORDER BY RAND() will randomize the order the rows are returned, and the LIMIT will only send the first one.

  • #3
    New Coder
    Join Date
    Feb 2006
    Location
    Arizona, USA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey that's pretty sweet. I'm terrible at using joins.

    Let me throw something else in there. Lets say that table1 and table2 CAN have a match, but only of the date field is a year older then today.
    So like, if John gets picked, and John is in table2, it is ok to keep it, if table2 date is a year older then today. Or any date they pick.

    And also, how "random" is the rand function really? Can you pass a seed to it or what does it use?

    Thanks dude, looks tight!

  • #4
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    With the added date check, your query will look like:

    Code:
    SELECT
     t1.ID
    ,t1.Name
    ,t2.ID
    FROM
     t1
    LEFT JOIN
     t2
    ON
     t1.ID = t2.ID
    WHERE
     t2.ID IS NULL
    OR
     t2.datecol <= DATE_SUB(NOW(),INTERVAL 1 YEAR)
    ORDER BY
     RAND()
    LIMIT 1
    As for how random RAND() is, here's how the manual puts it:

    RAND() is not meant to be a perfect random generator, but instead is a fast way to generate ad hoc random numbers which is portable between platforms for the same MySQL version.
    So it's not as good as a script-side randomizer would be, but it should be random enough for your purposes, especially weighed against the extra overhead of other methods.

  • #5
    New Coder
    Join Date
    Feb 2006
    Location
    Arizona, USA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Alright, looks good dude. I will be implementing it within the next couple nights. I'll let you know how it works out.

    Also, can you PASS a seed to rand? I mean, like could you put RAND() * xx or something like that?
    In other words, if I could bring the randomness of user input then I would feel better. So like when the administrator goes to select the random person, it simply asks him for a 2 digit number which I pass as a seed.

    Otherwise, without wasting your time rewriting it, I suppose I could just as easy remove the random number and LIMIT and then just create an array and randomly pick one from the array using a more random generator function?

    Thanks again.

  • #6
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can seed RAND(): RAND(x).

    If you'd rather not use MySQL's RAND(), though, you could do the following, and I doubt there'll be much of a performance difference:

    PHP Code:
    $query "
    SELECT
     t1.ID
    ,t1.Name
    FROM
     t1
    LEFT JOIN
     t2
    ON
     t1.ID = t2.ID
    WHERE
     t2.ID IS NULL
    OR
     t2.datecol <= DATE_SUB(NOW(),INTERVAL 1 YEAR)"
    ;

    //retrieve all rows that fit criteria
    $result mysql_query($query) or die(mysql_error());

    //find out how many rows we've retrieved
    $rows mysql_num_rows($result);

    //use PHP's mt_rand() function to decide which row we want
    //(subtract one from row count because row numbering
    //within the resource starts with 0)
    $row_no mt_rand(0,$rows 1);

    //pull data from selected row
    $id   mysql_result($result,$row_no,0);
    $name mysql_result($result,$row_no,1); 

  • #7
    New Coder
    Join Date
    Feb 2006
    Location
    Arizona, USA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The 2nd way seems better for my needs. Because I'd like to "act" on ALL the possible entries.
    That is, for example, have a visible "counter" going through them like a spinning wheel until it picks the last one. Know what I mean? Instead of just an instant pop up "BAM, you picked john". Instead it puts on a show for a few seconds before selecting the final entry. Anyway, if you could help me fit your query into my tables, here are the details.

    Table1 is called dbtablepart
    Table2 is called dbtablecon

    I want to select ALL, not just 2 fields. And first and foremost I am selecting everything from dbtablepart where CID = 10 for example. So basically like this:

    SELECT * FROM dbtablepart WHERE CID = 10

    This is all I really need except for the check in the other table.
    So from this selection I need to EXCLUDE any rows where dbtablepart.UName EQUALS dbtablecon.Winacct not exclude if dbtablecon.Windate is more then a year old from today.

    That is, they are not joined on a field with the same name, they are not joined on ID fields.
    So IF dbtablepart.UName = dbtablecon.Winacct AND IF dbtablecon.Windate is within a year of today, to exclude that entry.

    Hope that makes sense. Can a join be done if the 2 columns don't have the same name?
    And if I might ask why, in your query, you say:
    ON
    t1.ID = t2.ID
    WHERE
    t2.ID IS NULL
    ---

    I don't get why it's t2.ID IS NULL. ID would never really be NULL, so what does that mean?

    Thanks a ton for the help so far. I'm just having trouble putting all my values here and there in the query.

    Edit:
    I might also add that the date field is DATE type and stored as yyyy-mm-dd. So not sure if that effects the date function you used.

  • #8
    New Coder
    Join Date
    Feb 2006
    Location
    Arizona, USA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hold it dude, before you waste any more time writing queries for me

    I built off yours again and it works! Here is the final PHP version using my vars in place:

    PHP Code:
    $query "SELECT $dbtablePart.* 
    FROM $dbtablePart 
    LEFT JOIN $dbtableCon 
    ON $dbtablePart.UName = $dbtableCon.Winacct 
    WHERE $dbtablePart.CID = $id 
    AND $dbtablePart.Passed <> 'n' 
    AND $dbtableCon.Winacct IS NULL 
    OR $dbtableCon.EndDate <= DATE_SUB(NOW(),INTERVAL 1 YEAR)"


  • #9
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, let's take it in pieces. First, like you said, we want everything in dbtablepart with a specific CID:

    Code:
    SELECT
    ,dp.field1
    ,dp.field2
    FROM
     dbtablepart AS dp
    WHERE
     dp.CID = 10
    Since you have joined any other tables, that 'dp' alias isn't necessary, but I wanted to go ahead and add it. You can also use the wildcard ('SELECT dp.* FROM...'), but I believe you should always specify exactly which columns you plan to use, mainly for clarity.

    So far, so good. Now let's make the check against the other table.

    Code:
    SELECT
    ,dp.field1
    ,dp.field2
    FROM
     dbtablepart AS dp
    LEFT JOIN
     dbtablecon AS dt
    ON
     dp.UName = dt.Winacct
    WHERE
     dp.CID = 10
    AND
     dt.ID IS NULL
    First, the IS NULL. This is how we're determining if there are any matching entries in dbtablecon. There are several different types of JOINs you can make between two tables. When you use a LEFT JOIN, you select every row in the left (or first) table. For the second table, you're only selecting the rows that match the ON conditional. So in this case, we're getting every row from dp, but only rows from dt that have a Winacct that matches and existing dp.UName. (Any columns can be used for an ON conditional, but note that integer fields are most efficient, and properly indexing the fields you plan to join on can speed up your query greatly.)

    However, every row in your result set will have the same number of fields. So for those rows in dp that do not have a match in dt, all the fields that would contain dt's values are set to NULL. Since we only want those unmatched rows, we can use that NULL as a check to weed out the others.

    Now we just need to add the date check, which works the same as I posted earlier, but we need to use some parentheses to make sure our different WHERE conditionals are evaluated correctly:

    Code:
    SELECT
    ,dp.field1
    ,dp.field2
    FROM
     dbtablepart AS dp
    LEFT JOIN
     dbtablecon AS dt
    ON
     dp.UName = dt.Winacct
    WHERE
     dp.CID = 10
    AND
     (
      dt.ID IS NULL 
     OR
      dt.Windate <= DATE_SUB(NOW(),INTERVAL 1 YEAR)"
     )
    The date functions used here will only work correctly with properly formatted MySQL DATE,DATETIME, etc. field types, so you'll be fine there.

    And that should set you up, query-wise. The best way to get the 'spinning wheel' effect will depend on exactly what you want to display for it.

    Edit: Heh.


  •  

    Posting Permissions

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