CODEWITHSUNDEEP
phpmysqlcodeigniter-2
user2450349
user2450349

Reputation: 51

MySQL Database Query - Codeigniter

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

Upvotes: 0

Views: 109

Answers (1)

bekt
bekt

Reputation: 597

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>

**

Upvotes: 2

Related Questions