Getting data from multiple tables

[eluser]Sion[/eluser]
I am having problem displaying data from multiple tables (3) first table has 5 fields (eventID*,
name,
time,
url,
date)
which linked with eventID to 2nd table with 5 fileds
(venuID*,
eventID,
address,
eventUrl,
contactinfo)

now the 3rd table has 4 fields with
event_dayID*,
eventID,
day,
time)

my problem is that eventID is not unique, means same eventID has multiple days (0=sunday, 1=monday...and so on)

Here is my query


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");
return $this->db->get();

Here is my view

<?php foreach($xml->result() as $entry): ?>

<item>
&lt;title&gt;&lt;?php echo ($entry->name); ?&gt;&lt;/title&gt;
&lt;link&gt;&lt;?php echo site_url('event/detail/' . $entry->eventID) ?&gt;&lt;/link&gt;
<guid>&lt;?php echo site_url('event/detail/' . $entry->eventID) ?&gt;</guid>
<dateBegin>&lt;?php echo ($entry->dateBegin); ?&gt;</dateBegin>
<startTime>&lt;?php echo ($entry->time); ?&gt;</startTime>
<address>&lt;?php echo ($entry->address); ?&gt;
<ticketURL>&lt;?php echo xml_convert($entry->url); ?&gt;</ticketURL>
<eventDay>&lt;?php echo ($entry->day); ?&gt;</eventDay>
</item>

&lt;?php endforeach; ?&gt;

It returns same event multiple times with different days in each one. I want to display one event with multiple days like <eventDay>0,1,2,3,4,5</eventDay> what do I need to do? Can anyone help?

I am not sure if I am clear with my problem..

[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.

[eluser]Sion[/eluser]
eventID in events and venue tables are unique but the eventID in the event_day table is not.

I ran the model and get Parse error: syntax error, unexpected T_FOREACH in xml_model.php on line 33

line 33. foreach ($dbres->result_array() as $key => $value) {
line 34. if (!defined($retarr[$dbres[$key]['eventID']])) {
...

[eluser]gtech[/eluser]
Hi I did say it was untested.. it was more a concept

the error your getting is because before line 33

$retarr = array() needs a ; on the end

Code:

$retarr = array();

I will edit the code in my original reply and add some missing ;

.. see

// [edit]

in the comments

[eluser]Sion[/eluser]
Hey GTech

I am a little frustrated and did even see the missing ;. I think we are on the right track though, I got the view page to load but there is no data rendered on the page not sure if I did something incorrectly. Hmmmm any other ideas? Use controller??

[eluser]gtech[/eluser]
put a

print_r($retarr);

before the return in the get_xml_data just to see if the arrays getting populated (dont forget to take it out when it works)

Also put a

&lt;?=print_r($xml);?&gt;

just before the foreach in the view... it should be the same as whats being returned in get_xml_data

[eluser]sholnay[/eluser]
So I've been going through this trying to adapt it to my code as I have a pretty similar scenario.

Or - maybe someone has a solution that they have used already.

I'm just setting up tags on posts - my schema is pretty simple:

Code:

entries
-------
|entry_id
|title
|body
|author
-------

entry_tag
-------
|id
|entry_id
|tag_id
-------

tags
-------
|id
|tag_id
|tag
-------

I have a sql statement that is returning all entries and the tags that are associated with the entries:

Code:

$this->db->select('entries.*');
$this->db->select('tags.tag');
$this->db->from('entries');
$this->db->join('entry_tags','entries.entry_id = entry_tags.entry_id','left');
$this->db->join('tags','tags.id = entry_tags.tag_id','left');
$this->db->orderby('entries.entry_id','desc');        

entry_id    title        body     author     tag
------------------------------------------------------
1           test title   body 1   auth       chicken
1           test title   body 1   auth       salt
1           test title   body 1   auth       pork

So, I want to return to my function one row with:

Code:

entry_id    title        body     author     tag
------------------------------------------------------
1           test title   body 1   auth       arr(chicken,salt,pork)

I went through gtech's code

Code:

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;

and adapted it to my code - as it seemed similar (maybe I am really missing something here though?)

on this line:

Code:

if (!defined($retarr[$dbres[$key]['eventID']])) {
        // [edit] add missing ;
        $retarr[$dbres[$key]['eventID']] = array();
      }

I kept getting "undefined 'eventID'" error in php...

I swear I have been racking my brain to try and figure out how I am not converting the code above to work in my situation - any help would be greatly appreciated.

[eluser]gtech[/eluser]
eventID does not exist in any of you database columns, so thats why you get the error.

your example is slightly different and there may be a better solution, I have adapted the code and put it below... its UNTESTED as I couldn't be bothered to set up the database. Its just an idea so you get the concept,

Use some echos to debug it, or print_r to display array contents (don't forget to take them out as this can result in a headers already sent error message). e.g . print_r($dbres->result_array());

My Untested code:

Code:

..
// print_r($dbres->result_array());

$retarr = array();
foreach ($dbres->result_array() as $key => $dbrow_array) {
  // echo "<br>KEY:".$key."<BR>";
  // print_r($dbrow_array);
  // echo "<br>";
  if (!defined($retarr[$dbrow_array['entry_id']])) {
    $retarr[$dbrow_array['entry_id']] = $dbrow_array;
    $retarr[$dbrow_array['entry_id']]['tags'] = array();
    $retarr[$dbrow_array['entry_id']]['tags'][] = $dbrow_array['tag'];
  } else {
    $retarr[$dbrow_array['entry_id']]['tags'][] = $dbrow_array['tag'];
  }
  // print_r($retarr);

}
// the idea is to return [<entry id>][<db field name>] .. containing the field value
//                       [<entry id>]['tags'][<num>] .. containing the tag name
return $retarr;

[eluser]sholnay[/eluser]
[quote author="gtech" date="1196282239"]eventID does not exist in any of you database columns, so thats why you get the error.

[/quote]

hehe - thanks - I know, I modified my code to fit, I wasnt using eventID for my own code - I thought it would be easier to explain if I just left all the original code on here.

[quote author="gtech" date="1196282239"]
your example is slightly different and there may be a better solution, I have adapted the code and put it below... its UNTESTED as I couldn't be bothered to set up the database. Its just an idea so you get the concept,

Use some echos to debug it, or print_r to display array contents (don't forget to take them out as this can result in a headers already sent error message). e.g . print_r($dbres->result_array());

My Untested code:

Code:

..
// print_r($dbres->result_array());

$retarr = array();
foreach ($dbres->result_array() as $key => $dbrow_array) {
  // echo "<br>KEY:".$key."<BR>";
  // print_r($dbrow_array);
  // echo "<br>";
  if (!defined($retarr[$dbrow_array['entry_id']])) {
    $retarr[$dbrow_array['entry_id']] = $dbrow_array;
    $retarr[$dbrow_array['entry_id']]['tags'] = array();
    $retarr[$dbrow_array['entry_id']]['tags'][] = $dbrow_array['tag'];
  } else {
    $retarr[$dbrow_array['entry_id']]['tags'][] = $dbrow_array['tag'];
  }
  // print_r($retarr);

}
// the idea is to return [<entry id>][<db field name>] .. containing the field value
//                       [<entry id>]['tags'][<num>] .. containing the tag name
return $retarr;

[/quote]

Thank you - I will check this out when I get some time!