CodeIgniter mySQL 2 table LEFT OUTER JOIN

Question

everyone.

I'm using CodeIgniter, and I'm not getting results for this query:

    $this->load->database();

    $this->db->select('*');
    $this->db->from('users');
    $this->db->join('show_guides', 'show_guides.user_id = users.user_id');
    $this->db->where('users.user_id', $user_id['user_id'], 'left outer');

    $query = $this->db->get();
    foreach ($query->result_array() as $row) {
        $results = $row;
    }

The 'users' table will always have results, but sometimes the user won't have a row in the 'show_guides' table. When the 'show_guides' table doesn't have results, the query doesn't return results from the 'users' table.

$row doesn't exist when 'show_guides' produces no results. I only get results when both tables have data with the matching users.user_id .

Any suggestions?

Thanks!

EDIT To avoid any confusion, this query gives me the results I need, but I want to use the CodeIgniter db objects.

SELECT u.*,s.* 
FROM users u
LEFT OUTER JOIN show_guides s ON u.user_id = s.user_id
WHERE u.user_id = 155;

This gives results even if show_guides is empty.

dispake · Accepted Answer · 2011-11-10 17:14:07Z

21

You want to put your 'left outer' in the join() function, not the where()

$this->db->join('show_guides', 'show_guides.user_id = users.user_id', 'left outer');

answered Nov 10, 2011 at 17:14

dispake

3,3372 gold badges21 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CodeIgniter mySQL 2 table LEFT OUTER JOIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related