Raffle function to generate unique numbers for each player, related to the same table with a recursive aproach.
I had to do a simple raffle system for a contest that I was working on and tried many things and the most simple and the most effective one to generate unique numbers for each player was this, a simple recursive function.
<?php
public function createRaffleNumber($player) {
// Number that will be assigned to a player(unique)
$raffle_number = mt_rand(0,99999);
// We will select the random number that it was generated and check it's existence in the current table
$sql = "SELECT id FROM raffle.edition_1 as e1 WHERE e1.raffle_number = :raffle_number";
$query = $this->__construct()->prepare($sql);
$query->execute(array(
":raffle_number" => $raffle_number
));
$data = $query->fetchAll();
// If the number is 0, then it means that the random number that it was
// generated, it is free and we can assign it to the player
if(count($data) == 0) {
$sql = "INSERT INTO raffle.edition_1 (player, raffle_number) VALUES (:player,:raffle_number)";
$query = $this->__construct()->prepare($sql);
$query->execute(array(
":player" => $player,
":raffle_number" => $raffle_number
));
} else {
// If the number is occupied, we will go back and try a new number
// untill we find a free number that we can assign
$this->createRaffleNumber($player);
}
}
?>
Tip: If you use time() or microtime() in your random number somehow, you will generate a number that is much more likely to be unique, which means you may have to execute less database operations (less queries are needed to find out if a number already exists, for the number will probably always be unique).
That is true, my ideea was that I had limited players, almost 50k and I did a max of 100K.
Could you get all the raffle numbers and place them into an array an then check to see if the random number is in the array? Means you only ever run one SQL query.
Example...
function createRaffleNumber($db, $player, $start, $end) {
//start with getting random raffle number
$raffle_number = mt_rand($start,$end);
//select data
$sth = $db->prepare("SELECT raffle_number FROM edition1");
$sth->execute();
$data = $sth->fetchAll(PDO::FETCH_COLUMN, 0);
//check raffle not in db already - redraw if it is
while(in_array($raffle_number, $data)) $raffle_number = mt_rand($start,$end);
//insert raffle number to person
$sth = $db->prepare("INSERT INTO edition1 (member_id, raffle_number) VALUES (:mem,:raff)");
$sth->execute(array(':mem'=>$player,':raff'=>$raffle_number ));
//tell us if everything was ok
if($sth->rowCount()) return $raffle_number;
}
Not sure how fast it is compared to the loop sql queries though.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.