Need help writing a tricky DB query

[launchpad67a]

Sorry this is a long question it's a bit difficult to explain:

I have a reservation system I have built that has a limited number of entries available per time slot. Two of the input fields are for an 'adult' or 'child'. These fields are text fields where the user simply types the # of adults or child in the box, (ie; adult = 2 child =1). These are stored in the DB as a number under the adult and child columns.

I've setup a query that checks 'availability' for any given time, day, month, year...because there is a limit of "14" for any combination of those entries. That all works fine, but it doesn't check the numbers stored in the adult and child columns (and count them as more than 1 entry).
So here's my problem:
How do I get the query to check the 'adult' and 'child' entries and count them? Because if a user enters 2 adults, this is not being checked in my original query and needs to be counted as '2' entries for that time slot.

I hope this makes sense because it's difficult to explain.

Another example: If a user enters 2 adults, this is still written to a single row in the DB. And only considered as 1 when I do my query to check the availability...when it needs to be counted as "2" entries.
Understand?

Here's the query I'm using to check availability:
$sql = "SELECT * FROM `entries` WHERE `time` = $time AND `day` = $day AND `month` = $month AND `year` = $year";
$result = mysql_query($sql);
$entries = mysql_num_rows($result);
if ($entries < 14) {

Thanks, sorry for the long post.
Mike

[NogDog]

Perhaps something like:

$sql = "SELECT SUM(`adult` + `child`) AS `total` FROM `entries`
WHERE `time` = $time AND `day` = $day AND `month` = $month AND `year` = $year";

[launchpad67a]

Perhaps something like:

$sql = "SELECT SUM(`adult` + `child`) AS `total` FROM `entries`
WHERE `time` = $time AND `day` = $day AND `month` = $month AND `year` = $year";

Thanks NogDog,
But that didn't work. Sure looks like it could work but it doesn't.
In the AS `total` statement...what is `total`? And shouldn't something be checking the `total` to see what it is after adding the adult and child fields?

I knew this was a strange query question but I'm sure one of you pros can figure it out.

Thanks,
Mike

[chazzy]

values need to encapsulated in single quotes when used in a query.

what are the types for time, month, year etc?

you should be able to fix this by doing something like `time`='$time'.

[launchpad67a]

values need to encapsulated in single quotes when used in a query.

what are the types for time, month, year etc?

you should be able to fix this by doing something like `time`='$time'.
chazzy,
Not sure what you mean by "types" for time, month ect...?

I was thinking of another way to describe what I need done here:
Try this...I need to add all the values from the adult and child columns for the given time, day, month and year that are being entered for the query. Then add that total to the total rows for the time, day, month, year of the query. This would give the absolute total before I check if $entries <14
See what I mean?

[chazzy]

yes... i saw that from your first post.

do you know what column types you gave to these columns of "time,month,day,year"

[launchpad67a]

Oh yes, sorry.
They are all varchar(40) NOT NULL Default ''

Thanks

[NogDog]

"total" was just an arbitrary name I chose to assign the value of the sums. If the query ran OK, then you you have to do a mysql_fetch_assoc() on the query result, and the total should then be in the array element named 'total':

$sql = "SELECT SUM(`adult` + `child`) AS `total` FROM `entries`
WHERE `time` = $time AND `day` = $day AND `month` = $month AND `year` = $year";
$result = mysql_query($sql) or die("Query failed: $sql - " . mysql_error());
$row = mysql_fetch_assoc($result);
$total = $row['total'];

Of course, I'm guessing/assuming things about the column names, so you may need to adjust them. And as Chazzy points out, if the date/time values you are checking are not simple numeric values, then they must be quoted in the Where clause.

[launchpad67a]

Yes!! That works perfectly!! I barely had to alter the script and it works like a charm.
Thanks so much.

Now to find a fix for my other post about NOW() and returning a days entries.

Thanks guys,
Mike