PHP Multi-Day Event Calendar

[szkoda13]

I have the below code which works great for a booking calendar I am working on.

 

The problem I am having is that I can't work out a way to get bookings that last more than one day to be printed in each div that the event is active for. Currently it only uses the start date to put the event in the correct DIV.

 

One of the rows in my booking table is a "booking period" which is the number of days the booking is active for - I was thinking adding some sort of if statement that says:

 

If (booking_period > 1) {

 

$calendar.=  "code to display booking on each day that it is active";

 

}

 

but that's about as far as I get. Maybe there's an easier way that I'm missing? Can anyone give me a nudge in the right direction?

 

P.S. I know I'm using an insecure way of extracting data from a MYSQL database but it's only accessed locally so security is not an issue.

<?php



function draw_calendar($month,$year,$events = array()){


$month = (isset($_GET['month']) ? $_GET['month'] : date('m'));

    $year = (isset($_GET['year']) ? $_GET['year'] : date('Y'));


if($month < 10)

$month = '0'.$month;



$calendar = '<table cellpadding="0" cellspacing="0" class="calendar">';



$headings = array('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday');

$calendar.= '<tr class="calendar-row"><td class="calendar-day-head">'.implode('</td><td class="calendar-day-head">',$headings).'</td></tr>';



$running_day = date('w',mktime(0,0,0,$month,0,$year));

$days_in_month = date('t',mktime(0,0,0,$month,1,$year));

$days_in_this_week = 1;

$day_counter = 0;

$dates_array = array();



$calendar.= '<tr class="calendar-row">';



for($x = 0; $x < $running_day; $x++):

$calendar.= '<td class="calendar-day-np"> </td>';

$days_in_this_week++;

endfor;



for($list_day = 1; $list_day <= $days_in_month; $list_day++):

$calendar.= '<td class="calendar-day"><div style="position:relative;height:100px;">';

/* add in the day number */

$calendar.= '<div class="day-number">'.$list_day.'</div>';


$event_day = $year.'-'.$month.'-'.$list_day;

if(isset($events[$event_day])) {

foreach($events[$event_day] as $event) {

$calendar.= '<div class="event">'.$event['pet_name']. '<br>'. $event['booking_period']. '</div>';

}

}

else {

$calendar.= str_repeat('<p> </p>',2);

}

$calendar.= '</div></td>';

if($running_day == 6):

$calendar.= '</tr>';

if(($day_counter+1) != $days_in_month):

$calendar.= '<tr class="calendar-row">';

endif;

$running_day = -1;

$days_in_this_week = 0;

endif;

$days_in_this_week++; $running_day++; $day_counter++;

endfor;



if($days_in_this_week < :

for($x = 1; $x <= (8 - $days_in_this_week); $x++):

$calendar.= '<td class="calendar-day-np"> </td>';

endfor;

endif;



$calendar.= '</tr>';


$calendar.= '</table>';


/** DEBUG **/

$calendar = str_replace('</td>','</td>'."\n",$calendar);

$calendar = str_replace('</tr>','</tr>'."\n",$calendar);



return $calendar;

}



include 'functions/connect.php';

$month = str_pad($month,2,"0", STR_PAD_LEFT);

$events = array();

$query = "SELECT bb_Clients.pet_name, bb_Bookings.booking_period, DATE_FORMAT(start_date,'%Y-%m-%e') AS start_date FROM bb_Bookings INNER JOIN bb_Clients ON bb_Bookings.client_id=bb_Clients.client_id WHERE start_date LIKE '$year-$month%'";

$result = mysql_query($query) or die('cannot get results!');

while($row = mysql_fetch_assoc($result)) {

$events[$row['start_date']][] = $row;

}


$monthName = date("F", mktime(0, 0, 0, $month, 10));



echo '<h2>'. $monthName .' '. $year .'</h2>';

echo draw_calendar($month,$year,$events);

?>

 

[Barand]

Create an array where the keys are those dates that you want to display.

 

Read your events and place them in the array according to their date. Multiday events will go into multiple array elements.

 

Now output the calendar from the array.

[szkoda13]

Hi, thanks for the response.

 

What MYSQL query would I use to pull out every date that a particular event is active? I only have start and end date rows in my database?

[Barand]

A query that gets the data you need to produce the output you want. I don't now what your data looks like or what your final results should look like, so how do you expect me tell you what query you need? Telepathy is not one of my talents.

[szkoda13]

What I'm saying is how can I create a query to pull data from the table when that data doesn't exist as a value in the table?
 

In my bookings table the rows I have are:

 

booking ID

client ID

start date

end date

booking period

booking price

 

at the moment I am just pulling out the start dates and using them in my array and outputting them to the relevant date in the calendar.

 

I can't see how I would pull out the dates between the start date and end date of the booking?

[Barand]

What is "booking period" column?

 

Assuming you want to output a calendar for the period DateA to DateB then you want those events that start on or before DateB and end on or after DateA.

SELECT booking_id
  , client_id
  , ...
FROM bookings
WHERE start_date <= 'DateB' AND end_date >= 'DateA'
ORDER BY start_date;

[szkoda13]

The booking period column stores the length of the booking in a varchar format (which in hindsight was a mistake although I could convert to int if necessary). This is automatically worked out from the start and end dates.

 

Maybe I'm not explaining myself very well but the code I pasted originally outputs a monthly calendar which are split into DIV's for each day of the month.

 

The idea is that whoever looks at the calendar can see from a glance what bookings there are for the month (which it does currently). What it doesn't do is display each booking on every day that it is active - it is only displaying the booking on the start date of that event.

 

Looking at your MYSQL statement above I don't think this would work as 'DateB' and 'DateA' are not static and they are not values stored in the table. I need to somehow pull out the dates between the start date and the end date and store them in the array that then get's outputted to the calendar.

 

I hope this makes sense?

[Barand]

It makes no sense at all.

 

As you said, the booking period is calculated from the start and end dates, so why store it? Don't store derived data, calculate when required.

 

DateA and DateB will not be static. As stated, they will be the first and last dates of whatever is the required period, in this case the month. You would bind them as parameters to a prepared query.

 

I told you in my initial post how to show all the dates for an event - store in an array.

 

BTW I would have expected something in your bookings table to indicate which event, such as an event_id.