"Yes, that is the proper direction to travel, Avatar!" (ultima 6

)
I think your reservation system is advancing.
But the condition in your where-clause is wrong; you're searching for a date that is
smaller than your start-date and at the same time
bigger than your end-date. Since start-date is probably
smaller than end-date this condition cannot be fulfilled. You have to switch > and <
Let me show you something else. If you're sure about using mysql you might take full advantage of its features. For the examples you need to connect to your mysql-server directly (e.g. via mysqlc from console)
first an example directly from
http://www.mysql.com/doc/en/Comparison_Operators.html#IDX1134SELECT 'b' BETWEEN 'a' AND 'c'
this should echo
1 because mysql "decides" that the character
b is between
a and
c and therefor returns
1 for this query. try again with
'z' BETWEEN 'a' AND 'c'.
This also works with dates (the next example is not 100% correct because it relies on an implicit type conversion string->date that will not take place imho. But ignore it for now

)
Code: Select all
SELECT '2003-04-05' BETWEEN '2003-03-30' AND '2003-04-06'
this also returns
1 because the 5th of april is between 03/30 and 04/06.
But why calculate the end date
by hand if mysql can do that for you?
take a look at
http://www.mysql.com/doc/en/Date_and_time_functions.html
Code: Select all
SELECT '2003-03-30' + INTERVAL 6 day
amazing, isn't it
now combine it with BETWEEN
Code: Select all
SELECT '2003-04-05' BETWEEN '2003-03-30' AND '2003-03-30' + INTERVAL 6 day
let's see wether this works correctly for dates that are not within the interval
Code: Select all
SELECT '2003-04-15' BETWEEN '2003-03-30' AND '2003-03-30' + INTERVAL 6 day
I think this is much simpler...
You can use it for where-clauses, too (would be useless for you if not), e.g.
Code: Select all
SELECT * FROM tablename WHERE datefield BETWEEN '2003-03-30' AND '2003-03-30' + INTERVAL 6
this will return all records having a value for
datefield that is between 03/30 and 04/6
the examples as php code (just in case you have no mysql-client)
Code: Select all
<html><body>
<table border="1">
<?php
$dbHost = '???'; // edit
$dbUser = '???'; // edit
$dbPass = '???'; // edit
$dbConn = mysql_connect($dbHost, $dbUser, $dbPass) or die(mysql_error());
$querries =
array(
"SELECT 'b' BETWEEN 'a' AND 'c'",
"SELECT 'z' BETWEEN 'a' AND 'c'",
"SELECT '2003-04-05' BETWEEN '2003-03-30' AND '2003-04-06'",
"SELECT '2003-03-30' + INTERVAL 6 day",
"SELECT '2003-04-05' BETWEEN '2003-03-30' AND '2003-03-30' + INTERVAL 6 day",
"SELECT '2003-04-15' BETWEEN '2003-03-30' AND '2003-03-30' + INTERVAL 6 day"
);
foreach($querries as $query)
{
$result = mysql_query($query, $dbConn) or die(mysql_error());
?>
<tr>
<td><?php echo $query; ?></td>
<td><?php echo array_shift(mysql_fetch_row($result)); ?></td>
</tr>
<?php
}
?>
</table>
</body></html>