Mysql BETWEEN cluase being ignored. No syntax error.

Question

im trying to show items booked between two separate date columns (start and end dates). i want to search between the dates but the query seems to ignore any dates that land between the selected dates.

SELECT
bookings.booking_id,
bookings.booking_id,
bookings.booked_from,
bookings.booked_from,
bookings.booked_till,
item_set_computer.item_id,
item_set_computer.name,
item_set_computer.booking_groups_id
FROM
bookings,
item_set_computer
WHERE
item_set_computer.item_id = bookings.item_id
AND
item_set_computer.booking_groups_id = 3
AND
booked_from
BETWEEN "2010-04-13" AND "2010-04-20"
AND
booked_till
BETWEEN "2010-04-13" AND "2010-04-20"

for instance, I have an item booked from 13th to 15th. date1 is 2010-04-13 and date2 is 2010-04-15. User searches for booked items from 14th to 16th. It returns no results. Why is it ignoring database dates that drop between dates selected by the user? The columns are set as DATE in the database and have been correctly entered.

Answer 1

are there recordsets with the same id in both table which falls under the clause? also make sure that with WHERE col BETWEEN a AND b, a has to be smaller than b to work.

what gets returned if you comment out the last 6 lines?

Answer 2

You said "User searches for booked items from 14th to 16th."

That means your query will be

AND
booked_from
BETWEEN "2010-04-14" AND "2010-04-16"
AND
booked_till
BETWEEN "2010-04-14" AND "2010-04-16"

The first AND clause will obviously be false (since your 4/13 start date is NOT indeed between 4/14 and 4/16)

The correct logic for you (assuming you want the FULL booking interval) is

AND booked_from >= "2010-04-14" 
AND booked_till <= "2010-04-16" 

Or if you want partial interval

AND booked_from >= "2010-04-16" -- means we booked before the end of interval
AND booked_till <= "2010-04-14" -- means we left after the start