How to make PHP Ticker Number Assignment More Robust?

I am creating a ticketing system. I wish for each entry to be a unique number between the specified range. I have created functions, which I will include to show how I am processing things. If I purchase all the tickets in one go, the function generally has 3 or 4 duplicate numbers.

I have attempted this but failed ending up with some duplicates if a person purchases all tickets at once. I assume this can happen if purchasing a few at a time also. This needs to be more robust.

Taken tickets which returns an array of numbers already taken

function takenNumbers($drawID){
    $connect = new mysqli("localhost", "root", "", "dream");
    $stmt = $connect->prepare("SELECT * FROM transaction WHERE DrawID = ?");
    $stmt->bind_param("i", $drawID);
    $stmt->execute();
    $result = $stmt->get_result();

    $tickets = array();
    while($row = $result->fetch_assoc()){
        $id = $row['ID'];
        $tickets[] = $row['TicketNumber'];
    }

    return $tickets;
}

Numbers which are not taken also returned in an array

function freeNumbers($drawID){
    $minTickets = 1;
    $maxTickets = totalTickets($drawID);
    $takenNumbers = takenNumbers($drawID);
    $freeNumbers = array();
    $allTickets = range(1, $maxTickets);
    $freeNumbers = array_values(array_diff($allTickets, $takenNumbers));

    return $freeNumbers;  
}

Then I have a random ticket generator based on these functions

function randomTicket($drawID){
    $num = freeNumbers($drawID);
    $random = array_rand($num, 1);
    return $random;
}

After payment processing I call this function to insert to database along with the randomTicket.

for($i = 0; $i < $quantity; $i++){
    echo paymentMade($paymentId, $token, $payerID, $drawID) . "<br>";
}

I am receiving duplicates. This cannot happen in the live application. I have tried a number of things and found this quite an issue to program around. Any and all input would be appreciated. Thanks in advance guys.

Solution

Your database is the source of truth here, it is the repository of all possible ticket numbers and the tickets that have been assigned.

When you try to do this inside your web application there is always the possibility of collisions when there are multiple concurrent users or worse when your application is distributed over multiple servers.

That said, it would be better to find a database solution to issuing these tickets.

The first thing that you should implement is a UNIQUE INDEX on your transaction table that is recording the ticket numbers, then the DB will maintain the integrity of your numbers at all times:

You have not provided the full schema so I am assuming that your transaction table has both a DrawID and a TicketNumber column

CREATE UNIQUE INDEX UX_DrawTicketNumbers
ON transaction (DrawID,TicketNo);

Now when you insert a duplicate ticket number, the DB will fail the operation. A basic way to start is to simply handle the failure and use your current logic to keep retrying with the next number until it works.

You can query for the next ticket number directly from the database using a CTE:

WITH is supported in MySQL since version 8 (Released in 2016), if you are using an older version you could populate a concrete or temporary table with all the possible ticket numbers to use in place of this WITH clause


  SET @DrawID := 1; -- set your drawId here
  SELECT TotalTickets into @MaxTicketNo FROM Draw WHERE ID = @DrawID;
  WITH RECURSIVE TicketNumbers (n) AS
  (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM TicketNumbers WHERE n < @MaxTicketNo
  )
  SELECT n  FROM TicketNumbers 
  WHERE n NOT IN (SELECT TicketNo FROM transaction WHERE DrawID = @DrawID)
  ORDER BY RAND() LIMIT 1;

By default, MYSQL limits recursion to 1000, so you may need to set the current session recursion limit to a number larger than or equal to the totalTickets

SET SESSION cte_max_recursion_depth = 1000000;

Now you could utilise atomicity of the database to take the result of the above query and use it directly in your insert statements, in SQL Server using CTEs I would just do something similar to this for the transaction update: (I can't get it to work in DB Fiddle, but the concept should work)

SET @paymentId := 1, @token := 'XF-BankResponseNo', @payerID := 234, @drawID := 1;
SELECT TotalTickets into @MaxTicketNo FROM Draw WHERE ID = @DrawID;
WITH RECURSIVE TicketNumbers (n) AS 
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM TicketNumbers WHERE n < @MaxTicketNo
)
INSERT INTO transaction (PaymentID, TokenID, PayerID, DrawID, TicketNo)
SELECT @paymentId, @token, @PayerID, @DrawID, n
FROM TicketNumbers
ORDER BY Rand() LIMIT 1;

tl;dr;

To ensure atomicity, find a way to move this process to the database, create a stored procedure to encapsulate the process of assigning the ticket number and merging that in with the payment confirmation process.