averaging by weeks...

[AV1611]

This query was supposed to give the average for a 7 day period, but I guess I can't use LIMIT to pick the slot of dates...

Can someone point me in the right direction??? I actually have a table with about 100 rows, and I just want to create an array that has the average of each seven day period in order so I can plot it on a graph...

the full script repeat this a dozen times and only the limit part changes... but it don't work...
[code]

$result1 = mysql_query("Select avg(pass_avg) as pass_avg from firstpassavg order by it_date DESC limit 7,7")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValues2[]=round($row['pass_avg'],0);
echo round($row['pass_avg'],0);
[/code]

[AV1611]

Looks like this will do it...I'll have to tweak it some...
[code]

function average($a){
  return array_sum($a)/count($a);
}

$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 0,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValues2[]=round($row['pass_avg'],0);

}

echo average($graphValues2);
[/code]

[Barand]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Can someone point me in the right direction??? [/quote]

Frankly, I doubt it. You don't give us any clues about the data you are starting with

[AV1611]

I'm sorry... we're in the middle of an ISO audit and this is part of a thing tomorrow for it: Here is where I am now. I've fixed the major issue, and have a working script, but the script plots from a table that has a date and a number that is an average. the graph charts each day's average, then a second line charts the previous seven days average. so the line plots every seven days... I did it because I'm not smart enough to do a true rolling seven day average (each day plots the previous seven day average daily instead of each week plotting the seven day average)

Could someone help me convert this to a true seven day rolling average? I can't get my head around how to do the loops...

the table has fields it_date and pass_avg

the script is as follows:
[code]
<?php
$user="";
$host = "localhost";
$password="";
$database = "teknetix";
mysql_connect($host,$user,$password);
mysql_select_db($database);
// creates the daily plot array
$result = mysql_query("Select distinct firstpass3.it_date,(firstpass4.pass/(firstpass3.pass+firstpass4.pass))*100 as passpercent from firstpass3,firstpass4 where firstpass3.it_date=firstpass4.it_date order by firstpass3.it_date DESC limit 90")or die(mysql_error());
while ($row = mysql_fetch_array($result)){
$graphValues[]=round($row['passpercent'],0);
}


function average($a){
  return array_sum($a)/count($a);
}

// this section is repeated 13 times (90 days/7)
$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 0,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesa[]=round($row['pass_avg'],0);
// echo $row['pass_avg']."<br/>";
}
// echo "<br/>". average($graphValuesa);
$graphValues3[] = average($graphValuesa);

$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 7,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesb[]=round($row['pass_avg'],0);
}
$graphValues3[] = average($graphValuesb);

$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 14,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesc[]=round($row['pass_avg'],0);
}

$graphValues3[] = average($graphValuesc);

$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 21,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesd[]=round($row['pass_avg'],0);
}

$graphValues3[] = average($graphValuesd);

$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 28,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuese[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuese);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 35,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesf[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesf);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 42,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesg[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesg);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 49,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesh[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesh);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 56,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesi[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesi);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 63,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesj[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesj);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 70,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesk[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesk);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 77,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesl[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesl);


$result1 = mysql_query("Select pass_avg from firstpassavg order by it_date DESC limit 84,7 ")or die(mysql_error());
while ($row = mysql_fetch_array($result1)){
$graphValuesm[]=round($row['pass_avg'],0);

}

$graphValues3[] = average($graphValuesm);
//
$graphValues3[] = average($graphValuesm);

// from here below is how I make the graph

// Define .PNG image
header("Content-type: image/png");
$imgWidth=676;
$imgHeight=501;
// Create image and define colors
$image=imagecreate($imgWidth, $imgHeight);
$colorWhite=imagecolorallocate($image, 255, 255, 255);
$colorGrey=imagecolorallocate($image, 192, 192, 192);
$colorBlue=imagecolorallocate($image, 0, 0, 255);
$colorBlack=imagecolorallocate($image, 0,0,0);
$colorRed=imagecolorallocate($image, 255,0,0);

// Create border around image
// right vert
imageline($image, 674, 674, 674, 0, $colorBlack);
// bottom horiz
imageline($image, 0, 499, 674, 499, $colorBlack);
// top horiz
imageline($image, 674, 0, 0, 0, $colorBlack);


imageline($image, 0,374,674,374,$colorBlack);
imageline($image, 0,249,674,249,$colorBlack);
imageline($image, 0,124,674,124,$colorBlack);

imageline($image, 224,0,224,499,$colorBlack);
  imageline($image, 449,0,449,499,$colorBlack);


// left vert
imageline($image, 0, 0, 0, 674, $colorBlack);

// Create grid i<52 means 52 verticals
for ($i=1; $i<90; $i++){
//H
imageline($image, $i*7.5, 0, $i*7.5, 675, $colorGrey);
//V
imageline($image, 0, $i*25, 675, $i*25, $colorGrey);
}


// Create line graph
for ($i=0; $i<90; $i++){
    if (isset($graphValues[$i+1])) {
    imageline($image, ($i*7.5)/1,
    (500-($graphValues[$i])*25 ), (($i+1)*7.5)/1,
    (500-($graphValues[$i+1])*25 ), $colorBlue);
    }
}

// Create line graph
for ($i=0; $i<13; $i++){
    if (isset($graphValues3[$i+1])) {
    imageline($image, ($i*7.5),
    (500-($graphValues3[$i])*25 ), (($i+1)*7.5),
    (500-($graphValues3[$i+1])*25 ), $colorRed);
    }
}


// Output graph and clear image from memory
imagepng($image);
imagedestroy($image);

?>
[/code]

[Barand]

This is the method I employ for rolling averages

1 ) put all the values into a single array

2 ) get the size of the array

3 ) Use array_slice() to get successive groups of 7 elements

<?php

[code]$data = array(1,2,4,6,5,8,7,9,3,4,6,7,8,4,5,6,2,3,4,8,7,9,4,6,7);

$size = count($data);

$avgs = $sums = $nums = array();
for ($i = 0; $i < $size-6; $i++) {
     $tmp = array_slice($data, $i, 7);
     $s = array_sum($tmp);
     $avgs[$i+6] = $s/7;
     $nums[$i+6] = join (',',$tmp);
     $sums[$i+6] = $s;
}

// output
echo "<TABLE border='1'>\n";
echo "<TR>
       <TH>Data</TH>
       <TH>Average</TH>
       <TH>Numbers</TH>
       <TH>Sum</TH>
       </TR>\n";
foreach ($data as $k => $v) {
         echo "<TR>
                <TD>$v</TD>
                <TD>".number_format($avgs[$k],3)."</TD>
                <TD>$nums[$k]</TD>
                <TD>$sums[$k]</TD>
         </TR>\n";
}
echo "</TABLE>\n";
?>[/code]