Create a "Secret Santa" generator using MySQL and PHP

Question

I am trying to create a Secret Santa system using a PHP page and a MySQL database to store the details so if someone forgets their match they can re-request it.

Step 1: I created a random number generator based on the number of people in the list in the database.

Count Function:

$maxSQL = "SELECT COUNT(id) as total FROM secretsanta";
$maxRS = mysqli_query($conn, $maxSQL);
$maxQuery = mysqli_fetch_array($maxRS);
$maxpersons = $maxQuery['total'];

Then the Random Number Generator:

$assigned = rand(1,$maxpersons);

Step 2: Test if the random number matches the persons own id and regenerate a new number if true.

do { 
    $assigned = rand(1,$maxpersons);
} while ($assigned==$id);

Step 3: Write the paired id to the persons database record.

$assignSQL = "UPDATE secretsanta SET assigned = '".$assigned."' WHERE secretsanta.id = ".$id;
if (mysqli_query($conn, $assignSQL)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

The Problem: Now I need to check that other people aren't assigned to that person or otherwise some could miss out and others would get more than others.

I tried to implement a function that contained a query to test each record to see if that number already existed and was hoping to add it as a condition to perhaps a while or do while statement?

if (!function_exists('checkRandom')){
    function checkRandom($funcid){
        $Check_SQL = "SELECT assigned FROM secretsanta ORDER BY id ASC";
        $Check_RES = mysqli_query($conn, $Check_SQL);
        if (Check_RES) {
            while ($CheckArray = mysqli_fetch_array($Check_RES, MYSQLI_ASSOC)) {
                $CheckAsgn = $CheckArray['assigned'];
                if ($funcid==$CheckAsgn) {return true;}else{return false;}
            }
        }
    }
}

Then implement it into the do while statement like this:

do { 
    $assigned = rand(1,$maxpersons);
} while ($assigned==$id||checkRandom($assigned));

No luck so far...HELP!.. please :)

P.S. I know there are websites that already do this, I just don't trust them to give out mine and family email address' if I can make my own private version myself.

I want a more powerful laptop so I can answer your question faster :-) — Tim Biegeleisen, Sep 11 '17 at 11:37
Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either the [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) drivers. [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Sep 11 '17 at 14:04
Thanks Alex, that's really helpful information. I never knew that this was something I could do, thank you again, I will research this more. — Biscuits, Sep 19 '17 at 22:19

kscherrer · Answer 1 · 2017-09-11T13:15:14.797

Using your method, the first few assignments will be done with no problem, but imagine the last unassigned entry and how many times it will try a random number only to find the person with that id is already assigned..

I'm gonna give you another approach to your problem: for each user that you want to assign a santa to, make a new SELECT statement with a WHERE clause that lets you select only those users that are not assigned yet. check out my code and see if that helps you. I just typed this and didnt test it so there could be some mistakes.

// load all unassigned users into an array
$unassignedUsers = [];
$query = "SELECT id, assigned FROM secretsanta WHERE assigned is NULL";
$res = mysqli_query($conn, $query);
while($row = mysqli_fetch_assoc($res){
    $unassignedUsers[] = $row;
}

if(count($unassignedUsers) == 1){
    echo 'There is only 1 unassigned user. Therefore he cannot be matched';
} else {
    // for loop for each user in DB that is not assigned yet
    //for ($i = 1;$i <= count($unassignedUsers); $i++){
    $i = 0;
    foreach($unassignedUsers as $user)
        // if its the second-to-last iterations of the for-loop, check for legality of the last one
        if(count($unassignedUsers) - $i == 1){
            $lastUserID = $unassignedUsers[count($unassignedUsers)-1]['id'];
            $query = "SELECT id FROM secretsanta WHERE assigned is NULL AND id = ".$lastUserID;
            $res = mysqli_query($conn, $query);
            $rowcount = mysqli_num_rows($res);
            if ($rowcount){
                // last user is still unassigned
                $query = "UPDATE secretsanta SET assigned = '".$lastUserID."' WHERE id = ".$user['id'];
                if(mysqli_query($conn, $query)){
                    echo "Record with id ".$user['id']." updated successfully";
                } else {
                    echo "Error updating record: ".mysqli_error($conn);
                }
            }
        } else {
            // select all unassigned users
            $unassignedIDs = [];
            $query = "SELECT id FROM secretsanta WHERE assigned is NULL AND id <> ".$user['id'];
            $res = mysqli_query($conn, $query);
            while($row = mysqli_fetch_assoc($res){
                $unassignedIDs[] = $row['id'];
            }

            // get a random id from $unassignedIDs
            $randomIndex = rand(0, count($unassignedIDs)-1);
            $randomID = $unassignedIDs[$randomIndex];

            // assign $randomID to user
            $query = "UPDATE secretsanta SET assigned = '".$randomID."' WHERE id = ".$user['id'];
            if(mysqli_query($conn, $query)){
                echo "Record with id ".$user['id']." updated successfully";
            } else {
                echo "Error updating record: ".mysqli_error($conn);
            }
        }
        $i++;
    }
}

last edit: refactored whole code so it is able to be run multiple times and only assigns new users who are not assigned yet.

Concurrency might be a issue here. If multiple people do this simultaneously, a rare case may arise where 2 persons are assigned the same id. — Simrandeep Singh, Sep 11 '17 at 12:03
this script only has to run once, when all users who want to participate are registered. no need for two people to run this simultaneously — kscherrer, Sep 11 '17 at 12:05
That's correct, the script will only be run once at any one time, for this example it will be done by me. If this was implemented as an open to the public project, the creator of the group would be the one to run the script. — Biscuits, Sep 11 '17 at 12:34
Still no luck, fixed a few minor syntax errors but unfortunately it gave the same assignments to some people and gave 0 to some also?? — Biscuits, Sep 11 '17 at 12:50
@Biscuits i just found an offset error at $randomIndex = rand(0, count($unassignedIDs)); and fixed it, try again — kscherrer, Sep 11 '17 at 12:54
i refactored the whole code so you can run it again when new users are added to the table secretsanta. try it now i think it should work — kscherrer, Sep 11 '17 at 13:17
@Cashbee still no luck with the updates, still throwing 0 but now it just keeps throwing the same result, records 1,2 & 4 get 0 and record 3 gets 4?? Weird. It's 11pm here so I gotta get some sleep, I'll look back here tomorrow. Thanks for all your help. — Biscuits, Sep 11 '17 at 13:18
that may be because i re-edited the first query after my comment here x) if it really wont work im sorry. but i think you get the type of approach i took. — kscherrer, Sep 11 '17 at 13:19

symcbean · Answer 2 · 2017-09-11T12:19:18.093

Step 1 is dependent on have a contiguous set of ids for the people. Think what happens if '3' leaves the company and it hires 6 to replace them....1,2,4,5,6 ($maxpersons=5)

"Now I need to check" - no you are still trying to solve the problem by guessing then seeing if your guess worked. Use an algorithm which is always going to return a correct result. The method below requires the addition of a temporary field 'sequence' of type float.

mysqli_query($conn,"UPDATE secretsanta SET sequence=RAND()");
$first=false;
$prev=false;
$all=mysqli_query($conn, "SELECT * FROM secretsanta ORDER BY sequence, id");
while ($r=mysqli_fetch_assoc($all)) {
   if (false===$first) {
      $first=$r['id'];
   } else {
      save_pair($prev, $r['id']);
   }
   $prev=$r['id'];
}
save_pair($prev, $first);

(but with better error checking)

Create a "Secret Santa" generator using MySQL and PHP

2 Answers2