Help with query design.

[Chrisio]

Hi, OK. im trying to design a query that will show me whether I have anything booked within a date range.

basically I have this table
id, (primary)
cid, (int)
resort, (int)
villa, (int)
startdate, (date yyyy-mm-dd)
enddate, (date yyyy-mm-dd)
adults, (int)
children, (int)
infno, (varchr)
outfno, (varchr)
atime, (varchr)
slot, (int ,0 or 1)

what I need is a query that will pull back a record ONLY if the following conditions are met.

villa = $var
endate <= $var
slot <> $var

Im having trouble making this work I have tried multiple different aproaches but cant get any to work how I think they should. My best guess is below, however I think its falling down on the date element, Im not sure if I can use that operator on a date field.

Query:

SELECT * FROM booking WHERE villa = '1' AND enddate <= '2006-08-12' AND slot <> '0'



The above works as long as 'enddate' = $var I cant get it look backwards into the date if that makes sense!!!!

Many thanks
Chris

[penagate]

If villa is an INT field then you shouldn't have quotes around the 1.

However, I am not sure why the enddate part does not work.

Does this?

select * from `booking` where `enddate` <= 20060812;

[Chrisio]

If villa is an INT field then you shouldn't have quotes around the 1.

However, I am not sure why the enddate part does not work.

Does this?

select * from `booking` where `enddate` <= 20060812;

Hi this still isnt working, Prehaps if I explain what I want it will be easier...

I need to be able to check for a booking matching VILLA and the start and end dates clashing with the stored start and end dates.

for instance I have a stored entry

Villa = 1
startdate = 2006-08-06
enddate = 2006-08-12

and I query along the lines of
villa = 1
startdate=2006-08-06
enddate =2006-08-11

The above should pull the record whereas the below wont

-or-

villa=2
startdate=2006-08-06
enddate=2006-08-12


Does that make it any easier

Thanks
Chris

[penagate]

It doesn't explain why your query does not work though. I have tested it myself with no problems.

What DBMS (e.g. MySQL) and version are you using?

[Chrisio]

Hi,
I have manage to get what I want using

sql:
SELECT * FROM booking WHERE villa = 1 AND startdate < '2006-08-16' AND enddate >= '2006-08-13'

However I would also like to add this to my current query if posible.

I have 2 time slots availble 0 and 1 0=am and 1=pm
I am able to have a clash against my enddate and startdate so long as my stored slot = 0 and my quiered slot =1

how can i work that into my current query.