Codeigniter pass array from controller to model, But not returning a matching result

Question

Im trying to send an array of data from controller to model. but when I print_r on controller, I am not getting the matching "employee_code" for respective "used_id".

My Controller

Controller:

    $get_userid = $this->report->get_users_from_manager_id($this->session_user_id);
    $data["permit_user_id"] = $get_userid;
    foreach($get_userid as $user){
    $data["get_employee_code"] = $this->report->get_user_employee_code($user['user_id']);
    }

My model:

Model:

 /**Get User_id From the Manager id */
public function get_users_from_manager_id($manager_d){
    // $output = array();
    $sql = 'SELECT * FROM manager WHERE manager_id = "'.$manager_d.'"';
    $query = $this->db->query($sql);

    if ($query->num_rows() > 0) {
        $data = $query->result_array();
    }
    return $data;
}
 public function get_user_employee_code($user_id)
{
    $sql = 'SELECT * FROM user WHERE user_id = "'.$user_id.'"';
    $query = $this->db->query($sql);

    if ($query->num_rows() > 0) {
        $data = $query->result_array();
    }
    return $data;
}

My views:

Views:

foreach($permit_user_id as $user){
    foreach($get_employee_code as $employee_code){
        print_r("User_id:".$user['user_id']."  Employee_code:".$employee_code['employee_code']."<br>");
    }
}

Table manager

 id     user_id     manager_id
  1     10001       20000
  2     10002       20000
  3     10003       20000
  4     10004       20000
  5     10005       20000
  6     10006       20000

Table user:

  id    user_id     employee_code
  1     10001       500001
  2     10002       500002
  3     10003       500003
  4     10004       500004
  5     10005       500005
  6     10006       500006

Output what i got

 User_id: 10001 Employee_code: 500006
 User_id: 10002 Employee_code: 500006
 User_id: 10003 Employee_code: 500006
 User_id: 10004 Employee_code: 500006
 User_id: 10005 Employee_code: 500006
 User_id: 10006 Employee_code: 500006

Output what i need:

 User_id: 10001 Employee_code: 500001
 User_id: 10002 Employee_code: 500002
 User_id: 10003 Employee_code: 500003
 User_id: 10004 Employee_code: 500004
 User_id: 10005 Employee_code: 500005
 User_id: 10006 Employee_code: 500006

Thanks in advance

@T.Maharani can you please check i have include the table data.. — Arun, Mar 29 '20 at 14:24
Kindly remove single quote before the variable $user_id and $manager_id in query. $sql = "SELECT * FROM manager WHERE manager_id =" .$manager_id; — T.Maharani, Mar 29 '20 at 14:46
@T.Maharani That would cause the literal text `.$user_id.` to be inserted instead of the variable value. — El_Vanja, Mar 29 '20 at 14:48

score 1 · Accepted Answer · answered Mar 29 '20 at 15:08

This is where you are mistaken:

foreach($get_userid as $user){
    $data["get_employee_code"] = $this->report->get_user_employee_code($user['user_id']);
}

Here you are overwriting $data["get_employee_code"] with each iteration. In the end, all that is left in this array is the last value, "500006". What you probably wanted to do was this:

$data["get_employee_code"][] = // <- note the extra brackets

But I also feel you're doing your view output wrong:

foreach($permit_user_id as $user){
    foreach($get_employee_code as $employee_code){
        print_r("User_id:".$user['user_id']."  Employee_code:".$employee_code['employee_code']."<br>");
    }
}

This means that for each user, you will also output every employee id, not just that user's id, resulting in this:

 User_id: 10001 Employee_code: 500001
 User_id: 10001 Employee_code: 500002
 User_id: 10001 Employee_code: 500003
 User_id: 10001 Employee_code: 500004
 User_id: 10001 Employee_code: 500005
 User_id: 10001 Employee_code: 500006

 User_id: 10002 Employee_code: 500001
 User_id: 10002 Employee_code: 500002
 User_id: 10002 Employee_code: 500003
 User_id: 10002 Employee_code: 500004
 User_id: 10002 Employee_code: 500005
 User_id: 10002 Employee_code: 500006

 ... and so on ...

What you should do instead of all this is write a single query that gets all the data you need in the model:

public function get_user_and_employee_ids_from_manager_id($manager_id){
    $sql = 'SELECT user.user_id, user.employee_id FROM user JOIN manager ON user.user_id = manager.user_id WHERE manager.manager_id = "'.$manager_id.'"';
    $query = $this->db->query($sql);

    if ($query->num_rows() > 0) {
        $data = $query->result_array();
    }
    return $data;
}

Now this $data is an array that holds rows with paired user and employee ids which you can pass to your view and simply output by using a single foreach.

Also, I don't know what's used in the background for db querying, but find a way to parametrize your queries. Directly inserting parameters into a query like this is unsafe.

score 0 · Answer 2 · answered Mar 29 '20 at 16:41

Try to achieve this in single query can reduce unnecessary loop and db call.

SELECT 
  a.`manager_id`,
  a.`user_id`,
  b.`employee_code` 
FROM
  `manager` AS a 
  INNER JOIN `user` AS b 
    ON a.`user_id` = b.`user_id`
ORDER BY a.`manager_id` ASC,
  a.`id` ASC ;

Once data reached to view.

You can use THIS

OR

Declare array for manager_id.
loop result
you differentiate manager_id by checking in_array call.

Codeigniter pass array from controller to model, But not returning a matching result

2 Answers2