Joe Joe - 3 months ago 9
MySQL Question

Get the most common value from MySQL column in PHP

I have a simple table in phpmyadmin called

name
with only 1 column
id
which is look like this

id
--------
a
b
a
a
a
a
b


Now i want to show most used id on my PHP page.
Here is my code:

$sql_a = mysql_query(SELECT id FROM name WHERE id='a');
$count_a = mysql_num_rows($sql_a);
$sql_b = mysql_query(SELECT id FROM name WHERE id='b');
$count_b = mysql_num_rows($sql_b);

if($count_a > $count_b)
{
$most_used_id = "A";
}
else
{
$most_used_id = "B";
}
echo "<h1>MOST USED ID IS $most_used_id</h1>";


Currently I have only 2 types of id but in future I will have multiples (maybe 300+) id's, so is there a way to make query dynamic and get only most used value

Answer

That's very redundant code. You'd be better off with

SELECT id, count(id) AS cnt
FROM name
ORDER BY cnt DESC
GROUP BY id
LIMIT 1

that'll give you the "most popular" id value, and just how popular it is. If you need to get the count of all the ids, then remove the limit line.