Getting data from multiple tables

[eluser]gtech[/eluser]
if you wanted to keep the database as is you could do somthing like (ITS UNTESTSED, just to give you a concept)

Code:

..
  function get_xml_data()
  {
    $this->db->select();
    $this->db->from('venue');
    $this->db->join('event','event.venueID = venue.venueID');
    $this->db->join('event_day','event.eventID = event_day.eventID','right');
    $this->db->where('event.dateEnd >= current_date');
    $this->db->orderby("eventType","asc");
    $dbres =  $this->db->get();

    // [edit] add missing ;
    $retarr = array();
    // build up an events array
    // [<eventid>][<num>][<sql fieldname from join>]

    foreach ($dbres->result_array() as $key => $value) {
      if (!defined($retarr[$dbres[$key]['eventID']])) {
        // [edit] add missing ;
        $retarr[$dbres[$key]['eventID']] = array();
      }
      // [] will append to the array i.e [<event id>][0], [<event id>][1] etc
      $retarr[$dbres[$key]['eventID']][] = $dbres[$key];
    }
    return $retarr;  
  }
?&gt;

then in the view

Code:

<?php foreach($xml as $eventid => $eventrows_array): ?>
  echo "EVENT =: ".$eventid."BR";
  <?php foreach($eventrows_array as $key => $dbrow): ?>
     echo "DAY = $dbrow['day']"
  <?php endforeach; ?>
<?php endforeach; ?>

Is eventid unique in the events table? if not then maybee the database design needs a bit of thought.

If eventID is not unique in the events table, is it because you have a many2many relationship between events & venues (e.g. a venue can have many events, and an event can have many venues) or between events & event_days?

If this is the case then maybe you should have a lookup table between the many2many relationship so you can keep the eventID unique in the events table to save you having to replicate data.