CODEWITHSUNDEEP
phpmysqldatabasecount
qweqweqwe
qweqweqwe

Reputation: 343

Show data based on number of appearances MYSQL

I am trying to list the top 3 names that appear most often in my MySql Database.

This is what I'm using to do:

        $nameQuery = "SELECT PeopleName, COUNT(*) AS totalNumber FROM finaldb ORDER BY        COUNT(PeopleName) LIMIT 5";
        $nameResult = mysql_query($nameQuery);
        while($data = mysql_fetch_array($nameResult)) {

              $name = $data['totalNumber'];
        }

 echo $name;

However, that doesn't seem to work. Any suggestions?

My DB consists of: PeopleName, ID and is called finaldb.

Upvotes: 1

Views: 279

Answers (4)

aynber
aynber

Reputation: 23001

You should return 1 result with that query, but that's it. You need to add a GROUP BY clause to your SQL:

SELECT PeopleName, COUNT(PeopleName) AS totalNumber FROM finaldb GROUP BY PeopleName ORDER BY COUNT(PeopleName) DESC LIMIT 5

You can also check what your query should return by running it on the mysql command line or inside of phpmyadmin.

Your loop is only assigning the latest totalNumber to $name. If you want to echo all of the data, try this:

    while($data = mysql_fetch_array($nameResult)) {

          echo "{$data['PeopleName']} - {$data['totalNumber']}\n";
    }

For adding data, just keep adding column names:

          echo "{$data['PeopleName']} - {$data['totalNumber']} - {$data['Gender']} - {$data['Age']}\n";

Upvotes: 1

pmcgovern
pmcgovern

Reputation: 71

SELECT 
    PeopleName
    count(PeopleName)
FROM
    finalDB
GROUP BY 
    PeopleName
ORDER BY
    count(PeopleName) DESC
LIMIT 0,3

Upvotes: 1

Marc B
Marc B

Reputation: 360772

Your loop isn't preserving the names you pull out, it simply overrites the PREVIOUS name with the next value. You need to build an array of values, or at least do your output inside the loop. e.g.

$names = array();
while(...) {
   $names[] = array('name' => $data['PeopleName'], 'total' => $data['totalNumber']);
}
var_dump($names);

Upvotes: 1

leticia
leticia

Reputation: 2388

You are missing the Group By, try this query:

$nameQuery = "SELECT PeopleName, COUNT(PeopleName) AS totalNumber FROM finaldb GROUP BY PeopleName ORDER BY        COUNT(PeopleName) LIMIT 5";

Upvotes: 2

Related Questions