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

Derokorian

Well keep track of the last person and change arrays if its changed:

$last = '';
$hours = array();
while( $row = $res->fetch_assoc() ) {
   if( $last != $row['lastname'] ) {
      $hours[$row['lastname']] = array();
      $last = $row['lastname'];
   }
   $hours[$row['lastname'][] = $row['hoursResolveDiff'];
}

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

Weedpacket

While looping through the rows...

$hoursByWorker[$row['lastName']][] = $row['hoursResolveDiff'];

That will give you the multiple arrays you're wanting (indexed by the worker's name).

There are also methods for determining the median within MySQL described in its documentation. You might want to search through some of them.

scriderpsd

Derokorian,

Thanks for the detailed response. This is what I'm trying to do. But the code keeps breaking on this line...

$hours[$row['lastname'][] = $row['hoursResolveDiff'];

Thanks for the help.

bradgrafelman

One question: Do you actually need the individual values as you're receiving them from the MySQL result set above, or are you only interested in the averages?

If so, then why SELECT the individual data at all? Why not just tell MySQL to GROUP BY the name field and only SELECT the AVG() value from each group?

EDIT: As for your coding problem above, look at the square brackets on the left-hand side:

$hours[$row['lastname'][]

You've got three opening '[' brackets but only two closing ']' brackets.

scriderpsd

bradgrafelman;10992613 wrote:
One question: Do you actually need the individual values as you're receiving them from the MySQL result set above, or are you only interested in the averages?

If so, then why SELECT the individual data at all? Why not just tell MySQL to GROUP BY the name field and only SELECT the AVG() value from each group?

EDIT: As for your coding problem above, look at the square brackets on the left-hand side:
$hours[$row['lastname'][]
You've got three opening '[' brackets but only two closing ']' brackets.

Thanks! I'm actually trying to calculate the median, not the average.

bradgrafelman

D'oh, of course you are, dunno why I was going on about averages.

In that case, the syntax error in my reply still applies.

scriderpsd

Thanks bradgrafelman, 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.

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

Any ideas?

bradgrafelman

The second return:

return (($hl+$hu)/2);

is returning the average of the array indexes not of the two array values at those respective indexes.

scriderpsd

bradgrafelman;10992619 wrote:
The second return:
return (($hl+$hu)/2);
is returning the average of the array indexes not of the two array values at those respective indexes.

I'm still at a total loss. I can't get the function to return anything.

Derokorian

return (($hl+$hu)/2); 
// Should have been:
return (($v[$hl]+$v[$hu])/2);