querying bookings/events that are in a given month

[dadamssg87]

I'm trying to display all events that are in a given month. The events have 'start' and 'end' datetimes in my db. I came up with this query but it only grabs events that start AND end on the same month. I'm trying to figure out how to query events that span multiple months. Ex: Start in the previous month and end in the the current month; Start in the current month and end in the next month, or start in the previous month and end after the current month. I'd appreciate the help, thanks.

 

<?php
$first = "2011-06-01";
$last = "2011-06-30";
$cal_id = "77";

$query = "SELECT * FROM Bookings WHERE DATE(START) AND DATE(END) BETWEEN  '$first' AND  '$last' AND cal_id =  '$cal_id'";
?>

 

[WebStyles]

try something like this: (I'm assuming your database fields are in the date format YYYY-MM-DD)

 

$yesterday = date("Y-m-d",strtotime("- 1 day");

 

$query = "select * from `Bookings` where (`startDate` BETWEEN  '$first' AND  '$last') and (`endDate` > '$yesterday') ";

 

// also assuming you do not want to grab expired records, thats why I added condition that end date should be greater than yesterday.

 

hope this helps

[The Little Guy]

I may be wrong, but I am thinking something like this:

 

<?php
$first = "2011-06-01";
$last = "2011-06-30";
$cal_id = "77";

$query = "SELECT * FROM Bookings WHERE (startDate >= '$first' OR endDate <= '$last') AND cal_id =  '$cal_id'";
?>