This is probably a simple task, but its late, and I can't seem to get it right...

I have a table that includes two fields "date_event_from" and "date_event_to". I am trying to match a single user selected date to select events within which the from and to are defined.

For example (this is an example of a row in the table):

event_id = 1
date_event_from = "2006-05-22 00:00:00"
date_event_to = "2006-06-01 00:00:00"
event_title = "Event Title"
event_description = "Event Description"

... Now the user has selected to view all events for 05/25/2006 and I have it converted into datetime format (2006-05-25 00:00:00). How do I set my select statement to select events that span over the user selected date?

I am currently using the following statement, but its not returning any results:

PHP Code:

$startRange = '2006-05-25 00:00:00';  // This would be the user selected date
$endRange = '2006-05-25 23:59:59';
 
$sql = 'SELECT * FROM calander_events WHERE date_event_from >= "' . $startRange . '" AND date_event_to <= "' . $endRange . '"'; 


I'm just writing this without verifying if it works.

PHP Code:

$sql = 'SELECT * FROM `calender_events` WHERE `date_event_from` >= "' . $startRange . '" AND `date_event_to` <= "' . $endRange . '"'; 


Hopefully that's it, otherwise it's back to the drawing board for me.

Cheers,

MC

Sorry MC - Its not working.

The logic is backwards as to what I'm trying to do. SELECT * FROM table WHERE user_selected_date BETWEEN date_event_from AND date_event_to

Well your problem is the end date inside the row is 2006-06-01, which is 2 weeks, rather than ending in the same day, so unfortunately your condition for WHERE TRUE AND TRUE, is actually WHERE TRUE AND FALSE.

If you fixed that, then you'd solve your problem. If it does go on for two weeks, then obviously you're going to have to make checks for conditions on a per row by row basis then, or just selecting dates that start BETWEEN '2005-05-22 00:00:00' AND '2005-05-22 23:59:59'; That way, you'll at least grab all events that are starting on that day.

Cheers,

MC

I used the between logic as MC had shown. I didn't think you could search user data BETWEEN two table fields, but you can!

However, it was still causing problems when it came across dates where the start time was beyound 00:00:00. I fixed this problem by modifying the table with four fields: date_event_from (YYYY-MM-DD), date_event_to (YYYY-MM-DD), time_event_from (HH:MM:SS), and time_event_to (HH:MM:SS).

It helps to split apart the dates and times and use four fields instead to two, I'm finally getting the results I need.