problems with php and mysql

cdub1988

I'm new to php and mysql, but I have a mysql table setup and several files that allow the user to correctly insert, update and delete records from the table. All these functions are currently working properly.

The problem I'm having is that I have a need to do a check based on querying the table for the SUM() of a given field (tix) based on the $tourdate variable and then do a compare on it.

In other words, I was trying to setup something like this:

<?
$resdate = $POST['resdate'];
$tourdate = $POST['tourdate'];
$fname = $POST['fname'];
$lname = $POST['lname'];
$address = $POST['address'];
$city = $POST['city'];
$st = $POST['st'];
$zip = $POST['zip'];
$phone = $POST['phone'];
$altphone = $POST['altphone'];
$email = $POST['email'];
$howheard = $POST['howheard'];
$tix = $POST['tix'];
$cc = $POST['cc'];
$ccexp = $POST['ccexp'];
$mailtype = $POST['mailtype'];

// script to display all the records in a table

include('conn.php');

$query = "select SUM(tix) AS num_tix FROM table WHERE tourdate='$tourdate'";

if(num_tix > 25)
{
echo "There are no reservations available for this tour date and time.";
}
elseif (tix > 25)
{
echo "You have entered too many reservations for this tour.";
}
else
{

// Insert Data into table
$query = "INSERT INTO haunted (resdate, tourdate, fname, lname, address, city, st, zip, phone, altphone, email, howheard, tix, cc, ccexp, mailtype) VALUES ('$resdate', '$tourdate', '$fname', '$lname', '$address', '$city', '$st', '$zip', '$phone', '$altphone', '$email', '$howheard', '$tix', '$cc', '$ccexp', '$mailtype')";
$result = mysql_query($query);

// Close the database connection
mysql_close();

echo "Your reservation has been submitted to the database.<br /><br />Click <a href='display.php'>here</a> to review your additions.";
}

but all I seem to get is the INSERT statement to execute......

any ideas on what I'm missing?

Thanks!

mourisj

Some of my thoughts about this:

Is this your complete script, or just a part of it?
If it is complete, I think there should be all the HTML tags which should be in any valid HTML file: <html>, <head>, <body> tags with their respective end-tags, etc.
You do not execute your first query: $query = "select SUM(tix) AS num_tix FROM table WHERE tourdate='$tourdate'";
You just set the valueof $query.
You have to mysql_query($query) like you do later, and fetch the result from it; otherwise, your num_tix will not be set!
I think, you also have to write
if ($num_tix > 25)
and
elseif ($tix > 25)
I guess you want to limit the tix for each tour to 25? What if num_tix == 24, and your next guest makes a reservation for 25 tix? You will end up with 49 reservations, and your program will accept it! You should check your algorithm for that part!
For instance, you could do something like:
...
elseif ($num_tix+$tix > 25)
{
echo "Not enough reservations available";

HTH

JJ Mouris
}

cdub1988

mourisj -

Thanks for the reply.

I thought that was probably the issue (the query execution), but wasn't real sure about where to drop it.

I'll try your suggestions and will post back either way.

Thanks again.

Chris

cdub1988

mourisj -

That didn't work.

I don't know what I'm doing wrong......

Here's what I have.....

// script to display all the records in a table

include('conn.php');

$query = "select SUM(tix) AS num_tix FROM table WHERE tourdate='$tourdate'";
$result = mysql_query($query);

if($num_tix > 25)
{
echo "There are no reservations available for this tour date and time.";
}
elseif(($num_tix + $tix) > 25)
{
echo "You have entered too many reservations for this tour.";
}
else
{

// Close the database connection
mysql_close();

echo "Your reservation has been submitted to the database.<br /><br />Click <a href='display.php'>here</a> to review your additions.";
}

Any ideas?

Thanks!

Chris

mourisj

$query = "select SUM(tix) AS num_tix FROM table WHERE tourdate='$tourdate'";
$result = mysql_query($query) or die("Failed to get num_tix");

$num_tix = mysql_fetch_array($result[num_tix]),

if($num_tix > 25)
{
echo "There are no reservations available for this tour date and time.";
}
elseif(($num_tix + $tix) > 25)
{
echo "You have entered too many reservations for this tour.";
}
else
{
...

you have to fetch the result into a variable, otherwise you just have a result-set, but not automatically your variables filled: In many cases, you will have more than one row returned; in this special case, you only have one row (and one field). If you have many rows returned, you have to loop through the results.

Just imagine your query finds 100 rows, or just even 2: how would PHP know, which value to fill into your variable? Thus, you have to tell it by fetching the first value from the result set. Fetching again would get you the second row, etc.

I recommend to read PHP manual or some book about the subject to understand these basic things before proceeding.

JJ Mouris