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. Quote Link to comment 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 Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment 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."; Quote Link to comment Share on other sites More sharing options...
Orionsbelter Posted February 20, 2011 Author Share Posted February 20, 2011 Thank you this worked Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.