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

Member Avatar

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.

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