[UPDATED with a followup question see edit4]
So I have a function which returns score average of X number of consecutive dates. But I like to handle the rest value.
For example if I have found the following rows (based on some random data and the hardcoded personid = 133)
the average output I get when callling the PHP function getConsecutiveInterval(4);
Total Found: 10
Num Of Loops: 2.5
2.75
3.5
and I expect the averages to be
2.75, 3.5 and 3.5(based on the rest 2 dates not 4)
And when I use getConsecutiveInterval(3); I expect the average value to be
3.33, 3.33, 2.66 and 4 (based on 1 date)
and I got
Total Found: 10
Num Of Loops: 3.3333333333333
3.3333333333333
3.3333333333333
2.6666666666667
So how can I return / handle the rest rows as an average?
This is the php function:
function getConsecutiveInterval($interval) {
//$interval Num Of Consecutive Days
global $conn;
$offset = '';
// Query is the same and at the end of it you include LIMIT to be controlled by the loop.
$q = "SELECT a.purdate, b.score, a.status FROM records a INNER JOIN scores2 b ON a.purdate = b.date AND a.personid = b.personid WHERE a.personid = 133 AND a.status IN('P','T') ORDER BY purdate ASC, score DESC ";
//Total Found Use To Divide by
$result = mysqli_query($conn, $q);
$num_rows = mysqli_num_rows($result);
echo 'Total Found: '.$num_rows.'<br/>';
$numOfLoops = $num_rows/$interval;
echo 'Num Of Loops: '. round($numOfLoops).'<br/>';
// For loop will control the results sets divided by 4
for ($i = 1; $i <= $numOfLoops; $i++) {
// To add the offset after the first set
if ($offset > 0) {
$limitValues = $offset. ", " . $interval . " ";
} else {
$limitValues = $interval;
}
$sqlquery = $q . " LIMIT " . $limitValues;
$avg = 0;
$total = 0;
//Total Found Use To Divide by
$result = mysqli_query($conn, $q);
$num_rows = mysqli_num_rows($result);
//echo 'Total Found: '.$num_rows;
foreach (mysqli_query($conn, $sqlquery) as $results) {
// Do Something
$total += $results['score'];
$avg = $total / $interval;
print_r($results['purdate'].' <-> ');
//see [edit2] need only the first date in assoc array?? $results['purdate'][0] doesn't work
}
echo $avg . '<br/>';
$offset += $interval;
}
}
[edit1] i add some code to get the startDate for each interval also some code for start to handle the rest value
function getConsecutiveInterval($interval) {
//$interval Num Of Consecutive Days
global $conn;
$offset = '';
// Query is the same and at the end of it you include LIMIT to be controlled by the loop.
$q = "SELECT a.purdate, b.score, a.status FROM records a INNER JOIN scores2 b ON a.purdate = b.date AND a.personid = b.personid WHERE a.personid = 133 AND a.status IN('P','T') ORDER BY purdate ASC, score DESC ";
//Total Found Use To Divide by
$result = mysqli_query($conn, $q);
$num_rows = mysqli_num_rows($result);
echo 'Total Found: '.$num_rows.'<br/>';
$numOfLoops = floor($num_rows/$interval);
echo 'Num Of Loops: '. $numOfLoops.'<br/>';
$rest = $num_rows - ($interval*$numOfLoops);
echo 'Rest: '.$rest.'<br/>';
$arrFound = [];
// For loop will control the results sets divided by 4
for ($i = 1; $i <= $numOfLoops; $i++) {
// To add the offset after the first set
if ($offset > 0) {
$limitValues = $offset. ", " . $interval . " ";
} else {
$limitValues = $interval;
}
$sqlquery = $q . " LIMIT " . $limitValues;
$avg = 0;
$total = 0;
//Total Found Use To Divide by
$result = mysqli_query($conn, $q);
$num_rows = mysqli_num_rows($result);
//echo 'Total Found: '.$num_rows;
$j = 0;
foreach (mysqli_query($conn, $sqlquery) as $results) {
if ($j == 0) {
echo $results['purdate'];
$arrFound[$i]['date'] = $results['purdate'];
}
// Do Something
$total += $results['score'];
$avg = $total / $interval;
$j++;
}
$arrFound[$i]['avg'] = $avg;
echo $avg . '<br/>';
$offset += $interval;
}
print_r($arrFound);
}
[edit2] In the end I also need the start purdate of each interval to be returned in the array as well.
[edit3]
For another table I need something similar but now I need to output also per code. The query looks like this :
$q = "SELECT date, code
FROM Records2
WHERE studentid = 131
AND date >= '2017-02-10' AND date <= '2017-06-13'
AND code IN ('P','T','L','U')
ORDER BY date ASC;";
and with the help of astrangeloop I like this to output per code as well. This is to plot a chart where x-axis will be the start date and y-axis will plot the number of P, T, L or U. (well mostly P and T)
example chart would be:
$intervals = array();
foreach (array_chunk($records, $interval_size) as
$interval_records) {
$first_record = reset($interval_records);
$interval = array(
'start' => $first_record['date'],
'codes' => array_column($interval_records, 'code'),
/*Is It Possible to Order and/or Calculate with the
code values? Like another array*/,
);
$intervals[] = $interval;
}
return $intervals;
At the moment this returns:
[5] => Array
(
[start] => 2017-03-23
[codes] => Array
(
[0] => P
[1] => P
[2] => P
[3] => P
[4] => P
)
[count] => 5
)
And I like the js output which looks likes this:
['start', #num_of_codes_P, #num_of_codes_T, #num_of_codes_L, #num_of_codes_U,'count'],
['start', #num_of_codes_P, #num_of_codes_T, #num_of_codes_L, #num_of_codes_U,'count'],
For future reference I think I found a workable solution (not using array_count_values because of Notice error) and this is how I get the codes:
$records = getRecordsWithIntervals($records, 5);
foreach ($records as $record) {
echo count(array_keys($record['codes'], 'P')).', '.count(array_keys($record['codes'], 'U')).'<br/>';
}
[edit4] I should ask this in a seperate question, but how do I add the ability to set the interval_size to weekly, monthly or customize quarters (custom beginDate-endDate = divide a educational year in 4 parts or more)
