Joe Joe - 3 months ago 11
MySQL Question

get most commen value from mysql colum 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 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.

Comments