Simple recursion unique raffle

Updated Szabi Zsoldos 0 920 Views

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);
	}
}
?>

Dani AI

Generated

Good starting point, . The recursive pick-and-check idea is simple and works at low contention, and the suggestions from (add time entropy) and (load existing numbers into an array) are helpful trade-offs. Two things to call out so the solution holds up under real load: race conditions and efficiency as the pool fills.

Make uniqueness a database guarantee (unique index on raffle_number) and stop trusting application-side existence checks alone. With a unique constraint, an atomic insert will either succeed or fail; catching the duplicate-key error and retrying is safe and simple and avoids lost/misassigned numbers under concurrency. An iterative retry loop is preferable to recursion to avoid stack growth on repeated collisions.

If collisions/performance are a concern, pick one of these patterns:

  • Use the DB unique index + retry loop (low memory, simple, robust under concurrency).
  • Pre-generate the full number set, shuffle it once, and pop numbers as entries are created (very fast O(1) assign; uses memory or a numbers table).
  • Maintain a numbers table and atomically flip an assigned flag with an UPDATE that checks assigned=0; on 0 affected rows, pick another candidate.

Practical tips: with a 100k space and ~50k assignments the expected tries per successful random draw is about 2 on average, so simple retries are fine; as occupancy approaches 90% the cost grows quickly. Use PDO exceptions (SQLSTATE 23000 for duplicates) or INSERT IGNORE / checking affected rows depending on the DB. Return the assigned raffle number from the function, avoid reconstructing the DB connection inside the method, and use cryptographic randomness only if unpredictability/fairness is required.

Member Avatar for minitauros
minitauros 151 Junior Poster

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).

Member Avatar for Szabi Zsoldos
Szabi Zsoldos 26 Learner and helper guy

That is true, my ideea was that I had limited players, almost 50k and I did a max of 100K.

Member Avatar for Member #120589
Member #120589

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.

Edited by Member #120589
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview