derekshull Posted October 9, 2012 Share Posted October 9, 2012 I have a database full of cities. The table name is "needs" What I'm looking for is a php script that looks through all the cities ("city" is the name of the column in the database) and if there is a city then it will post on the page the city name and how many records are in that city. Example is if there are 10 records in Jonesboro and 3 in Harrisburg then the page will look like this: Jonesboro (10) Harrisburg (3) Any help? I'm new to php :-/ Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/ Share on other sites More sharing options...
requinix Posted October 9, 2012 Share Posted October 9, 2012 (edited) A simple JOIN (query) can do this, like "SELECT city name, COUNT(need) FROM city table JOIN needs table ON city table's city = needs table's city GROUP BY city table's city" Edited October 9, 2012 by requinix Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384055 Share on other sites More sharing options...
mikosiko Posted October 9, 2012 Share Posted October 9, 2012 In an early similar question the answer from one of our members was: ... and it apply here too If you want advice on the general idea, this belongs in Application Design. If you want code, it belongs in Freelance. If you need help with code you've written, post it and explain the problem. Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384056 Share on other sites More sharing options...
derekshull Posted October 10, 2012 Author Share Posted October 10, 2012 mikosiko I'll do that next time sorry. requinix there is only one table called "needs" but it has a column called "city" in it. The code you provided looks like it has multiple tables. Am I wrong? Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384210 Share on other sites More sharing options...
derekshull Posted October 10, 2012 Author Share Posted October 10, 2012 I'm not trying to join them together I'm just trying to display any cities that are in the "city" column of the table called "needs" and then beside that name of the city I want it to display how many records there are of that city. Thus: Example is if there are 10 records in Jonesboro and 3 in Harrisburg then the page will look like this: Jonesboro (10) Harrisburg (3) Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384211 Share on other sites More sharing options...
ManiacDan Posted October 10, 2012 Share Posted October 10, 2012 SELECT city, count(city) as num FROM needs GROUP BY city ORDER BY count(city) DESC Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384215 Share on other sites More sharing options...
derekshull Posted October 10, 2012 Author Share Posted October 10, 2012 Ok i got it to show the city and show a number next to the city but even when I have multiple records for that city it only says there is one record....this is what I have: $query = mysql_query("SELECT state, city, count(city) as num FROM needs WHERE status='posted' GROUP BY city ORDER BY count(city) DESC"); $citycount = count(city); while ($rows = mysql_fetch_array($query)): $city = $rows['city']; $state = $rows['state']; echo " $city, $state ($citycount)<br><br>"; endwhile; Where am I screwing up at? Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384217 Share on other sites More sharing options...
ManiacDan Posted October 10, 2012 Share Posted October 10, 2012 (edited) Do this by hand. Find a city and state combo with more than one row in need where status is 'posted'. Is there such a thing? Edited October 10, 2012 by ManiacDan Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384218 Share on other sites More sharing options...
mikosiko Posted October 10, 2012 Share Posted October 10, 2012 $query = mysql_query("SELECT state, city, count(city) as num FROM needs WHERE status='posted' GROUP BY city ORDER BY count(city) DESC"); $citycount = count(city); while ($rows = mysql_fetch_array($query)): $city = $rows['city']; $state = $rows['state']; echo " $city, $state ($citycount)<br><br>"; endwhile; Where am I screwing up at? well... mysql count() and php count() function are totally different... why are you using this: $citycount = count(city); if you already counted in mysql? the right way to do it is delete that line and do it in the same way that you did for $city and $state in your loop. Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384222 Share on other sites More sharing options...
derekshull Posted October 10, 2012 Author Share Posted October 10, 2012 What do I replace that line with though? It needs to count how many records there are of each city. I'm confused how that is done. Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384228 Share on other sites More sharing options...
mikosiko Posted October 10, 2012 Share Posted October 10, 2012 (edited) $query = mysql_query("SELECT state, city, count(city) as num FROM needs WHERE status='posted' GROUP BY city ORDER BY count(city) DESC"); while ($rows = mysql_fetch_array($query)): $city = $rows['city']; $state = $rows['state']; $citycount = $rows['num']; // num is holding your count by city echo " $city, $state ($citycount)<br><br>"; endwhile In another note, do you want the count only by city (no matter the state) or the count by the pair city-state?... if the last case is what you want you must GROUP BY city AND state Edited October 10, 2012 by mikosiko Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384229 Share on other sites More sharing options...
ManiacDan Posted October 10, 2012 Share Posted October 10, 2012 Use curly braces instead of while: and endwhile: while ( $something ) { //loop contents } Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384238 Share on other sites More sharing options...
derekshull Posted October 10, 2012 Author Share Posted October 10, 2012 miosko I did group by city and state and it only showed me one city...weird but the other code works thank! Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384246 Share on other sites More sharing options...
derekshull Posted October 10, 2012 Author Share Posted October 10, 2012 I have one more issue So I have a record that has Jonesboro, AR and Jonesboro, GA When I do this code it groups Jonesboro, GA into Jonesboro, AR How do I get it to seperate city and state. Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384253 Share on other sites More sharing options...
derekshull Posted October 10, 2012 Author Share Posted October 10, 2012 $query = mysql_query("SELECT state, city, count(city) as num, count(state) as statenum FROM needs WHERE status='posted' GROUP BY city ORDER BY state DESC"); while ($rows = mysql_fetch_array($query)): $city = $rows['city']; $state = $rows['state']; $citycount = $rows['num']; // num is holding your count by city echo " <a href='http://www.1511project.com/node/browseresults.php?city=$city&state=$state'>$city, $state ($citycount)</a><br><br>"; endwhile This is what I have right now Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384255 Share on other sites More sharing options...
ManiacDan Posted October 10, 2012 Share Posted October 10, 2012 How do I get it to seperate city and state. Remember when someone asked you... In another note, do you want the count only by city (no matter the state) or the count by the pair city-state?... if the last case is what you want you must GROUP BY city AND state You've also added count(state) which is unnecessary, get rid of it. Your new query: $query = mysql_query("SELECT state, city, count(city) as num FROM needs WHERE status='posted' GROUP BY state, city ORDER BY state, city"); Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384256 Share on other sites More sharing options...
derekshull Posted October 10, 2012 Author Share Posted October 10, 2012 Ah when I did that earlier I put GROUP BY city AND state.....when I should have put city, state. Thanks everyone! Quote Link to comment https://forums.phpfreaks.com/topic/269277-list-cities-and-how-many-records-match-that-city/#findComment-1384258 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.