Problem with multiple joins

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

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

Code:

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

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

<?=$row->event_id?>

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

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

<?=$row->event_id?>

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?

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

<?=$row->event_id?>

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?

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