array manipulation for graphing

array manipulation of data points finding average values per hour

[timemachine]

I need a way of calculating the average per hour in my data series so I can pass it on to the graphing script as the current way gives too many data points per 24hr period.

The graphing script requires the following array so it can output a graph:

$sql="SELECT items, recordTime FROM tbl_CBS WHERE recordTime > (Date_SUB(CURDATE(), INTERVAL 1 DAY))";

while($row = mysql_fetch_assoc($result)){
$recordTime=$row["recordTime"];
$count=$row["items"];
//ADD TO ARRAY
$dataArray[$salesgroup]=$count;
}
// Now graph it 
$graph->addData($dataArray);

The array contains the following data

Array
(
 [2010-02-09 00:02:17] => 18
 [2010-02-09 00:12:18] => 18
 [2010-02-09 00:22:20] => 18
 [2010-02-09 00:32:21] => 18
 [2010-02-09 00:42:22] => 18
 [2010-02-09 00:52:24] => 18
 [2010-02-09 01:02:25] => 17
 [2010-02-09 01:12:49] => 17
 [2010-02-09 01:23:18] => 17
 [2010-02-09 01:33:28] => 17
 [2010-02-09 01:43:30] => 18
 [2010-02-09 01:53:31] => 19
 [2010-02-09 02:03:33] => 19
 [2010-02-09 02:13:34] => 19
 [2010-02-09 02:23:36] => 19
 [2010-02-09 02:33:38] => 19
 [2010-02-09 02:43:39] => 19
)

As can be seen the number of data points can get really huge over 24 hour periods or more if we get the data every 5 minutes.

What I want is to average the data every hour which will give 24 data points per day to graph.

so what I want is for the array to have the data as follows: with averaged values for each hour.

Array
(
 [2010-02-09 00:00:00] => 18
 [2010-02-09 01:00:00] => 18
 [2010-02-09 02:00:00] => 18
 [2010-02-09 03:00:00] => 18
 [2010-02-09 04:00:00] => 18
 [2010-02-09 05:00:00] => 18
 [2010-02-09 06:00:00] => 17
 [2010-02-09 07:00:00] => 18
 [2010-02-09 08:00:00] => 18
 [2010-02-09 09:00:00] => 18
 [2010-02-09 10:00:00] => 18
 [2010-02-09 11:00:00] => 18
 [2010-02-09 12:00:00] => 18
 [2010-02-09 13:00:00] => 17
 [2010-02-09 14:00:00] => 18
 [2010-02-09 15:00:00] => 18
 [2010-02-09 16:00:00] => 18
 [2010-02-09 17:00:00] => 17
 [2010-02-09 18:00:00] => 18
 [2010-02-09 19:00:00] => 18
 [2010-02-09 20:00:00] => 18
 [2010-02-09 21:00:00] => 17
 [2010-02-09 22:00:00] => 18
 [2010-02-09 23:00:00] => 18

)

Thanks guys

[Readie]

I think the following will work... Although it may be a little CPU intensive for the server if your arrays get really big.

You'll need to do your graphing and stuff yourself, but this will develop your arrays for you.

$output[xx] will get you your average / time, where xx is the hour (without leading zeroes) so $output[7] is 7am, [0] is midnight etc...

I know it may seem a bit convoluted me creating an array a certain way, then exploding it, but I wrote a solution, realised I'd made a mistake, and then threw something in to make it work. You'll have to streamline it yourself I'm afraid :P

<?php
 
$sql= 'SELECT items, recordTime FROM tbl_CBS WHERE recordTime > (Date_SUB(CURDATE(), INTERVAL 1 DAY))';
$result = mysql_query($sql);
$sql_count = mysql_num_rows($result);
 
for($i = 0; $i < $sql_count; $i++) {
$items = mysql_result($result,$i,"items");
$rec_time = mysql_result($result,$i,"recordTime");
$row[$i] = $rec_time . '-' . $items;
}
 
$array_count = count($row);
 
for($i = 0; $i < $array_count; $i++) {
$data = explode(" ", $row[$i]);
$hour = explode(":", $data[1]);
$count = explode("-", $hour[2]);
$countf[$i] = $count[1];
$hourf[$i] = $hour[0];
}
 
for($i = 0; $i < 24; $i++) {
$hour_count[$i] = 0;
$data_total[$i] = 0;
}
 
for($i = 0; $i < $array_count; $i++) {
for($ii = 0; $ii < 24; $ii++) {
if($hourf[$i] == sprintf("%02d", $ii)) {
$hour_count[$ii] += 1;
$data_total[$ii] += $countf[$i];
}
}
}
 
for($i = 0; $i < 24; $i++) {
if($data_total[$i] != 0 && $hour_count[$i] != 0) {
$out = ($data_total[$i] / $hour_count[$i]);
$output[$i] = $out;
} else {
$output[$i] = 'NULL';
}
$time = sprintf("%02d", $i);
echo $time . ':00:00 = ' . $output[$i] . '<br />';
}
 
?>

... I hope it works... Took me ages to do that.

[timemachine]

Readie,

It worked perfectly, thank you for your time, very much appreciate it mate,

And to put it into the array $dataArray that the graphing script needs I just added to the following bit of code:

again thank you very much


for($i = 0; $i < 24; $i++) {
if($data_total[$i] != 0 && $hour_count[$i] != 0) {
$out = ($data_total[$i] / $hour_count[$i]);
$output[$i] = $out;
} else {
$output[$i] = 'NULL';
}
$time = sprintf("%02d", $i);
echo $time . ":00:00 = " . $output[$i] . "\n";
$time = $time.":00:00";
$dataArray[$time] = $output[$i];
}

[Readie]

Glad to hear it :)

An afterthought I had: you may consider changing

$output[$i] = 'NULL'

to

$output[$i] = 0

To stop it messing with your graph if no values are declared - unless your graph is set up to just stop the line when it encounters an alphanumeric string?

[timemachine]

Yep I already that, thanks