[RESOLVED] BETWEEN query won't work

retro

I have a query that works fine, limiting the records by a date selected from a calendar ($DelDate) as follows:

SELECT * 
FROM orders
LEFT JOIN customers ON ( customers.id = orders.customer ) 
WHERE (
recur =1
AND DAYOFWEEK( date ) = DAYOFWEEK( '$DelDate' ) 
)
OR (
recur =2
AND DATEDIFF( date, '$DelDate') %14 =0
)
AND date <= '$DelDate'

However, I now want to exclude any records that lie between a date range. The date range is specified in customers by two date fields, 'holdstart' and 'holdend'.

I added the following to the end:

AND '$DelDate' NOT BETWEEN holdstart AND holdend

However, it just gave the same four results. Three of these results have no holdstart or holdend values, the fourth has a holdstart value of 2008-05-08 and end 2008-05-15. I have tried entering the query in phpMyAdmin manually, replacing '$DelDate' with '2008-05-08', but get the same results.

How can I fix this?

Thanks in advance for any help offered, it is greatly appreciated!

dagon

it will always match blanks: what's not between 0 and 0 -everything

2008-05-08 is not between 2008-05-08 2008-05-15

the only problem here is your logic

retro

...but that is what I want!

These are (for simplification purposes, anyway) weekly recurring orders for food.

Mr. Smith wants a box of veg on Mondays. However, he is away between the 5th and 15th of May. Obviously, he does not want vegetables when he is not there.

Mr. Jones, however, will not be going on holiday. He also has a box of veg on a Monday.

Therefore, I have a start date (2008-05-08) and end date (2008-05-15) in my database for (Mr. Smith's) holidays. I want the query to show me records that are NOT BETWEEN these dates. As far as I can tell, 2008-05-08 IS between 2008-05-08 and 2008-05-15, as it is the starting date, and so shouldn't be displayed. (You're saying otherwise - if you'd care to explain, I'm all ears!) However, Mr. Jones' start and end dates will have a value of 0000-00-00. 2008-05-15 is NOT BETWEEN 0000-00-00 and 0000-00-00, so should show up.

The problem is that Mr. Smith's order is still showing up.

That's how I see it, to me my logic is sound. However, I am a relative beginner at PHP, and very new to MySQL, having not needed to bother with SQL commands in Access, hence why I am posting in the Newbies section. If my logic is wrong, fair enough, tell me. However, don't just leave it at that - explain WHY my logic is wrong and HOW to overcome this! I can accept criticism if it is constructive, but if you aren't going to help me overcome the problem, it isn't at all helpful and quite frankly, what exactly did you achieve out of it?

leatherback

Hi Retro,

I am absolutely unsure.. Just guess here. The manual states that:

expr NOT BETWEEN min AND max:
This is the same as NOT (expr BETWEEN min AND max)

Which means: Not:
equivalent to the expression (min <= expr AND expr <= max)

So yes. Start and end date should be tossed out in your case.

As this is not happening, it could be that the date field in your database is actually a timestamp, and does a comparison on seconds or something?

retro

Well, I posted the question on another forum too, as I hadn't had a reply here, and they were stumped too! They also asked whether the dates were date types, which they are. They asked me to do:

var_dump($DelDate);

Which returned: string(10) "2008-05-08"

Eventually, someone suggested the following:

SELECT * 
FROM orders
LEFT JOIN customers ON ( customers.id = orders.customer ) 
WHERE ((
recur =1
AND DAYOFWEEK( date ) = DAYOFWEEK( '2008-05-08' ) 
)
OR (
recur =2
AND DATEDIFF( date, '2008-05-08') %14 =0
))
AND date <= '2008-05-08'
AND '2008-05-08' NOT BETWEEN holdstart AND holdend

...and that worked in phpMyAdmin! Ahh, two simple extra brackets! It is a little puzzling that it worked with the one AND statement, I guess I was confusing even the database with my query!!

I'm going to try it on the variable now - hopefully it should work!

Thanks guys!