PHP SQL Query to get the most common value in the table

I’m trying to have my query count the rows and have it return the most common name in that list, then from that it counts how many times that name appears and outputs the name and the amount of times its there This is the code I’m using:

$vvsql = "SELECT * FROM votes WHERE sid=? ORDER BY COUNT(*) DESC LIMIT 1";
$vvresult = $db->prepare($vvsql);
$vvresult->execute(array($_GET['id']));
$vvcount = $vvresult->rowCount();
    foreach ($vvresult->fetchAll(PDO::FETCH_ASSOC) as $row) { 
            echo $row['username'];
            echo $vvcount;
    }

JavaScript
​x
 
$vvsql = "SELECT * FROM votes WHERE sid=? ORDER BY COUNT(*) DESC LIMIT 1";$vvresult = $db->prepare($vvsql);$vvresult->execute(array($_GET['id']));$vvcount = $vvresult->rowCount();    foreach ($vvresult->fetchAll(PDO::FETCH_ASSOC) as $row) {             echo $row['username'];            echo $vvcount;    }​

However, it just displays the first username in the table and counts up the entire table. I’m pretty new to this so I’m sorry if this is a bad post or if it didn’t make much sense.

Answer

You would seem to want:

SELECT name, COUNT(*) as cnt
FROM votes 
GROUP BY name
ORDER BY COUNT(*) DESC
LIMIT 1;

JavaScript
 
SELECT name, COUNT(*) as cntFROM votes GROUP BY nameORDER BY COUNT(*) DESCLIMIT 1;​

Note the GROUP BY. You may also want to filter by sid but your question makes no mention of that.

Advertisement

Answer