Getting data from multiple tables

Getting data from multiple tables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Getting data from multiple tables (/showthread.php?tid=4225)

Getting data from multiple tables - El Forum - 11-13-2007

[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>
<title><?php echo ($entry->name); ?></title>
<link><?php echo site_url('event/detail/' . $entry->eventID) ?></link>
<guid><?php echo site_url('event/detail/' . $entry->eventID) ?></guid>
<dateBegin><?php echo ($entry->dateBegin); ?></dateBegin>
<startTime><?php echo ($entry->time); ?></startTime>
<address><?php echo ($entry->address); ?>
<ticketURL><?php echo xml_convert($entry->url); ?></ticketURL>
<eventDay><?php echo ($entry->day); ?></eventDay>
</item>

<?php endforeach; ?>

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

Getting data from multiple tables - El Forum - 11-14-2007

[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:
&lt;?php foreach($xml as $eventid => $eventrows_array): ?&gt;

  echo "EVENT =: ".$eventid."BR";

  &lt;?php foreach($eventrows_array as $key => $dbrow): ?&gt;

     echo "DAY = $dbrow['day']"

  &lt;?php endforeach; ?&gt; 

&lt;?php endforeach; ?&gt;

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.

Getting data from multiple tables - El Forum - 11-14-2007

[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']])) {
...

Getting data from multiple tables - El Forum - 11-14-2007

[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

Getting data from multiple tables - El Forum - 11-15-2007

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

Getting data from multiple tables - El Forum - 11-15-2007

[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

<?=print_r($xml);?>

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

Getting data from multiple tables - El Forum - 11-27-2007

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

Getting data from multiple tables - El Forum - 11-28-2007

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

Getting data from multiple tables - El Forum - 11-28-2007

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