How can I make sure no duplicates occur when making a site that preorders tickets?

Question

I am making a website in which people can select which tickets they want and they just get an ID to go pay and pick them up to the desired location.

I have a PHP function that actually checks if the tickets are already booked before allowing people to reserve them, but I just found out that if 2 people happen to click the "booking" button within the same second, my system reserves the places twice.

Is there a way to prevent this? My current code works well except for what I've said, it's here:

$f1="";
    $f2="";

    $sqlAP="SELECT * FROM apartados";
                if ($resultAP = mysql_query($sqlAP)) {
                    while ($rowAP = mysql_fetch_array($resultApP)) {
                        $f = $rowAP['funcion'];
                        $lugar=$rowAP['lugar'];
                        $count++;
                            if($f=="F1"){
                            $f1.=($lugar. " ");
                            }else if($f=="F2"){
                            $f2.=($lugar. " ");
                            }

                    }
                }
            $sqlPag2="SELECT * FROM pagados";

                if ($resultPag2 = mysql_query($sqlPag2)) {
                    while ($rowPag2 = mysql_fetch_array($resultPag2)) {
                        $f = $rowPag2['funcion'];
                        $lugar=$rowPag2['lugar'];
                        $count++;
                        if($f=="F1"){
                            $f1.=($lugar. " ");
                            }else if($f=="F2"){
                            $f2.=($lugar. " ");
                            }
                    }
                }
            $func1= explode(" ",$f1);
            $func2= explode(" ",$f2);
        $repetidos=0;

        for($int=0;$int<$cant;$int++){
            $helper=0;
            while($func1[$helper]){
                if($func1[$helper]==$lugar[$cant]){
                    $repetidos++;       
                    }
                $helper++;
                }
            }
        for($int=0;$int<$cant2;$int++){
            $helper=0;
            while($func2[$helper]){
                if($func2[$helper]==$lugar2[$cant2]){
                    $repetidos++;       
                    }
                $helper++;
                }
            }

This takes from the database what has been booked (apartados) and paid (pagados) and then checks for repeated seats trying to get booked (repetidos) after this comes an if just looking for repetidos > 0, it works, except on the instance I described earlier. Could anyone let me know how I can avoid this?

score 1 · Answer 1 · answered Nov 18 '10 at 01:52

Give each ticket a unique id.
Insert the tickets in to a "reserved" table.
Do this in a transaction, so all tickets commit at once.
Put a unique constraint on the ticket id in the reserved table.

If you get an error during your insert, then someone else has already reserved one of the tickets.

score 0 · Accepted Answer · edited May 23 '17 at 12:11

0

PHP + MySQL transactions examples

Use transactions. They'll make your life a lot easier when you need to do multiple queries that can't be done in parallel.

edited May 23 '17 at 12:11

Community

1
1

answered Nov 18 '10 at 01:51

Scott

1,477
12
13

score 0 · Answer 3 · answered Nov 18 '10 at 01:57

The proper way to do this would be to design the database with a unique constraint on the seat/ticket column, then insert a new reservation in one atomic query. If a reservation already exists for this seat, the database will complain and you can handle that complaint in your code. It won't be possible to insert two identical seat reservations into the database. Selecting all records and looping through them in your application is, sorry, ludicrous.

score 0 · Answer 4 · answered Nov 18 '10 at 02:08

When worried if the same seats are going to be reserved at the same time you can use MySQL table locks. When locked, no one else can write to that table. The process would look something like this:

lock table
check if seats are available
reserve seats for this user if available or return error if not
unlock table

This will prevent tickets being reserved twice as no other scripts can access the table until it is unlocked (other requests get 'queued' if they happen at the same time)

How can I make sure no duplicates occur when making a site that preorders tickets?

4 Answers4