CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 105

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

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

And from the Members table

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

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!

Upvotes: 0

Views: 958

Answers (1)

Hazzit
Hazzit

Reputation: 6882

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.

Upvotes: 1

Related Questions