how to count and rank data in php as efficiently as possible

Question

I'm not new to programming, but i'm new to MySQL and PHP. I'm wondering what the most efficient way of doing the following. I dont need the actual code, but just the steps.

I have a list of user reviews, lets say simply:

USER    REVIEW  
Bob     nice
John    good
Fred    bad
Bob     poor
Bob     the best
Fred    medicre
Bob     shiny

I want to be able to create a list of the reviewers that have made the most reviews, ie

USER REVIEWS
Bob  4
Fred 2
John 1

WHat's the best way of going about this, in the most efficient way

cheers!!

Answer 1

As your query:

SELECT user, COUNT(*) AS reviews
FROM <table name>
GROUP BY user
ORDER BY reviews DESC;

Answer 2

Sometimes it's just easier for programmers to explain themselves with code:

// Credit to Chad Birch for the query...
$query = "SELECT user, COUNT(*) AS reviews 
          FROM <table name>
          GROUP BY user
          ORDER BY reviews DESC";

$res = mysql_query($query);
if(mysql_num_rows($res) > 0) {
    $res_array = mysql_fetch_assoc($res);
    $list = "<h1>USER REVIEWS</h1>\n";
    foreach($res_array as $user) {
        $list .= $user['user'].' '.$user['reviews']."<br />\n";
    }
}

echo $list;

So, basically, 9/10 times, it's best to let SQL do the sorting and stuff because it's just way more efficient at stuff like that.

1. Do SQL query and let it sort your results
2. Let PHP check to see if there are any results returned.
3. Let PHP convert results to array/object
4. Let PHP tranverse array, extract needed content
5. Print your desired output.