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.

Answer 1

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');