List Cities And How Many Records Match That City.

[derekshull]

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 :-/

[requinix]

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

[mikosiko]

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.

[derekshull]

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?

[derekshull]

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)

[ManiacDan]

SELECT city, count(city) as num FROM needs GROUP BY city ORDER BY count(city) DESC

[derekshull]

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?

[ManiacDan]

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

[mikosiko]

 

 

$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.

[derekshull]

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.

[mikosiko]

$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

[ManiacDan]

Use curly braces instead of while: and endwhile:

 

while ( $something ) {
 //loop contents
}

[derekshull]

miosko I did group by city and state and it only showed me one city...weird but the other code works thank!

[derekshull]

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.

[derekshull]

$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

[ManiacDan]

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");

 

[derekshull]

Ah when I did that earlier I put GROUP BY city AND state.....when I should have put city, state. Thanks everyone!