How can I loop over a CodeIgniter query's results to execute another SELECT query for each row? [duplicate]

Question

I'm accessing the database to retrieve a list of employee ids and then take that array of employee id to run a query against another table on the database that gets all the information of the person based off the employee id but I'm only getting the last value in the statement but I need to get and loop for all of them. Can anyone see my error?

public function manager_list()
{
    $results = array();
    $managers = $this->db->query('select `employee_id` from `managers`');
    
    foreach($managers->result() as $row)
    {
        $employee_id = $row->employee_id;
    }
    
        $query = $this->db->query('select `employee_id`, `ssn`, `first_name`, `last_name`, `department`, `title`, `status` from `employees` where `employee_id` = "'.$employee_id.'"');
    
        foreach ($query->result() as $row){
            $results[] = array(
            'employee_id' => $row->employee_id,
            'ssn' => $row->ssn,
            'first_name' => $row->first_name,
            'last_name' =>$row->last_name,
            'department' =>$row->department,
            'title' =>$row->title,
            'status' =>$row->status,
        );
        }
    return $results; 
}

This is using the code igniter framework.

You need to move the closing brace after getting the employee ID to right above the return. The second query (and loop) needs to be within the braces for the first loop. — Mark S.
– Mark S., Commented May 14, 2014 at 20:53
1. The closing } of the for-loop is misplaced. 2. Surely this could be done better with a single SQL query using a JOIN rather than a whole bunch of queries in a loop. — p.s.w.g
– p.s.w.g, Commented May 14, 2014 at 20:55
Why not do a JOIN in the query? Do you have a need to run two queries? — Jay Blanchard
– Jay Blanchard, Commented May 14, 2014 at 20:56
There is really no need for two queries here unless you're using the employee_id's somewhere else that you've not told us about. — Jay Blanchard
– Jay Blanchard, Commented May 14, 2014 at 21:09
Relevant reading: SELECT WHERE IN subquery using CodeIgniter's where_in() method and Using a subquery with CodeIgniter WHERE IN clause — mickmackusa
– mickmackusa ♦, Commented Jul 26 at 4:57

Nimantha · Accepted Answer · 2025-08-04 03:22:13Z

2

Here is a short version with JOIN (replaces your whole code):

public function manager_list()
{
    return $this->db
    ->select('e.employee_id, e.ssn, e.first_name, 
              e.last_name, e.department, e.title, e.status')
    ->join('managers AS m', 'e.employee_id = m.employee_id')
    ->where(array('e.employee_id' => $employee_id))
    ->get('employees AS e')
    ->result_array();
}

edited Aug 4 at 3:22

Nimantha

6,5476 gold badges32 silver badges78 bronze badges

answered May 14, 2014 at 21:03

Shomz

37.7k4 gold badges61 silver badges87 bronze badges

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

Comments

Jay Blanchard · Accepted Answer · 2014-05-14 21:17:55Z

1

Do one query with a JOIN and do one loop -

public function manager_list()
{
    $results = array();
    $managers = $this->db->query('SELECT `m`.`employee_id`, `e`.`ssn`, `e`.`first_name`, `e`.`last_name`, `e`.`department`, `e`.`title`, `e`.`status` FROM `managers` AS `m` JOIN `employees` AS `e` ON `m`.`employee_id` = `e`.`employee_id`');

    foreach ($managers->result() as $row){
        $results[] = array(
            'employee_id' => $row->employee_id,
            'ssn' => $row->ssn,
            'first_name' => $row->first_name,
            'last_name' =>$row->last_name,
            'department' =>$row->department,
            'title' =>$row->title,
            'status' =>$row->status,
        );
    }
    return $results;
}

edited May 14, 2014 at 21:17

answered May 14, 2014 at 21:08

Jay Blanchard

34.5k17 gold badges82 silver badges130 bronze badges

3 Comments

Shomz Over a year ago

I believe the whole foreach part is slightly redundant because it does exactly what the native CI method result_array() does, right?

Jay Blanchard Over a year ago

Yes, it is redundant. I was just making enough change in the OP's code for the point to be made about the JOIN. BTW +1 for your excellent elegant solution @Shomz

Cesarg2199 Over a year ago

Thank you @Shomz and Jay for telling me the foreach is redundant. I was unaware it was because the first time I looked up how to return an array with code igniter was with a foreach. You have saved me some time, and code.

Collectives™ on Stack Overflow

How can I loop over a CodeIgniter query's results to execute another SELECT query for each row? [duplicate]

2 Answers 2

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Linked

Related