Most Common Mysql Field

[Orionsbelter]

In my mysql database i have a field that records peoples details one part of the details is their county/region they live in. It records the county in a normal varchar field however i need a simple php script that searching the database and finds the most common county so i can then return to the screen where the most popular region for my members.

 

Thank you for reading.

[Pikachu2000]

Off the top of my head, but this syntax should work.

SELECT country, COUNT(country) FROM table GROUP BY country ORDER BY COUNT(country) DESC LIMIT 1

[Orionsbelter]

Thank you very much will try it

[Orionsbelter]

i tried $mostPopCounty=mysql_query("SELECT County, COUNT(County) FROM members GROUP BY County ORDER BY COUNT(County) DESC LIMIT 1");

 

however i get a return of Resource id #13.

[Pikachu2000]

mysql_query() returns a result resource, and you can't echo a result resource. You have to do something to get the values out of it, (typically) using one of the mysql_fetch_* functions. In this case, mysql_fetch_row() would be fine.

 

$query = "SELECT County, COUNT(County) FROM members GROUP BY County ORDER BY COUNT(County) DESC LIMIT 1";
$result = mysql_query($query);
$arr = mysql_fetch_row($result);
echo "Most popular county is: {$arr[0]}, with {$arr[1]} members.";

[Orionsbelter]

Thank you this worked