[SOLVED] Display most common entry in a mysql column.

[kmutz22]

The column is called 'nm' and i want to display its most common entry.

 

I tried this:

 
<?php
$sql="select
nm, 
count(nm) as total
from users
group by 
nm
order by
total desc";

$res=mysql_query($sql);
$obj=mysql_fetch_object($res);
$nm=$obj->nm;

print $nm;
?>

 

But it doesn't work and doesn't display anything. I really don't know what I'm doing or even if I'm on the right lines so any help would be greatly appreciated. Thanks.

 

 

[revraz]

Give an example of what is in that field in your DB.

[premiso]

Add in some error checking:

 

$res=mysql_query($sql) or die("SQL WAS {$sql} <br /> Error was:" . mysql_error());
$obj=mysql_fetch_object($res);
$nm=$obj->nm;

 

And you will see why. As to why you have your sql spread out like that, I have no clue.

[kmutz22]

Oh i just realised. The most common entry is nothing.

 

The sql looks like that because I was following a tutorial and they had done it like that, I don't really know why..

 

Thanks for everyone's help, can't believe I missed that one though!

 

 

 

[kmutz22]

Hi, I know I clicked that this topic was solved, but I've been trying to do this for a while now. If the most common entry is nothing, how do i make it choose the most common from all the entries that are not empty?

 

thanks for any help

[phil88]

$sql="select

nm,

count(nm) as total

from users

WHERE nm IS NOT NULL

group by

nm

order by

total desc";

 

I guess?

[kmutz22]

thanks