How can I loop through an array while averaging the values of one element and only keep the newly averaged field in PHP?

Question

I have database that contains scores which are stored daily. I want to average each months scores for each user. So far I have this:

DB structure:

id | name    | tscore            | added 

int| string  | float(100 or less)| date(2014-01-01 16:34:22)

Code:

while($row = mysql_fetch_assoc($getChartData)){ // Data from MySQL
    $added_date = explode(' ',$row['added']); // Date formate 2014-01-01 16:34:22
    $chartData[] = array(
        'id'     => $row['name'],
        'tscore' => $row['tscore'],
        'added'  => $added_date[0] // Here I take the month only
    );
}
if($_POST['range'] == 'month'){
    foreach($chartData as $key => $value){
        $added = explode('-',$chartData[$key]['added']);
        $count = 1;
        foreach($chartData as $key2 => $value2){
            $added2 = explode('-',$chartData[$key2]['added']);
            if($chartData[$key]['id'] === $chartData[$key2]['id'] && $added[1] === $added2[1]){ // if user is the same and the month is the same, add the scores together, increment counter, and unset 2nd instance
                $chartData[$key]['tscore'] = ((float)$chartData[$key]['tscore'] + (float)$chartData[$key2]['tscore']);
                $count++;
                unset($chartData[$key2]);
            }     
        }
        $chartData[$key]['tscore'] = ($chartData[$key]['tscore']/$count); // Average all the scores for the month.
    }
}

The problem is this method is deleting all the elements of the $chartData array. What can I try to resolve this?

Answer 1

You should try to solve it with MySQL. Try something like this (replace 'your_scores_table' with your table name):

SELECT
    Score.name, 
    AVG(Score.tscore) AS `avg`,
    CONCAT(YEAR(Score.added), '-', MONTH(Score.added)) AS `year_month`
FROM 
    your_scores_table AS Score
GROUP BY 
    Score.name ASC, 
    YEAR(Score.added) DESC, 
    MONTH(Score.added) DESC
;