Random selection using offset with exclusions

[showman13]

OK, hopefully this question makes sense...

I have a select that I have been to randomly select a record within a table

The Table was simply a list of member IDs 'mem_id'
BUT
Now I've added a couple more fields to the table, one of which allows the member to OPT OUT of being PIFFED, and I need to have the random selection ignore those records.

Here is the current code: and to be honest I don't have a 100% grasp on how it works, I just know that it does

//  Random selection of mem_id based on random offset from beginning of table
//  Works very well...
$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM pif_list ");
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( " SELECT m.user FROM members AS m, pif_list AS p WHERE m.mem_id=p.mem_id LIMIT $offset, 1 " );
$random_result=mysql_fetch_array($result);
$random_pif = $random_result[0];
// $random_pif contains the username that was randomly selected

I need to have it ignore all records with a value of 'N' in the bene_select field that I added

The two lines that I think need to be changed are these:

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM pif_list WHERE bene_select!='N'");

$result = mysql_query( " SELECT m.user FROM members AS m, pif_list AS p WHERE m.mem_id=p.mem_id AND p.bene_select!='N' LIMIT $offset, 1 " );

Just wondering if this makes sense to those that have greater knowledge of selects than I do.?

thanks in advance.

Douglas

[diafol]

I suppose a simple solution would be:

"SELECT ... FROM table WHERE piffed = 'Y' ORDER BY RAND() LIMIT 0,1"  

However this is quite slow. Other solutions may involve more than one query.

[showman13]

Thank you diafol for your response.

It appears (with the limited data set I currently have to test it on) that the proposed changes I was asking about actually worked.

Hopefully it will do the same when I hit it with 100K + records to work on.

going to mark this one as solved.

Thanks again
Douglas