PHP Calendar running from MySQL database

[dmirsch]

I am trying to create a calendar of Events for my company's website. When I search for this type of calendar, I can find code that runs the calendar from one Events table. However, we could have an Event that has multiple performances. (For example, Lily Tomlin could be performing at the Atwood Concert Hall in Anchorage on 09/09/11 and at the Vagabond Blues in Palmer on 09/10/11.) So we have two tables to draw the information from: Events and Performance

 

I can successfully create a MySQL query (see below) with PHP coding that will make a listing (as demostrated on our website http://www.centertix.net/).

"SELECT Events.PublishDate, Events.EventOnSaleDate, Events.BldgContractDate, Events.SetUpComplete, Events.EventTitle, Performance.PerfID, Performance.startDateTime,
Performance.endDateTime, Performance.PerfType, venues.VenueName, venues.VenueCode FROM (Performance RIGHT JOIN Events ON Performance.EventID = Events.EventID)
LEFT JOIN venues ON Performance.VenueCode = venues.VenueCode WHERE Events.BldgContractDate Is Not Null AND Events.SetUpComplete Is Not Null AND Performance.PerfType='Public Event'"

However, I'm not advanced enough to create the if-clause or while-loop to check to see if the Performance.startDate = the calendar's date and then place the info into the appropriate calendar grid box. I think have determined where it should be place in my PHP code, but need help getting the calendar to work.

 

Here's the code where I think it should go:

         for($list_day = 1; $list_day <= $days_in_month; $list_day++):
		if(($list_day == date("j",mktime(0,0,0,$this->month))) &&
		(date("F") == date("F",mktime(0,0,0,$this->month))) &&
		(date("Y") == date("Y",mktime(0,0,0,$this->month,$list_day,$this->year)))) {
                 $this->calendar.= '<td class="'. $this->style .'-current-day">';
             } else {    
                 if(($running_day == "0") || ($running_day == "6")) {
                     $this->calendar.= '<td class="'. $this->style .'-weekend-day">';
                 } else {
                     $this->calendar.= '<td class="'. $this->style .'-day">';    
                 }
             }
            
                 /* add in the day number */
                 $this->calendar.= '<div class="'. $this->style .'-day-number">'.$list_day.'</div>';
    
                 /** QUERY THE DATABASE FOR AN ENTRY FOR THIS DAY !!  IF MATCHES FOUND, PRINT THEM !! **/
                 $this->calendar.= '<div class="'. $this->style .'-text"><a href="">PERFORMANCE INFO</a></div>'. "\n";
                 /** QUERY THE DATABASE FOR AN ENTRY FOR THIS DAY !!  IF NO MATCHES FOUND, PRINT NOTHING !! **/
                 $this->calendar.= str_repeat('<p> </p>',2);
                
             $this->calendar.= '</td>';
             if($running_day == 6):
                 $this->calendar.= '</tr>';
                 if(($day_counter+1) != $days_in_month):
                     $this->calendar.= '<tr class="'. $this->style .'-row">';
                 endif;
                 $running_day = -1;
                 $days_in_this_week = 0;
             endif;
             $days_in_this_week++; $running_day++; $day_counter++;
         endfor;

 

Please help me plug the calendar in with the appropriate events! If you would like to see how far I have gotten, you can check out this link: http://www.myalaskacenter.com/calendars/calendarSample2.php

[fenway]

You shouldn't be running one query per day.

[dmirsch]

OK, so I found a similar calendar, but it does NOT run the query each day; it's just once. The following is run for each day:

function draw_calendar($month,$year,$events = array()){
//CODING REMOVED that creates the beginning of the table for the calendar; what follows is the code for each DAY of the month

$event_day = $year.'-'.$month.'-'.$list_day;
      if(isset($events[$event_day])) { // <THIS IS ALWAYS FALSE -- WHY??? It should be set when there is a Performance on the $event_day
        foreach($events[$event_day] as $event) {
          $calendar.= '<div class="event">'.$row['EventTitle'].'</div>';
		//^this is where we put the performance info
        }
      } else {
        $calendar.= str_repeat('<p> </p>',2);
		//^this is where we have blanks if an event_day <> list_day
      }

 

So in the coding above, I cannot ever get $events[$event_day] to be SET so there only appears blank squares.

[xyph]

There's a lot of information there to process.

 

Is Performance.startDate a MySQL datetime column?

 

If so, you can also return PARTS of those datetime's using DAY( Performance.startDate ), MONTH( Performance.startDate ) and YEAR( Performance.startDate )

 

From there, it's very easy to check if a given performance is in the calendar month. You can even use that in the WHERE clause to only grab performances for a given month.

[dmirsch]

So then I put the WHERE clause into the coding where the

isset($events[$event_day])

is currently, right? That would be my guess. Something that says

WHERE $list_day == $row['Performance.startDateTime']

 

Do you know why the

isset($events[$event_day])

would continue to be false in the previous code?

[Psycho]

You should run the query each time the page is accessed - not once each day - as Fenway already stated. Not sure why you need two tables (one for events and one for performances). Is there different data you need to store for the two types? If no, then just use one table with a "type" column to identify if it is an event or a performance. Then you only need to query one table. Or, if the two types of activities share some data, but not all. You could still do the same thing. Have one table for the common data with a column to identify the type, then two associative tables to define the extended information for Events or Performances. Then, you still have one main table to query. Or, you could stay with two completely separate tables and use a UNION clause to run one query to get all the records.

 

 

[dmirsch]

I do run the query once per page. In response as to why we need two tables: as stated previously,

we could have an Event that has multiple performances. (For example, Lily Tomlin could be performing at the Atwood Concert Hall in Anchorage on 09/09/11 and at the Vagabond Blues in Palmer on 09/10/11.) So we have two tables to draw the information from: Events and Performance.

So "Lily Tomlin" is the event with two Performances. That is the reason for the two separate tables. I do not have a problem with the query, it lists out my separate performances linked to one event. You can see this at http://www.myalaskacenter.com/calendars/calendarSample3.php.

 

My problem is that I cannot get the PerformanceDate (startDateTime) to correspond with the gridded calendar date (list_day) in my PHP code. I also do not understand why

isset($events[$event_day])

is always coming up empty/FALSE.

[Psycho]

OK, I misunderstood your events vs. performances. So, every event will have an associated performance (or performances). As to

I also do not understand why

isset($events[$event_day])

is always coming up empty/FALSE.

 

In your function, you are setting $events=array() in the function header if no value is passed to the function draw_calendar() for that parameter. You need to validate that the value passed to the function has what you expect it to have. Also, you define $event_day as

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

$year and $month are supposed to be passed to the function, but I don't see where $list_day is defined.

 

You need to simply add some debugging lines to your code to see what is what. Add this after the line where you define $event_day

 

echo "Month: {$month}<br>\n";
echo "Year: {$year}<br>\n";
echo "Event Day: {$vent_day}<br>\n";
echo "Events:<pre>" . print_r($events) . "</pre>";

[dmirsch]

I have $list_day defined in the coding I removed previously. It falls just before $event_day = $year.'-'.$month.'-'.$list_day;

  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>';

That is the only other reference to $list_day. It is not a typical definition of a variable as I have seen it before, but I thought it was defining $list_day. Is it not?

 

I do have $month and $year defined; see http://www.myalaskacenter.com/calendars/calendarSample3.php for proof of that. I tried to define $event_day, but could not figure out where to put that code. Do I put it where I expect the performances to be? Or do I put it before/after calendar?

[Psycho]

. . .  could not figure out where to put that code. Do I put it where I expect the performances to be? Or do I put it before/after calendar?

 

C'mon, this isn't that difficult a concept is it? If you can't learn to debug code you will never get anywhere. That debugging code I gave you simply echo's out the values of the variables which are causing your problem. So, put those lines after where those variables are defined ($event_day is the last one defined - so it goes after that) and before the lines where you are having the problem. That would be before the if() statement that is always failing for you. You should then see where the cause of the problem is.

[dmirsch]

OK, THANK YOU mjdamato!!!!

 

I do have it working now! See http://www.myalaskacenter.com/calendars/calendarSample3.php. However, there are events that are happening on some days that do not show up on the calendar. Perhaps it has something to do with the two-digit days as it seems that anything happening before the 10th of the month is not showing up.

 

The array display is on the following page: http://www.myalaskacenter.com/calendars/calendarBroken.php. From the array you can see that the performances prior to the 10th do show up within the array. So I'll keep digging through my code to see why this is happening, but it it's self-evident to you, please let me know my problem.

[Psycho]

I checked your page and it looks like you resolved the problem with the events priot to the 10h. Glad you got it worked out.