0

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

tim.baker
  • 3,109
  • 6
  • 27
  • 51
Joe
  • 51
  • 2
  • 11
  • 1
    Check out http://stackoverflow.com/questions/7693613/select-most-common-value-from-a-field-in-mysql – Daniel Aug 31 '16 at 21:31
  • Can I point out that your code is outdated and you should be using MySQLi. This code will not work in more recent versions of PHP as what you are doing has been depreciated and removed. http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – tim.baker Aug 31 '16 at 21:50

2 Answers2

3

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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Think you need a group as well - `count(id)` returns the entire number of rows, not the total per variant of `id`: http://sqlfiddle.com/#!9/34c75d/4/0 – scrowler Aug 31 '16 at 21:30
  • 1
    @RobbieAverill: yeah, thanks... it's been a long day. – Marc B Aug 31 '16 at 21:40
  • it show error Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in – Joe Aug 31 '16 at 22:05
  • @MarcB it show error Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in – Joe Aug 31 '16 at 22:27
0

Try the following query:

select id, count(id) cnt 
from name 
group by id 
order by cnt desc 
limit 1
Andrej
  • 7,474
  • 1
  • 19
  • 21