Date range problems...Please have a look

Hello

I have been working on a website which requires a user to be able to search between certain date ranges.

I have a table which consists of two columns which are used for matching and comparing the dates from. Each user is able to submit a travel request and will be travelling within a certain time.

For example:-

date_from (The day the user is travelling)
date_to (The day upto travelling, or also known as the last day of travel)

date_from & date_to are also the names of the columns on the table.

Now i would like the query to also pickup users which are travelling between these point of times, nothing less than the "date_from" and nothing greater than the "date_to". I have had several play around with queries and some return results and other do not. Now the ones which have returned results, also returns the result if the "date_to" is greater than one matched within the table, this should not be possible! Can somebody help me here.... I have given example of the kind of query i am working with.

$sql = "SELECT travelling_details.*, user_table.* FROM travelling_details LEFT JOIN user_table ON travelling_details.user_id=user_table.id WHERE (date_from <= '$date1') and (date_to >= '$date2')";

PHP:

Some insight would be great thankyou!

 

have you tried BETWEEN?

SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
AND to_date ('2003/12/31', 'yyyy/mm/dd');

This SQL statement would return all orders where the order_date is between Jan 1, 2003 and Dec 31, 2003 (inclusive).
It would be equivalent to the following SQL statement:

SELECT *
FROM orders
WHERE order_date >= to_date('2003/01/01', 'yyyy/mm/dd')
AND order_date <= to_date('2003/12/31','yyyy/mm/dd');

​

 

Yes but that only uses one column! I have 2 columns for storing these date ranges "date_from" and "date_to". I have been wandering how i could perform a search using between on 2 date columns.