Randomly select players, but with exclusions

[cdoyle]

Hi,

 

I'm trying to think how to accomplish this, but not really sure where to begin.

 

I have my players table, that has all the players ID (id).  I also have a medical ward table, for those who are currently in the hospital and can't play (player_id).

 

What I would like to do is select random rows from the players table, but not include any players that are listed in the medical ward table.

 

I started out with this, and then I remembered the medical ward players shouldn't be selected and that's where I got stuck.

$getvictims = $db->execute("SELECT `id`, `username` FROM `players` 
                                           WHERE `City_ID`=? ORDER BY RAND() Limit 20", array($player->City_ID));

 

Would I somehow use 2 queries,  1 query to select the players not in the medical ward,  then another query to randomly select those from query 1?

 

 

 

[dropfaith]

<?php
$getvictims = $db->execute("SELECT * FROM `players` 
                                           WHERE `City_ID`=?  and WHERE 'medicalward' != 'FALSE'  ORDER BY RAND() Limit 20", array($player->City_ID));
?>

 

I ASSUME the field value medical ward is named such and has a setting of true or false

 

not sure that will work or not but its my best off hand attempt

 

 

[cdoyle]

<?php
$getvictims = $db->execute("SELECT * FROM `players` 
                                           WHERE `City_ID`=?  and WHERE 'medicalward' != 'FALSE'  ORDER BY RAND() Limit 20", array($player->City_ID));
?>

 

I ASSUME the field value medical ward is named such and has a setting of true or false

 

not sure that will work or not but its my best off hand attempt

 

 

 

Thanks for replying,

 

I'm not sure what you mean by a setting true or false?

I have 2 different tables,

`players` and `medical_ward`

 

 

If a player is dead,  their `id` from the `players` table, is listed in the `player_id` field in the `medical_ward` table.

 

 

[dropfaith]

oh my bad the two tables thing eluded me i was going from one table with a medical ward field

 

your problem needs some sort of join  which im not too clear on the workings of yet (still kinda noobish)

[fenway]

yes, you need a left join:

select * 
from players as p 
left join medicalward as m on ( m.player_id = p.id )
where p.City_ID=? and m.player_id is null
ORDER BY RAND() Limit 20

[cdoyle]

yes, you need a left join:

select * 
from players as p 
left join medicalward as m on ( m.player_id = p.id )
where p.City_ID=? and m.player_id is null
ORDER BY RAND() Limit 20

 

thank you, that worked!

 

I have another question related to this.

Not really sure what to search for,  but right now the query I have always returns 20 rows from the players table.

Is there a way to make it so the number of rows it selects is dynamic?

 

Meaning each time it runs, the total number of rows is different?

For example,  if I run it the first time it select say 15 rows.  Then I run it again, it select 40 rows,  the next time 8 rows.

 

Not really sure what to search for on that.

[fenway]

You can use a random number generated from php....