Sum values in each group with a loop

Question

I have a while loop that gives this result:

Userid      Point
1           10
1           15
2           5
2           10
3           8
3           2

How can I sum the userid points and output with highest number first, like this:

Userid     Point
1          25
2          20
3          10

Is there any "foreach", "for" or any other method that can accomplish such result?

The code:

include ('variables.php');

//Fetch data from matchdata table
$q = "SELECT userid, matchid, homescore, awayscore FROM predictiondata ORDER BY userid ASC";
$r = mysqli_query($mysqli, $q);

while ($row = mysqli_fetch_array($r)) {
    //Define predictions
    $predhome = $row['homescore'];
    $predaway = $row['awayscore'];

    //Fetch gameresults
    $qres = "SELECT id, homescore, awayscore, bonuspoints FROM matches WHERE id = ".$row['matchid']."";

    $rres = mysqli_query($mysqli, $qres);
    $result = mysqli_fetch_array($rres);
    $homescore = $result['homescore'];
    $awayscore = $result['awayscore'];
    $bonus = $result['bonuspoints'];
    $id = $result['id'];



    //Calculate points
            if ($homescore == $predhome && $awayscore == $predaway && $homescore != '') { $result_point = $correct_score + $correct_result + $correct_number + $correct_number; } 
            else if ($homescore == $predhome && $awayscore != $predaway OR $homescore != $predhome && $awayscore == $predaway) { $result_point = $correct_result + $correct_number; } 
            else if ($predhome > $predaway && $homescore > $awayscore OR $predhome < $predaway && $homescore < $awayscore) { $result_point = $correct_result; } 
            else if (is_null($predhome) OR $homescore == '') { $result_point = 0; } 
            else { $result_point = 0; }

            if ($homescore == $predhome && $awayscore == $predaway && $homescore != '') { $bonus = $bonus; } 
            else if (is_null($predhome) OR $homescore == '') { $bonus = 0;  } 
            else { $bonus = 0;  }

            if (is_null($predhome) OR $homescore == '') { $total_point = 0; } 
            else { $total_point = $result_point + $bonus; } 


            //Calculate total round sum

            $total_roundsum = $result_point + $bonus;
            //echo $username.' - '.$total_roundsum.'<br />';

    if($total_roundsum != 0) {
    echo $row['userid']. ' - ' .$total_roundsum.'<br />';
    }
}

At the moment, the code only echo's the results. The "variables.php" holds the $correct_score + $correct_result + $correct_number variables.

Use an associative array with the user id as the key and the summed value as the point. Add the points (start with 0 if they key does not exist) and in the end output the array. Post the code you have till now if you want an answer, otherwise it is kind of difficult to help you. — Rohan Prabhu, Jul 15 '14 at 19:15
Where does the data come from? How is it stored/produced? If it is from SQL, then it might be trivially handled with changing the query. — user2864740, Jul 15 '14 at 19:17

Dan Cameron · Accepted Answer · 2014-07-15T23:06:13.323

Assuming the two columns are an associated array -- $users_and_points.

$points_array = array();
foreach ( $users_and_points as $user_id => $point ) {
    if( !isset( $points_array[$user_id] ) {
        $points_array[$user_id] = 0;
    }
    $points_array[$user_id] += $point;
}
// newly associated array with calculated totals
$points_array;

Update

Based on your code above instead of echoing build an array of users and points.

echo $row['userid']. ' - ' .$total_roundsum.'<br />';

can be replaced with:

if( !isset( $points_array[$row['userid']] ) {
    $points_array[$row['userid']] = 0;
}
$points_array[$row['userid']] += $total_roundsum;

That will give you an associated array of user ids and associated points.

print_r( $points_array );

Note: Set the variable before your loop. Example, $points_array = array();

I have updated the question with the php code. How can I make the $row['userid'] and $total_roundsum as an associated array? — user2282217, Jul 15 '14 at 19:33

Sum values in each group with a loop

1 Answers1

Linked