JOIN a MySQL table to itself with CodeIgniter active record syntax

Question

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

bekt · Accepted Answer · 2014-06-03 03:58:28Z

3

You can use $this->db->join()

controller

function get_clients()
{   
    $this->db->select('u1.* , u2.user_name as manager_user_name');
    $this->db->where('u1.user_role', 'client');
    $this->db->join('users u2 ', 'u2.user_id = u1.user_manager_id', 'left outer'); 
    $this->db->order_by("u1.user_name", "Asc");
    $query = $this->db->get("users u1");
    return $query->result_array();
}

view

<td><?php echo $row['manager_user_name']; ?></td>

**

edited Jun 3, 2014 at 3:58

answered Jun 3, 2014 at 2:05

bekt

6075 silver badges16 bronze badges

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

5 Comments

Dan Over a year ago

I think you will want to move order_by below join

bekt Over a year ago

haha. sorry @dan maybe i forgot. but i think it is no problem. because query will be executed in in $this->db->get("users"); #cmiiw

user2450349 Over a year ago

Sorry guys, maybe i didnt make this clear. I only have the one table.Each user has an id and a manager id.

bekt Over a year ago

you can identify one table as different two table with table alias name. and change the join to outer join be $this->db->join('users u2 ', 'u2.user_id = u1.user_manager_id', 'left outer');

mickmackusa Apr 11 at 20:08

@Dan With active record methods, it doesn't matter the order of the method calls. You can call select() after order_by() if you like, but all of thequery building calls must be written before the query executing method call.

Collectives™ on Stack Overflow

JOIN a MySQL table to itself with CodeIgniter active record syntax

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related