Adding up values in a SQL database and displaying them in a table using PHP

Question

I have a table called 'Results' of member_id's and scores. There will be several rows for each member_id and I want to add together the score for each member_id and output them in a table with two columns, Member an Score.

At the same time, I need to join on a table called Members (which shares a common field, member_id, with Results) in order to extract the value 'fullname' and display that in the Member column when the data is outputted.

I think I can do the whole lot in one SQL query and then simply output it into a table (which I can already do) but I'm struggling to write a query that can output the data correctly.

Here is a sample of data from the Results table

member_id: score:
45: 5:
45: 6:
26: 7:
26: 2:

And from the Members table

member_id: fullname:
45: Bob Jones:
26: John Smith:

And how I'm hoping the data can be returned from a SQL query

Member: Score:
Bob Jones: 11:
John Smith: 9:

Ignore the colons, I'm just using them here to separate values so they are easier to see. Bear in mind that there will be an unknown number of users and unknown number of rows in the Results table that will have to be iterated through.

Edit:

Here is what I now have

$sql = "SELECT Members.fullname 'Name', Results.score 'Score' FROM Members
JOIN Results ON Results.member_id=Members.member_id
GROUP BY Members.member_id, Members.fullname
";
  $result = mysql_query($sql)or die(mysql_error());


  echo "<table>";
  echo "<tr>

  <th>Name</th>
  <th>Score</th>
  </tr>";

  while($row = mysql_fetch_array($result)){
  $names      = $row['Name'];
  $score     = $row['Score'];

  echo "<tr>
    <td style='width: 100px;'>".$names."</td>
    <td style='width: 100px;'>".$score."</td>
    </tr>";

}
echo "</table>";

?>

Edit again: post corrected and problem now solved. Thanks!

Hazzit · Accepted Answer · 2013-03-30 22:02:18Z

1

This select should do the trick:

SELECT Members.fullname, SUM(Results.score) FROM Members 
LEFT JOIN Results ON Members.member_id=Results.member_id
GROUP BY Members.member_id, Members.fullname

I used a LEFT JOIN so that members without any result will still show up (with NULL as sum). Using a regular join will leave those out.

edited Mar 30, 2013 at 22:02

answered Mar 30, 2013 at 21:40

Hazzit

6,8921 gold badge29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Chris Over a year ago

Hi, thanks for the response. If I change score.member_id to Results.member_id it loads the table but no results are being returned I'm afraid.

Chris Over a year ago

I added 'Name' and 'Score' after the SELECT Members.fullname and SUM(Results.score) and it's now displaying properly and giving me the names, but no the scores.

Hazzit Over a year ago

sry, I had a mistake in the join condition. it should read "Results.member_id" where I wrote "score.member_id".

Chris Over a year ago

I've updated the post to contain the sql query and the code I'm using to generate the table. Can you see another reason why I'm getting the names through correctly but no scores? There are several rows in the Results table, and removing the LEFT on the join does only output a list of users who have results, but still no total scores.

Chris Over a year ago

Actually, I've just seen my own mistake :-) I accidentally deleted <td style='width: 100px;'>".$score."</td> from the table. You're query works perfectly, I knew there would be a SQL wizard out there somwhere who'd be able to work it out, thanks!!

|

Collectives™ on Stack Overflow

Adding up values in a SQL database and displaying them in a table using PHP

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related