...

View Full Version : Best way to select a random entry from a recordset?



Vigilante
02-18-2006, 06:54 PM
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

Kid Charming
02-18-2006, 07:49 PM
Use a query like this:



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.

Vigilante
02-19-2006, 10:30 PM
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!

Kid Charming
02-19-2006, 11:12 PM
With the added date check, your query will look like:



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.

Vigilante
02-20-2006, 09:02 PM
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.

Kid Charming
02-20-2006, 09:40 PM
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:



$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);

Vigilante
02-24-2006, 02:38 AM
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.


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.

Vigilante
02-24-2006, 03:30 AM
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:



$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)";

Kid Charming
02-24-2006, 03:41 AM
Ok, let's take it in pieces. First, like you said, we want everything in dbtablepart with a specific CID:



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.



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:



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.

Heh.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum