Problem with "SELECT SUM(Tickets) FROM bookings WHERE Performance = '...

[ooda55]

hi there

i have been trying to figure this one out for hours but no hope

basicly this code adds the input from a form to the drama departments DB, then checks the total number of tickets that have been booked

the db input code is fine, its the total number code i cant get to work

the code SHOULD go through the table and find all the rows containing the selected performance name (Test1 for example) in the performance colum

then all the rows containing test1, the "tickets" colum will be added uthen it will be echoed out

 

heres my code:

<?php
$Performancename = $_POST['Performance'];
mysql_connect("localhost","dncwd_drama","*********");
mysql_select_db("dncwd_drama"); 

$sql="INSERT INTO bookings (Performance, Name, Email, Phone, Tickets)
VALUES
('$_POST[Performance]','$_POST[Name]','$_POST[Email]','$_POST[Phone]','$_POST[Tickets]')";

if (!mysql_query($sql))
  {
  die('Error: ' . mysql_error());
  }
else echo " <br> Tickets Reserved ! <br> you should recive a conformation email shortly, please print it and bring it with you <br>";




$query = "SELECT SUM(Tickets) FROM bookings WHERE Performance = '$Performancename'"; 

$result = mysql_query($query) or die(mysql_error());

// Print out result
$tot = mysql_fetch_array($result);

echo "Bookings = $tot";
echo "<br>";
?>

the problem is the output of the page is:

Tickets Reserved !

you should recive a conformation email shortly, please print it and bring it with you

Bookings = Array

 

thanks for all help guys

 

[attachment deleted by admin]

[rajivgonsalves]

first I suggest change this

 

$query = "SELECT SUM(Tickets) FROM bookings WHERE Performance = '$Performancename'"; 

 

to

 

$query = "SELECT SUM(Tickets) as total_tickets FROM bookings WHERE Performance = '$Performancename'"; 

 

then later in the code change this

 

echo "Bookings = $tot";

 

to

 

echo "Bookings = ".$tot['total_tickets'];

 

hope its helps

 

[ooda55]

oh mate thats fantastic thanks i would never have figured that out

can you please expalin the whole ".$" thing

whats the difference between a .$variable and a $variable?

[ooda55]

can you also tell me how i can run the Die('sorry the performane is fully booked') command is the .$tot['total_tickets'] + $_POST[Tickets] > $seatlimit

i have had a play but no hope

thanks again

chris

[rajivgonsalves]

the first one is setting a alias for the row so the column, you should read more out here

 

http://dev.mysql.com/doc/refman/5.0/en/select.html

 

the whole idea is to get a proper key name in your php array

 

going further when you fetch data using mysql_fetch_assoc it fetches data into an array and not a scalar variable, so you will have to use it as an array, you can read more about array out here

 

http://php.net/manual/en/language.types.array.php

 

as for the .$variable that just neater coding nothing else you can do it the way you did it before. its just concatenation of a variable to a string

 

http://www.php.net/manual/en/language.operators.string.php

 

for your full booked you can just put a if statement

 

if (($tot['total_tickets'] + $_POST[Tickets]) > $seatlimit) {
   die('Sorry the performance is fully booked');
}

 

provided your $seatlimit is populated with the proper data it should work

 

Hope the explaination was helpful

[ooda55]

thanks again for the advice Rajiv

i dont think ive got my head round these arrays yet

how can i make a "remaining tickets" script

this is what i have so far:

echo 'remaining tickets= $tot['total_tickets'] - $seatlimit';

[rajivgonsalves]

I think it should be

 

$remaining tickets =  $seatlimit - $tot['total_tickets'];

 

then you can use the remaining tickets variable in your code

 

[Buddski]

To get your remaining tickets you can do this

 

$tickets_remaining = ($seatlimit - $tot['total_tickets']);
echo 'Tickets Available: '. $tickets_remaining;

And as you said previously, its best to check if the amount of tickets that person is booking is LESS than or EQUAL to the amount of tickets available, something like this might help you with that..

if ($_POST['tickets'] <= $tickets_remaining) {
// You can add their booking
} else {
// Advise the user that their booking cannot be fulfilled because of the ticket limit.
}

Another alternative is, if the user books 5 tickets and there is 4 spaces left.. You can give them the remaining 4 tickets and advise them that 1 of their friends is going to miss out

[ooda55]

Thanks to everyone for the help

i did try the $remaining tickets =  $seatlimit - $tot['total_tickets'];

but it didnt echo anything, and it didnt give me an error

this is becuase i didt realise i put it AFTER the die() LOL

I'm realy impressed by the help people give on thif fourm, and i plan to start giving advice when i get a bit better