I’m trying to build a report that basically presents the same data in three different ways. Because the query itself can take over a minute to run I’m wanting to just query the data and then loop through the results and build three arrays. However, it’s not working out as I had planned.
Each record has the following relevant data:
$employee_id
$employee_name
$department
$customer_code
$time
What I’m trying to get is an array like this:
$department
—$employee_id
—$employee_name
—projects
------$customer_code
---------$time
There would be multiple employees per department, multiple projects per employee, and multiple time entries per project.
The best I’ve gotten is the multiple employees per department and a single project ($customer_code) per employee. I got that using this code:
$emp[$department][] = array(id=>$employee_id,name=>"$employee_name",project=>array($customer_code));
I’ve tried so many different permutations of the above that I’ve lost track of what I’ve tried and what I haven’t. I could use a bit of guidance, please.
Can’t help you with the array right now (I’m sure someone else will 
), but I’m curious about your query. How comes it takes so long to run? Do you have so many employees, departments, customers?
I don’t know how many records are there in your database which might you think take too long time but if i have understood your problem, the array construction as follows could help you:
$array = array();
$array = array(
'dept1'=>array(
'empinfo1'=> array(
'id'=>'1',
'name'=>'A',
'projects'=>array('time1', 'time2','time3')
),
'empinfo2'=> array(
'id'=>'2',
'name'=>'B',
'projects'=>array('time1', 'time2','time3')
)
)
);
print_r($array);
The query is pulling from an Oracle database that is populated by a ERP type software package that we use. We only have read access to the database directly. In my opinion, the database wasn’t designed as optimally as it could have been. Every query we write involves joining many more tables than I think it should. Add in the fact that some of the tables contain half a million records and it gets time intensive quickly.
Not quite Rajug. Maybe a bit more code to help explain what I’m trying to do:
while($results->fetchNXTResult()){ //loop through result set
$customer_code = $results->getItemValue('CUSTOMERCODE');
$department = $results->getItemValue('DEPARTMENT');
$time = $results->getItemValue('TOTALTIME');
$employee_id = $results->getItemValue('USERNAME');
$employee_name = $results->getItemValue('FULLNAME');
//code to build the array
}
Now the way I wanted to do it doesn’t seem to be possible. If I lose the employee name (or the id) I can make it work. Here’s the code I for that:
$emp[$department][$employee_id][$customer_code][] = $time;
But I’m still trying to find a way to add the employee’s name back in without breaking things. Each way that I’ve tried, causes the array to only get the last project and the last time entry for that project.
Well, it’s not the way I wanted it, but I’ve got something that works anyway.
Rather than:
$department
—$employee_id
—$employee_name
—projects
------$customer_code
---------$time
I’ve got:
$department
—$employee_id
------$employee_name
---------$customer_code
------------$time
To get it, I used the following:
$emp[$department][$employee_id][$employee_name][$customer_code][] = $time;
If it’s a problem, another way exists. But, I assume employee_id functionaly determines the employee_name. I don’t know if this is true or not(eg, employee got married and changed name between projects).
Untested, but think it works.
$employee_names = array();
$tmp = array();
while($results->fetchNXTResult()){ //loop through result set
$customer_code = $results->getItemValue('CUSTOMERCODE');
$department = $results->getItemValue('DEPARTMENT');
$time = $results->getItemValue('TOTALTIME');
$employee_id = $results->getItemValue('USERNAME');
$employee_name = $results->getItemValue('FULLNAME');
$tmp[$department][$employee_id]['projects'][$customer_code][] = $time;
$employee_names[$employee_id] = $employee_name;
}
$result = array();
foreach ($tmp as $department => $records) {
foreach ($records as $employee_id => $projects) {
$employee_name = $employee_names[$employee_id];
$result[$department][] = compact('employee_id', 'employee_name', 'projects');
}
}
If this isn’t the format you desire, then you should show us the format. Don’t use pseudo syntax, use a real hard coded array like rajug did.
crmalibu, that works great. The only ‘issue’ (if you could call it that) is that it seems to add an unneeded layer (array) on projects.
[Materials] => Array
(
[0] => Array
[employee_id] => 1332
[employee_name] => John Doe
[projects] => Array
(
[projects] => Array
(
[488] => Array
(
[0] => 7.25
[1] => 8.5
[2] => 7
[3] => 7
)
[494] => Array
(
[0] => 6.25
[1] => 6.75
[2] => 7.75
[3] => 7.5
[4] => 6
[5] => 9
[6] => 8.25
[7] => 4
)
[515] => Array
(
[0] => 6.5
[1] => 8
[2] => 6.75
[3] => 6.75
[4] => 7.5
[5] => 8
[6] => 3
)
)
)
Thanks for the help. And for teaching me yet another php function I’ve never noticed before (compact).
You could just do
$projects = $projects['projects'];
in the nested foreach
edit-
or instead just change
$tmp[$department][$employee_id]['projects'][$customer_code][] = $time;
//to
$tmp[$department][$employee_id][$customer_code][] = $time;
That’s perfect. Thanks again crmalibu.