Orionsbelter Posted February 20, 2011 Share Posted February 20, 2011 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. Link to comment https://forums.phpfreaks.com/topic/228249-most-common-mysql-field/ Share on other sites More sharing options...
Pikachu2000 Posted February 20, 2011 Share Posted February 20, 2011 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 Link to comment https://forums.phpfreaks.com/topic/228249-most-common-mysql-field/#findComment-1177026 Share on other sites More sharing options...
Orionsbelter Posted February 20, 2011 Author Share Posted February 20, 2011 Thank you very much will try it Link to comment https://forums.phpfreaks.com/topic/228249-most-common-mysql-field/#findComment-1177028 Share on other sites More sharing options...
Orionsbelter Posted February 20, 2011 Author Share Posted February 20, 2011 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. Link to comment https://forums.phpfreaks.com/topic/228249-most-common-mysql-field/#findComment-1177029 Share on other sites More sharing options...
Pikachu2000 Posted February 20, 2011 Share Posted February 20, 2011 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."; Link to comment https://forums.phpfreaks.com/topic/228249-most-common-mysql-field/#findComment-1177038 Share on other sites More sharing options...
Orionsbelter Posted February 20, 2011 Author Share Posted February 20, 2011 Thank you this worked Link to comment https://forums.phpfreaks.com/topic/228249-most-common-mysql-field/#findComment-1177041 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.