How can PHP utilize SQL JOIN to calculate the available seats in vehicles based on database entries?

To calculate the available seats in vehicles based on database entries using SQL JOIN in PHP, you can join the tables containing vehicle information and booking information. By counting the total number of seats for each vehicle and subtracting the number of booked seats, you can determine the available seats. 

<?php
// Establish a database connection
$connection = new mysqli("localhost", "username", "password", "database");

// Query to calculate available seats in vehicles
$query = "SELECT vehicles.id, vehicles.model, vehicles.seats - COALESCE(SUM(bookings.seats_booked), 0) AS available_seats
          FROM vehicles
          LEFT JOIN bookings ON vehicles.id = bookings.vehicle_id
          GROUP BY vehicles.id";

$result = $connection->query($query);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Vehicle ID: " . $row['id'] . " | Model: " . $row['model'] . " | Available Seats: " . $row['available_seats'] . "<br>";
    }
} else {
    echo "No vehicles found.";
}

$connection->close();
?>

Keywords

PHP SQL JOIN database entries available seats vehicles

Related Questions