[SOLVED] SQL BETWEEN Problem

[gerkintrigg]

I'm trying to check whether a property is available during a specified range of dates using this code:

<?php 
$avail_query="SELECT COUNT(*) as records FROM booking, cottage WHERE 
	((
	(Date_ADD( start_date, INTERVAL 1
DAY ) ) BETWEEN '$week_start2' && '$week_end2') 
	|| ((Date_SUB( end_date, INTERVAL 1
DAY ) ) BETWEEN '$week_start2' && '$week_end2') 
	|| ('$week_start2' BETWEEN (Date_ADD( start_date, INTERVAL 1
DAY ) ) && (Date_SUB( end_date, INTERVAL 1
DAY ) )) 
	|| ('$week_end2' BETWEEN (Date_ADD( start_date, INTERVAL 1
DAY ) ) && (Date_SUB( end_date, INTERVAL 1
DAY ) )))
	&& cottage.name='$cot'
	&& cottage.cottage_id=booking.cottage_id";
?>

 

It's not finding some over-lapping date ranges that should have been returned.

Can anyone help please?

[redarrow]

would this make a diffrenc only trying theo

 

COUNT(DISTINCT start_date , end_date)

[TreeNode]

Looks to be fine as long as your date fields are formatted correctly. Try printing out a parsed version and run that SQL statement directly into the console (or through phpmyadmin).

[Illusion]

the problem might be as you are comparing DATETIME values(start_date) with DATE values(like $week_start2) so use CAST() function if you are passing those php varibles as a strings.

[Wildbug]

You're not getting errors from using "&&" instead of "AND" in the BETWEEN ...?

[gerkintrigg]

I wonder how often I come across someone asking about && Vs. AND... no && is the same.

There are NO errors, it just outputs an incorrect data set.

[Wildbug]

I tried it on my MySQL and it does produce an error.  That's why I brought it up, and that's why you come across someone asking about that.  But if you're getting results, clearly we have different SQL servers/versions.

mysql> SELECT 2 BETWEEN 1 && 4;                                                                        
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near '&& 4' at line 1
mysql> SELECT 2 BETWEEN 1 AND 4;                                                                      
+-------------------+
| 2 BETWEEN 1 AND 4 |
+-------------------+
|                 1 | 
+-------------------+
1 row in set (0.00 sec)

 

So you're trying to see if either date is within the range of other dates, non-inclusive?

[Wildbug]

In fact, can you give an example of what you get and what you expect to get with the dates that you're using?

[gerkintrigg]

The code I was looking at still seems to be fine, but after changing some PHP code in a related include it all seems to work perfectly. Thanks anyway chaps.