Problem with MAX operator

binlid31

Hi guys, Im trying to put together a booking system with a prevention script to ensure you can't double book a cottage.

I have been putting it together mainly in 3 parts, the first part uses the SELECT MAX() script with two conditions, getting the largest departure date held in the booking table WHERE the begin date of the stored record returned is less than the arrival date selcted and passed on as a variable.

This is then repeated to return details for the next booking , these two details are then passed to an IF statement to see whether you can book it or not.

This is my scriptfor the first part mentioned (where im stuck)

<?php 

$result = @mysql_query('SELECT MAX(end) as recentdate FROM booking WHERE cottage = '.$cottageid.' AND begin < '.$arrival.' '); 
while ($row = mysql_fetch_array($result)) {
 echo '<br><Br><br>'. $row['recentdate'].''  ;
}

?>

The thing is that this first piece of script (i set it to show the value returned to make sure it worked) returns nothing as it sits at the minute. However when you change the " AND begin > ......" It pulls the value in. The problem seems to be when the less than is used (the one I need) I have sample data in place for either side of the date variables passed on i.e. I have dates in the DB for booking 27-29 and for the 01-02 of the following month so theoretically if I use the dates 30-31 in the query I should get the details for the booking from 27-29.... BUT I DONT!!!

(note: the cottage Id's match up no problem)

Any ideas guys, I posted this up on another forum but didn't get a solution

Thanks in advance

Tim

Piranha

First, this error have nothing to do with using max, it is about begin.

What data type is begin? It should be date or datetime to work.

You should quote the date in the query.

To make it easier to spot errors in your queries you should do like this:

$sql = 'SELECT MAX(end) as recentdate FROM booking WHERE cottage = '.$cottageid.' AND begin < '.$arrival.' ';
echo $sql;
$result = @mysql_query($sql);
while ($row = mysql_fetch_array($result)) {

binlid31

Hi thanks for replying, yes the begin and end fields are date fields ie 2007-12-11 sort of idea, put your piece of script in and everything is as it should be but the query still doesn't return the record when less than is used!!!!

Here is the return on you code:

SELECT MAX(end) as recentdate FROM booking WHERE cottage = 1 AND begin < 2007-07-30

It just seems that this cannot process but greater than can!!!

Any ideas?

binlid31

Just quotted the date variable and it has worked........... thank you so much, don't know how I forgot that lol and then read over it in your post first time lol. But thanks again!!!! I could be on here again before this booking system is finished

Piranha

No problem, glad to help. Often when you get problems with the queries let php output them and use them in phpMyAdmin or another database tool, then you get a little more information about the error.

bradgrafelman

Just a little tidbit:

You might want to consider locking the row (or table) from writes while you perform the SELECT, verify that it can be booked, and the INSERT the booking information. Otherwise, it sounds like you have a race condition: what if two (or more) process are simultaneously checking to see if they each can book the date, find that no other booking exists, and then all try to book the same cottage?

Even if it doesn't sound likely, if you're truly wanting to add verification, then you'll want to minimize the chance of this type of situation as much as possible.