PHP/SQL - Add values together if ID is the same in foreach()

Question

I am trying to show statistics for goals scored by players, however sometimes the same player is added to the database with the same playerID, when a player is added twice how can I add the values together to show it as a total, rather than echo the player twice.

db structure example:

playerID | Goals | Season | leagueID
   1         5       1        1
   2         1       1        1
   1         2       1        2
   5         3       1        1
   1         3       2        2
   2         2       2        1

php:

$query = $db->query('SELECT * FROM playerstats ORDER BY goals DESC LIMIT 30');
$num = $query->num_rows;
if($num > 0) {

foreach($query as $row) {
$playerID = $row['playerID'];
$goals = $row['goals'];

echo '
<tr>
<td>'.$playerID.'</td>
<td>'.$goals.'</td>
</tr>

';

}

}

This would show playerID 1, 3 seperate times. How can I make it show playerID 1 just once with all the goals added together (10)

I have tried changing the query to: SELECT DISTINCT * FROM playerstats ORDER BY goals DESC LIMIT 30 but this made no difference.

Answer 1

Group BY will help you:

<?php
$query = $db->query('SELECT SUM(p.goals) as total_goals, 
p.playerID, p.leagueID, p.Season 
FROM playerstats as p 
GROUP BY p.playerID, p.leagueID, p.Season
ORDER BY total_goals DESC LIMIT 30');

$num = $query->num_rows;
if($num > 0) {
foreach($query as $row) {        
   echo '
    <tr>
    <td>'.$row['playerID'].'</td>
    <td>'.$row['total_goals'].'</td>
    </tr>

    ';

  }

}

Please note, that my query will group also by season and leagues, if you want total goals throughs seasons and leagues, your group by will be: GROUP BY p.playerID