这个sql问题的最佳解决方案

Question

I have following sql query in my header file:

// This query is getting points scored by user1, this number changes
// each time user scores more points

$query_points = mysql_query("SELECT * FROM quiz WHERE `sender` = user1 AND points= '1'");
$points = mysql_num_rows($query_points);

Now if there 3 or more users who are logged in how can i compare their individual points if i want to?

Do i write this sql query 3 times like this:

$query_points = mysql_query("SELECT * FROM quiz WHERE `sender` = user1 AND points= '1'");
$points1 = mysql_num_rows($query_points);

$query_points = mysql_query("SELECT * FROM quiz WHERE `sender` = user2 AND points= '1'");
$points2 = mysql_num_rows($query_points);

$query_points = mysql_query("SELECT * FROM quiz WHERE `sender` = user3 AND points= '1'");
$points3 = mysql_num_rows($query_points);

Than compare $points1, $points2 and $points3 and post results? Or is there a better way to do this?

Answer 1

Use COUNT, GROUP BY, ORDER BY and LIMIT :

  SELECT sender
    FROM quiz
   WHERE points = 1
GROUP BY sender
ORDER BY COUNT(*) DESC
   LIMIT 3

Which would select the three first senders who have the highest number of points.

By the way, if you only need the number of lines, don't do a SELECT * query and use mysql_num_rows. Instead, just do a SELECT COUNT(*) query.