php sql

timmybuck

hi guys

i have four tables customer, booking, ticket and event.

my goal is to make sure the system does not allow an event to be overbooked.

how would i go about writing the sql or hph query for this.

any guidance would help

barand

That would depend on the fields you have in each table and how they relate to one another.

If event table has a capacity field and booking has an event_id and number of tickets you could

SELECT e.event_id , e.description, SUM(b.tickets) as sales FROM
event e INNER JOIN booking b
GROUP BY e.event_id , e.description
HAVING sales >= e.capacity

timmybuck

Hi Barry,

Thanks for your response.

my design can be found at

http://homepage.ntlworld.com/unixhome/database-design.htm

could i drop that code in some php and make it display ina web browser

barand

Can a booking have multiple tickets>

(Julia Roberts won't thank you for that test data :p )

timmybuck

yes it would be more realistic if a booking can have multiple tickets.

see im sure that my design needs to be reworked, i just haven't got any one to bounce ideas off.

i thought about having a one booking for one customer.

i think maybe i need to think about php doing most of the processing work and just have simple sql for data retrivel

what do u reckon

barand

SQL can be very concise and a well designed query can save a page of php coding so it pays to use it where you can.

If bookings have several tickets (which seems reasonable) then we need to change the query a little. We need an extra join from booking to ticket and then count the tickets for the booking.

Also we reverse the test to 'ticket count < capacity' so only those events with spare capacity are displayed when selling tickets.

$sql = "SELECT e.event_id , e.description, COUNT(t.*) - e.max_bookings as available FROM 
event e INNER JOIN booking b ON e.event_id = b.event_id
INNER JOIN ticket t ON b.booking_id = t.booking_id
GROUP BY e.event_id , e.description 
HAVING available > 0";

$result = mysql_query($sql);

timmybuck

ok that's great, im gonna try and drop that in some php code and try and get it to work.

may i ask another question, if you're still around something else i would like to do if possible is have a fully booked icon appear if all tickets have been sold.

how do you think that could be implemented

barand

You would need to remove the HAVING bit so all events are displayed and check availabilty for each one

$sql = "SELECT e.event_id , e.description, COUNT(t.*) - e.max_bookings as available FROM 
event e INNER JOIN booking b ON e.event_id = b.event_id 
INNER JOIN ticket t ON b.booking_id = t.booking_id 
GROUP BY e.event_id , e.description ";


$result = mysql_query($sql);

echo "<table>";
while (list($id. $desc, $avail) = mysql_fetch_row($result)) {
    $icon = $avail <= 0 ? "<img src=\"full.gif\">" : "" ;
    echo "<tr><td>$id</td><td>$desc</td><td>$icon</td><.tr>";
}
echo "</table>";

hth

timmybuck

bloody hell your quick are u a professional dude or something.

I have copied and pasted your code in to notepad and i wll try and understand it and implement it

timmybuck

should i check aviliabilty using sql or php

barand

The availability is calculated in the SQL but I've just noticed I got it back to front. SQL should be

$sql = "SELECT e.event_id , e.description, e.max_bookings - COUNT(t.*) as available FROM
event e INNER JOIN booking b ON e.event_id = b.event_id
INNER JOIN ticket t ON b.booking_id = t.booking_id
GROUP BY e.event_id , e.description ";