Tuesday, 1 October 2013

SUM of a values in a column in MySQL and group them

SUM of a values in a column in MySQL and group them

I need to design a leader board for a quiz website that I am making. I
store the user scores in a database table called 'user_record' which has
the following structure:
'user_id' - varchar(254) -which holds the user id for a particular score
'score' - int(2) -which holds the score
'time' - timesatamp -which has the CURRENT_TIMESTAMP
'date' - date -which has the CURDATE()
Now I need to sum the values for each user_id for the current date and
display the top 5 in a descending order. I have the following code for
this, however it doesn't seem to be working. Any help?
<table>
<tr>
<th>User Id</th>
<th>Score</th>
</tr>
<?php
include "connection.php";
$w=mysql_query("SELECT user_id,SUM(Score) from score WHERE
date=CURDATE() GROUP BY user_id ORDER BY score DESC limit 5");
$b=$row=mysql_fetch_array($w);
while($b)
{
$user_id=$row['user_id'];
$score=$row['score'];
?>
<tr>
<td><?php echo $user_id;?></td>
<td><?php echo $score; ?></td>
</tr>
<?php
}
?>
</table>

No comments:

Post a Comment