rjcode rjcode - 17 days ago 10
PHP Question

Query data from sql table where reference is in another table

I am using CodeIgniter and trying to query data from mysql.

Scenario -- Logged on user is Manager and wants to pull data for his team..

Explanation --

e_users table

|user_id | name | manager_id
---------------------------------
|1 | test | 10
|2 | another | 1
|3 | testX | 1
|4 | testY | 2
|5 | testX | 2


e_details table

|det_id | detail | user_id
---------------------------------
|1 | sdfs | 1
|2 | xcffgdf | 2
|3 | sdfsd | 3
|4 | etret | 5
|5 | cvbcvb | 5


Now when user_id 1 is logged as manager, he should get data of det_id -2,3 only,

e_details table have data and i want to query only those data of users where whose manager is currently logged on. Means manager will pull data of users from e_details table.

e_details table have user_id, detail_comment, details_date...

and e_users have user_id, manager_id , ...

I tried below function, but i could not get required data

$user_id = $this->session->userdata('user_id')
public function get_method($user_id)
{
$this->db->join('e_users', 'e_users.manager_id = e_details.user_id','INNER');
$this->db->where('e_details.user_id',$user_id);
return $this->db->get('e_details')->result_array();
}


I think i need to join two tables and then search with common key value?

Thanks,

Answer

Well If I am getting it correct you have missed the select statement. This would help you

 <?php 
    public function get_method($user_id){
        $query = $this->db->select('e_users.*, e_details.*')
                ->from('e_users')
                ->join('e_details', 'e_users.user_id = e_details.user_id', 'inner')
                ->where('e_users.manager_id',$user_id);
                ->get();
        if($query -> num_rows() > 0){
            $details_array = $query2->result();
            $response['details_array'] = $details_array;
            return $response;
        }else{
            return false;
        }
    }
?>