MySQL Database Query - Codeigniter
- by user2450349
I am building an application with Codeigniter and need some help with a DB query.
I have a table called users with the following fields:
user_id, user_name, user_password, user_email, user_role, user_manager_id
In my app, I pull all records from the user table using the following:
function get_clients()
{
$this->db->select('*');
$this->db->where('user_role', 'client');
$this->db->order_by("user_name", "Asc");
$query = $this->db->get("users");
return $query->result_array();
}
This works as expected, however when I display the results in the view, I also want to display a new column called Manager which will display the managers user_name field.
The user_manager_id is the id of the user from the same table.
Im guessing you can create an outer join on the same table but not sure.
In the view, I am displaying the returned info as follows:
<table class="table table-striped" id="zero-configuration">
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Manager</th>
</tr>
</thead>
<tbody>
<?php
foreach($clients as $row)
{
?>
<tr>
<td><?php echo $row['user_name']; ?> (<?php echo $row['user_username']; ?>)</td>
<td><?php echo $row['user_email']; ?></td>
<td><?php echo $row['???']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
Any idea of how I can form the query and display the manager name is the view?
Example:
user_id user_name user_password user_email user_role user_manager_id
1 Ollie adjjk34jcd [email protected] client null
2 James djklsdfsdjk [email protected] client 1
When i query the database, i want to display results like this:
Ollie [email protected]
James [email protected] Ollie
