Jump to content

List Cities And How Many Records Match That City.


derekshull

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

 

 

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

Link to comment
Share on other sites

$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 by mikosiko
Link to comment
Share on other sites

$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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.