CiviCRM Community Forums (archive)

Is there anyway to display a list of events and their dates plus a list of contribution projects/pages on another page on my drupal site? so that anon users can just see a list?

I am using the Upcoming CiviEvents block provided here.

I'm not sure how to add location details to my display.  From looking at the events db table, it appears the location details are not in that table... and if I var_dump the $myEvents array in the Upcoming CiviEvents block, there is nothing in the array for location name, street address, etc.  I'm not sure what table those things are pulled from, and how best to access that data from this block.

Thanks in advance for any help with this.
Ryan

yes, version 1.9.

Initially I'm not sure why it needs to be a left join.  Unless I'm missing something, it seems like a simple join would work, like so...

$result = db_query('SELECT * FROM civicrm_location AS l, civicrm_address AS a WHERE l.entity_id = $eventid AND l.entity_table = \'civicrm_event\' AND l.id = a.location_id');
while ($row = db_fetch_object($result)) {
...
}

What is the best way to query the civicrm database in a Drupal block?  The above doesn't work because db_query() queries the drupal database.  I have a separate database for civicrm.

My plan is to put the above query in the foreach loop in the Upcoming CiviEvents block.

Thanks.

awesome... thanks.  Is there a php function or drupal function for getting the string value of a state_province_id?

Here's the whole block as of now...

<?php

function cmp_date($a,$b) {
  if ($a['start_date'] > $b['start_date']) return 1;
  if ($a['start_date'] < $b['start_date']) return -1;
  return 0;
}

if (module_exists('civicrm')) {
  civicrm_initialize(TRUE);
  require_once 'api/v2/Event.php';
  // This example limits listing to public and active events
  $params = array ('is_public'     => 1, 'is_active'     => 1);
  $myEvents = civicrm_event_search( $params );
  if ($myEvents) {
    $count = 0;
    $last = '';
    usort($myEvents,'cmp_date');
    foreach ($myEvents as $event) {
      $eventid = $event['id'];
      $the_city = '';
      $the_state_province_id = '';
      
      civicrm_initialize( );
      $query = 'SELECT * FROM civicrm_location AS l, civicrm_address AS a WHERE l.entity_id = '.$eventid.' AND l.entity_table = \'civicrm_event\' AND l.id = a.location_id';
      require_once 'CRM/Core/DAO.php';
      $the_event = CRM_Core_DAO::executeQuery( $query, CRM_Core_DAO::$_nullArray );
  while ( $the_event->fetch( ) ) {
          $the_location = $the_event->name;
          if (!empty($the_event->city)) {
$the_city = ', ' . $the_event->city;
}
  if (!empty($the_event->city)) {
$the_state_province_id = ', ' . $the_event->state_province_id;
}   
  }

      $now = date('Y-m-d H:i:s');
      if ($now > $event['start_date']) continue;
      $startdate = date('F j, Y',strtotime($event['start_date']));
      $enddate = date('F j, Y',strtotime($event['end_date']));
             
      list($title_place, $title_desc) = split(":",$event['title'],2);
      if ($last != $startdate) {
        $display = '<div class="pod"><p class="date">'.$startdate.'</p>';
      }
      $display .= '<p class="event_name">' . l($title_place.' '.$title_desc, 'civicrm/event/info', array(), 'reset=1&id='.$event['id']).'<br /><strong>'.$the_location.$the_city.$the_state_province_id.'</strong></p></div>';
      echo $display;
      $count++;
      $last = $startdate;
      if ($count > 8) break;
// this limits the number of events to 8 - put in whatever number suits you
    }
    if ($count > 0) {

    } else {
      echo 'No events found.';
    }
  } else {
    echo 'No events found.';
  }
}
?>