Making sure you got the Events entered correctly (EoL D5.x)

Current status of Event module and all information here.

The Event module was end of life at Drupal 5.x and is not in current development. Please do not read further if you are working with Drupal 6,7, or 8.

Information referring to Drupal 5.x and below only

As a recent convert to the Event family, and hitting a small bug (that has been fixed now), I found it messy to make sure I had the events entered without weird starting/ending times or the right taxonomy terms or the right durations. So I decided to check up on myself and created this little snippet to show all my events in a little more concise way. This is just a simple table that summarizes all those fields. Feel free to use it.

Note: This snippet is for 5.x-1.x of Event.

<?php
  $header = array('Title', 'Type', 'Term', 'Start', 'End', 'Length');
  $rows = array();
  $results = db_query("SELECT n.nid, n.title, n.type, td.name AS term, e.event_start AS start, e.event_end AS end FROM {node} n INNER JOIN {event} e ON e.nid = n.nid LEFT JOIN {term_node} tn ON tn.nid = n.nid LEFT JOIN {term_data} td ON td.tid = tn.tid ORDER BY start");
  while ($node = db_fetch_object($results)) {
    if ($node->end > $node->start) {
      $duration = $node->end - $node->start;
      if ($duration == 86340) { $duration = 'all day';}
      else { $duration = format_interval($node->end - $node->start, 2); }
     }
    else { $duration = NULL; }
    $rows[] = array(l($node->title, 'node/'. $node->nid .'/edit'),
                              $node->type,
                              $node->term,
                              format_date($node->start),
                              $node->end == $node->start ? NULL : format_date($node->end),
                              $duration,
                       );
   } /* end while */
  return theme_table($header, $rows);
?>

Testing 5.x-2.x-dev

While the above works with event 5.x-1.x, If you have upgraded to 5.x-2.x-dev you will find it does not display your data correctly since the column types have changed. Here is something I wrote to do the equivalent. My test is against a PostgreSQL database, but I believe the query should be generic.

As my upgrade seems to have broken a number of things with events, I cannot be certain that the code below displays events with the same intent as the module, particularly with reference to denoting all day events.

<?php
  $header = array('Title', 'Type', 'Term', 'Start', 'End', 'Length');
  $rows = array();
  $results = db_query("SELECT n.nid, n.title, n.type, td.name AS term, e.event_start AS start, e.event_end AS end, e.event_end-e.event_start as duration, to_date(to_char(e.event_end,'YYYY-MM-DD'),'YYYY-MM-DD')-to_date(to_char(e.event_start,'YYYY-MM-DD'),'YYYY-MM-DD')+1 as day_duration, e.has_time, e.has_end_date FROM {node} n INNER JOIN {event} e ON e.nid = n.nid LEFT JOIN {term_node} tn ON tn.nid = n.nid LEFT JOIN {term_data} td ON td.tid = tn.tid ORDER BY start");
  while ($node = db_fetch_object($results)) {
    $duration = $node->duration;
    $start = $node->has_time ? $node->start : substr($node->start, 0, 10);
    $end = $node->has_time ? $node->end : substr($node->end, 0, 10);
    $end = $node->has_end_date ? $end : '';
    $duration = $node->has_time ? ($node->has_end_date ? $duration : 'none') : ($node->has_end_date ? $node->day_duration : 'all day');
    if (preg_match("/^\d+$/", $duration)) {
      $duration = $duration == 1 ? "$duration day" : "$duration days";
    }
    # $duration = $node->has_end_date ? ($node->has_time ? $duration : $node->day_duration) : 'all day';
    $rows[] = array(l($node->title, 'node/'. $node->nid .'/edit'),
                     $node->type,
                     $node->term,
                     $start,
                     $end,
                     $duration,
                   );
   }
  return theme_table($header, $rows);
?>