CODEWITHSUNDEEP
phpdatabasecodeignitermysqli
Cesarg2199
Cesarg2199

Reputation: 579

Not looping correctly

So im 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.

Upvotes: 0

Views: 63

Answers (2)

Shomz
Shomz

Reputation: 37711

You could shorten this even more if you did it in a single query, but since you say you must have two, here is a short version of your second query, with no need for the foreach loop: 

$query = $this->db->query('
select `employee_id`, `ssn`, `first_name`, 
       `last_name`, `department`, `title`, `status` 
from `employees` where `employee_id` = "'.$employee_id.'"');

return $query->result_array();

Using Active Records: 

return $this->db
->select('`employee_id`, `ssn`, `first_name`, 
       `last_name`, `department`, `title`, `status`')
->where(array('employee_id' => $employee_id))
->get('employees')
->result_array();

UPDATE

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();
}

Upvotes: 2

Jay Blanchard
Jay Blanchard

Reputation: 34426

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;
}

Upvotes: 1

Related Questions