Have Geek Brain

How to read and rank data in php as efficiently as possible

I am not new to programming, but I am new to MySQL and PHP. I am wondering what is the most efficient way to do the following. I don't need the actual code, but only the steps.

I have a list of user reviews, let's just say:

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 reviewers who have done the most reviews, i.e.

USER REVIEWS
Bob  4
Fred 2
John 1

What's the best way to do this, in the most efficient way

Hooray!!

+1


a source to share


2 answers


As your request:



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


a source


Sometimes it is 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 sort and stuff, because it's just more efficient at this sort of thing.

  • Executes an SQL query and sorts the results
  • Let PHP check if there are any results.
  • Let PHP convert results to array / object
  • Let PHP tranverse array, extract the content you want
  • Print the desired result.
+3


a source






More articles:


All Articles