Contest winners are supposed to be random, pulled from a hat so to speak. The process of doing that is a little less obvious when that hat is a MySQL database with 500,000 people in it. You can't just stick your hand in and grab the first peice of paper you feel.
Wait.... actually you can.
So I needed to select a random group of people from this MySQL table. It's super easy to randomize things in PHP so that was my first approach. First get a list of all the people that meet the criteria for selection, put them in an array, randomize the order of the array, and pull the winners from the top. Easy!
It goes something like this:
$number_of_winners = 3;
$sql = "SELECT id
FROM people
WHERE is_eligible = 'yes'
AND has_already_won = 'no'
";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result))
{
$users[] = $row['uid'];
}
shuffle($users);
$winners = array_slice($users,0,$number_of_winners);
//--> now update the table with the new winners
$sql = "UPDATE people
SET is_a_winner = 'yes'
WHERE uid IN('{$winners[0]}','{$winners[1]}','{$winners[2]}')
";
$result = mysql_query($sql);
That's was easy enough. But as it turns out there is an easier way. Using MySQL UPDATE we can randomize the rows we select and update them all in one tidy query.
//--> simplified version
$sql = "UPDATE people
SET is_a_winner = 'yes'
WHERE is_eligible = 'yes'
AND has_already_won = 'no'
ORDER BY RAND()
LIMIT $number_of_winners
";
$result = mysql_query($sql);
Now isn't that nice? It's all the same functionality with only one SQL query and less than half the code.
J Cornelius is a software developer, Web developer, and Formula 1 fan in Atlanta GA. He has a strange affinity for odd numbers, european sports cars, thoughtful analogies, and is hopelessly addicted to chips & salsa. Read more
Was it good for you?
Post to Digg Post to del.icio.us Post to ma.gnolia Post to Furl Post to Mixx