-1

I have two tables. An "events" tables where events have a unique event_id and where each events has a total_tickets available. I then have a "bookings" table where event_id is used as a foreign key. For each booking, I have a tickets_booked value. I would like add all the tickets_booked for a specific event_id in my "bookings" table and compare it to the total_tickets value from my "events" table for that same event_id.

I basically want to warn the user when an event is sold out.

So far I have 2 queries but I can seem to combine them.

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['book'])) {
$num_tick = $_POST['num_tick'];

$booked = "SELECT SUM(booked_tickets) AS sum_tickets FROM bookings 
WHERE event_id = '$event_id'";
$result = $conn->query($booked);

$total = "SELECT total_tickets FROM events 
WHERE event_id = '$event_id'";
$result2 = $conn->query($total);

if (($result + $num_tick) < $result2) {

$sql = "INSERT INTO bookings (user_id, event_id, booked_tickets)
VALUES ((SELECT user_id FROM users WHERE username = '$user'), '$event_id', '$num_tick')";

    if ($conn->query($sql) === TRUE) {
        header('Location: dashboard.php?username=' . $user);
    }
}

I know this is absolutely not correct, but this is the logic I have so far :D Any help would be appreciated !

Agathe
  • 1
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman May 17 '22 at 15:08
  • Why do you need two queries? Why not just do it in a single query? – Dharman May 17 '22 at 15:09
  • Maybe `SELECT case when SUM(booked_tickets) >= ? then 1 else 0 end as num_tick FROM bookings join events using(event_id) WHERE event_id = ?` then bind `$num_tick ` use `num_tick` index to determine if tickets are available. I would just store the userid in your session rather than name, or in addition to. Current insert would be `INSERT INTO bookings (user_id, event_id, booked_tickets) select user_id, ?, ? FROM users WHERE username = ?` then bind in $event_id, $user, and $num_tick – user3783243 May 17 '22 at 15:12
  • @Dharman that is what i am trying to do! one single query – Agathe May 17 '22 at 15:14
  • I would imagine that something simple as `SELECT (SELECT SUM(booked_tickets) AS sum_tickets FROM bookings) + ? < (SELECT total_tickets FROM events WHERE event_id = ?) should do the job. Remember to parameterize the query! – Dharman May 17 '22 at 15:16
  • If you want to fetch the values to PHP, the linked question should answer that problem. – Dharman May 17 '22 at 15:17

0 Answers0