Chunking Returned MySQL Dataset Into Multiple Arrays

[scriderpsd]

I'm working to compute the median hours to resolution for tech support workers in my organization. I have pretty good idea of what needs to happen to compute the median, I'm just not sure how to get there.

I currently have a mysql dataset being returned with values similar to the data below.

hoursResolveDiff,lastname
0,Astudillo
3,Astudillo
7,Astudillo
7,Astudillo
8,Astudillo
8,Astudillo
22,Astudillo
25,Astudillo
46,Astudillo
51,Astudillo
103,Astudillo
117,Astudillo
145,Astudillo
148,Astudillo
169,Astudillo
173,Astudillo
334,Astudillo
339,Astudillo
0,Blakeman
0,Blakeman
0,Blakeman
0,Blakeman
0,Blakeman
0,Blakeman
1,Blakeman
2,Blakeman
2,Blakeman
2,Blakeman
3,Blakeman
4,Blakeman
4,Blakeman
5,Blakeman
5,Blakeman
5,Blakeman
6,Blakeman
6,Blakeman
6,Blakeman
6,Blakeman
7,Blakeman
7,Blakeman
7,Blakeman
10,Blakeman
...

I'm guessing I need to break this data into multiple arrays, so I would have an array of Astudillo's data, and array of Blakeman's data, etc. Count the number of elements in each array. Then, if the number of elements in that array is odd, grab the middle element. If the number of elements is even, get the sum of the middle two elements / 2.

Any help is appreciated. I'm really having trouble getting started.

Thanks for your time.

Scott

[Spookster]

Are you trying to compute the median for each person or a median for everybody? If for each person why not just query for a list of people and use that result to query the other data you need for computations and then do the computation for each.

Something like:

1. Get a list of all the tech support personnel
2. Use that list in a foreach loop to query each person for the list of hours they worked
3. Compute the median

[scriderpsd]

Yes, I'm trying to compute the median of each individual.

I've got the first part worked out.

$hours is now an array of arrays for each worker like so...

Array ( 
	[] => Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 [4] => 1 [5] => 3 [6] => 122 [7] => 211 [8] => 212 [9] => 256 ) 
	[Astudillo] => Array ( [0] => 0 [1] => 3 [2] => 7 [3] => 7 [4] => 8 [5] => 8 [6] => 22 [7] => 25 [8] => 46 [9] => 51 [10] => 103 [11] => 117 ) 
	[Blakeman] => Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 [4] => 0 [5] => 0 [6] => 1 [7] => 2 [8] => 2 [9] => 2 [10] => 3 ) 
	[Blank] => Array ( [0] => 1 [1] => 5 [2] => 6 [3] => 6 [4] => 20 [5] => 25 [6] => 28 [7] => 93 [8] => 115 [9] => 119 [10] => 170 [11] => 190 [12] => 260 ) 
)

But now I'm unable to get this part working.

PHP Code:

foreach( $hours as $k=>$v ) { 
   echo 'Median hours for '.$k.' is '.computeMedian($v).'<br>'; 
} 

function computeMedian(Array $v) { 
   $c = count($v); 
   $hl = floor($c/2); 
   $hu = ceil($c/2); 
   if( $hl == $hu )  return $v[$hl]; 
   return (($hl+$hu)/2); 
} 


[Spookster]

It would be easier if you didn't try to rework the data after getting it from the database. You didn't provide any information on your database structure but if you did this correctly you should be able to do something as simple as this:

In place of the hardcoded array you would loop through the resultset from the query in much the same way.

PHP Code:

<?php

$employees = array(
    array(
        'name' => 'john',
        'hours' => array(10, 20, 30, 40)
    ),
    array(
        'name' => 'jack',
        'hours' => array(15, 25, 35, 45)
    )
);

var_dump($employees);

foreach ($employees as $employee) {

    $totalHours = 0;
    $totalDays = 0;

    foreach ($employee['hours'] as $hours) {

        $totalHours += $hours;
        $totalDays++;
    }

    echo "Name: " . $employee['name'] . " Median Hours: " . $totalHours / $totalDays . "</br>";
}
?>

PHP Code:

array
  0 => 
    array
      'name' => string 'john' (length=4)
      'hours' => 
        array
          0 => int 10
          1 => int 20
          2 => int 30
          3 => int 40
  1 => 
    array
      'name' => string 'jack' (length=4)
      'hours' => 
        array
          0 => int 15
          1 => int 25
          2 => int 35
          3 => int 45
Name: john Median Hours: 25
Name: jack Median Hours: 30