codeigniter active record left join

Question

I have 3 mysql tables.

Table 1 user   id | name    Table 2 emails   id | email    Table 3 user_email   user_id | email_id   

I have no experience in query multi tables.
Using codeigniter active record, i want to find out the user email address based on the user id, pls advise if the below code is correct ?

$CI->db->select('email'); $CI->db->from('emails'); $CI->db->where('id', $userid); $CI->db->join('user_email', 'user_email.user_id = emails.id', 'left'); $query = $CI->db->get();   

Answer 1

You have wrong where clause you need to compare user_id from your table ,you are comparing the id of email to the provided $user_id

$CI->db->select('email'); $CI->db->from('emails'); $CI->db->where('user_id', $userid); $CI->db->join('user_email', 'user_email.user_id = emails.id', 'left'); $query = $CI->db->get();  

A more useful way is to give aliases to your tables so the tables with same columns will not have any confusion

$CI->db->select('e.email'); $CI->db->from('emails e'); $CI->db->join('user_email ue', 'ue.user_id = e.id', 'left'); $CI->db->where('ue.user_id', $userid); $query = $CI->db->get();