Code Dictionary Signature

Question

I want to display the user table and users_profiles table in 1 table: I want to link them so that usrpID = usrID,

Before this process, I tried to display only the user table using this code, and it works fine.

Controller:

$data['query'] = $this->db->query('SELECT * FROM users_profiles'); $this->load->view('users/users_view',$data); 

View:

 <?php foreach($query->result_array() as $row): ?> <tr class="even gradeC"> <td><?php echo $row['usrID']</td> <td><?php echo $row['usrName'];?></td> </tr> <? endforeach; ?> 

but when I try to join two tables, it returns me an error: this is my code

 $this->db->select('users.usrID, users_profiles.usrpID'); $this->db->from('users', 'users_profiles'); $this->db->join('users', 'users.usrID = users_profiles.usrpID'); $result = $this->db->get(); 

The user table has fields such as username, password, etc., and each user has his own profile in the users_profiles table

  users users_profiles 

EDIT I tried to select the fields, but when I tried this

 <td><?php echo $row['usrID'];?></td> <td><?php echo $row['usrName'];?></td> <td><?php echo $row['usrpFirstName'].' '.$row['usrpLastName'];?></td> <td><?php echo $row['usrpBday'];?></td> <td><?php echo $row['usrpSex'];?></td> <td><?php echo $row['usrpAddress'];?></td> 

he returns me the first value in user profiles in which he should not

Answer 1

Table

users was in both the from and join functions, so you merged 3 tables: users , users and users_profiles 2 first have the same name -> table of unique errors / aliases.

Try this (connecting [ users in from ] with [ users_profiles in join ]):

 $this->db->select('users.usrID, users_profiles.usrpID') ->from('users') ->join('users_profiles', 'users.usrID = users_profiles.usrpID'); $result = $this->db->get(); 

EDIT:

Example:

To get the users_profiles userpNick column:

 $this->db->select('users.usrID, users_profiles.userpNick') ->from('users') ->join('users_profiles', 'users.usrID = users_profiles.usrpID'); $query = $this->db->get(); 

View:

 <?php foreach($query->result() as $row): ?> <tr class="even gradeC"> <td><?php echo $row->usrID</td> <td><?php echo $row->userpNick;?></td> </tr> <? endforeach; ?> 

Answer 2

You can reference this example of a join result between the employee table and address.

 function getEmployees(){ $this->db->select("trn_employee.EMPLOYEE_ID,trn_employee.FIRST_NAME,trn_employee.LAST_NAME,trn_employee.EMAIL,trn_address.ADDRESS_LINE,trn_address.CITY"); $this->db->from('trn_employee'); $this->db->join('trn_address', 'trn_address.employee_id = trn_employee.employee_id'); $query = $this->db->get(); return $query->result(); }