Problem with multiple joins - Printable Version

Problem with multiple joins - 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: Problem with multiple joins (/showthread.php?tid=22417)

Problem with multiple joins - El Forum - 09-08-2009

[eluser]JoshK[/eluser]
I'm new to Code Igniter, and so far love it. However, I'm having issues when using multiple join statements in my functions.

Example: this code doesn't work.

Code:
function get_records()

    {

        $this->db->from('events');

        $this->db->join('event_dates', 'event_dates.event_id = events.event_dates', 'LEFT');        

        $this->db->join('locations', 'locations.location_id = events.event_location', 'LEFT');

        $this->db->join('users', 'users.user_id = events.event_creator', 'LEFT');

        $this->db->join('categories', 'categories.category_id = events.event_category', 'LEFT');

        $query = $this->db->get();

        return $query->result();

    }

however, if I only have 3 joins it works:

Code:
function get_records()

    {

        $this->db->from('events');

        $this->db->join('locations', 'locations.location_id = events.event_location', 'LEFT');

        $this->db->join('users', 'users.user_id = events.event_creator', 'LEFT');

        $this->db->join('categories', 'categories.category_id = events.event_category', 'LEFT');

        $query = $this->db->get();

        return $query->result();

    }

I'm lost, can anyone spot the error in the first chunk of code?

And it only worked with 3 joins once I added the 'LEFT' to each of the joins. I can't find any answers.

Thanks in advance guys.

Problem with multiple joins - El Forum - 09-08-2009

[eluser]nzmike[/eluser]
Should it not be

Code:
$this->db->join('event_dates', 'event_dates.event_id = events.id', 'LEFT');

Problem with multiple joins - El Forum - 09-08-2009

[eluser]JoshK[/eluser]
Oops, yes you are right. I was using an extra column which I never needed, however it never fixed the issue, just made my database a little cleaner. Thanks for that.

What's happening is in my foreach statement I have a link

Code:
<h2><a >event_id?&gt;">&lt;?=$row->event_name?&gt;</a></h2>

All works perfect with 3 joins. If I add that final join (the 'event_dates' one) every foreach after the first doesn't include the

Code:
&lt;?=$row->event_id?&gt;

But thanks for the other correction, it's been implemented.

Problem with multiple joins - El Forum - 09-08-2009

[eluser]JoshK[/eluser]
ok, so it's not letting me display the html as it's written. Essentially, in the anchor tag I have included

Code:
&lt;?=$row->event_id?&gt;

in my foreach statement, the value is only returned for the first one, all other records the value is coming back empty. If I remove that join statement, it works perfectly.

Any ideas?

Problem with multiple joins - El Forum - 09-09-2009

[eluser]überfuzz[/eluser]
[quote author="JoshK" date="1252490912"]ok, so it's not letting me display the html as it's written. Essentially, in the anchor tag I have included

Code:
&lt;?=$row->event_id?&gt;

in my foreach statement, the value is only returned for the first one, all other records the value is coming back empty. If I remove that join statement, it works perfectly.

Any ideas?[/quote]How does the MySQL string look rendered by active records?

Problem with multiple joins - El Forum - 09-09-2009

[eluser]nzmike[/eluser]
If you've named the primary key in the events table event_id you might have a problem because now you're trying to select from two tables that share a column name. As überfuzz has mentioned it would be useful to see the MySQL string. Try using

Code:
$this->db->last_query();