I am fetching the id, first name, and last name of all employees that are approved and not archived. Then I am looping these results and using the ids to query other tables to collect some count data.
I tried the below code, but I am not getting the expected output.
$queryEmp = "
SELECT id, firstname, lastname
FROM tbl_employee as e
WHERE is_archive=0 and is_approved=1
";
$getQuery= $this->db->query($queryEmp);
$result= $getQuery->result();
foreach ($result as $key=> $value) {
//echo "<pre>";
print_r($value);
$day = "MONTH(date_of_created) = DATE(CURRENT_DATE())";
$group = "f_id IN (SELECT MAX(f_id) FROM tbl_fileStatus GROUP BY f_bankid)";
$condiion = "and ba.createdby='" . $value->id . "' and " . $day ." and " . $group;
$query2 = "
select
(SELECT COUNT(c_id)
FROM tbl_lead
WHERE leadstatus='1' AND ".$day.") as confirmCount,
(SELECT COUNT(f_id)
FROM tbl_fileStatus as fs
join tbl_bankdata as ba on ba.bank_id=fs.f_bankid
WHERE fs.f_filestatus=1 " . $condiion . ") as disbursed,
(SELECT COUNT(f_id)
FROM tbl_fileStatus as fs
join tbl_bankdata as ba on ba.bank_id=fs.f_bankid
WHERE fs.f_filestatus=2 ".$condiion.") as filesubmit
";
# code...
$getQuery2= $this->db->query($query2);
$result2[]=$getQuery2->result();
}
echo "<pre>";
print_r(result2);
$result
looks like this:
Array (
[0] => stdClass Object (
[id] => 1
[firstname] => xyz
[lastname] => xyz
)
...
)
Second query output:
Array (
[0] => Array (
[0] => stdClass Object (
[fallowCall] => 0
[confirmCount] => 0
[disbursed] => 0
[filesubmit] => 0
)
)
...
)
How can I produce the correct results which relate respective employees with with their performance metrics? Either this structure:
Array (
[0] => stdClass Object (
[id] => 1
[firstname] => xyz
[lastname] => xyz
[somename] => (
[fallowCall] => 0
[confirmCount] => 0
[disbursed] => 0
[filesubmit] => 0
)
)
...
)
Or this structure:
Array (
[0] => stdClass Object (
[id] => 1
[firstname] => xyz
[lastname] => xyz
[fallowCall] => 0
[confirmCount] => 0
[disbursed] => 0
[filesubmit] => 0
)
...
)
I have added the my table structure and some sample data here: https://www.db-fiddle.com/f/8MoWmKPuzTrrC3DQJsiX35/0
some notes here
1) createdby
is the id of table tbl_employee
2) lead_id
in the bank table is the c_id
of the table tbl_lead
3) f_bankid
in the tbl_fileStatus
is the bank_id
of the table tbl_bankdata