View Full Version : How many members live in which city

11-30-2006, 04:32 PM

// This is an admin page that lists current registered members' names and the city they live in.

// I have following array in which I keep the city names, the actual array has 81 cities.

$citiesarray = array(
1 =>"Berlin",
2 =>"Istanbul",
3 =>"Newyork",
4 =>"Stockholm");

function GetCity($city) {
global $citiesarray;
return $citiesarray[$city];

// I also keep member information in a table, keep it simple:

$result = mysql_query("SELECT ID,name,city FROM members");

echo '<table>';
while ($row = mysql_fetch_array($result)) {
echo '<tr>';
echo '<td>' . $row['name'] . '</td>';
echo '<td>' . GetCity($row['city']) . '</td>';
echo '</tr>';
echo '</table>';


What I need is the correct syntax to show the admin, how many members are living in which city, in a descending order, like

Berlin: 22 Members
Stockholm: 18 Members
Istanbul: 12 Members
Newyork: 6 Members

I could do it with loops, but I guess this is possible in a single query (COUNT?)

Later, I will try to build a pie graph based on these values.

Can someone help?

11-30-2006, 04:55 PM
It should be a matter of the SQL query.

SELECT ID, name, city, COUNT(ID) AS members_in_city FROM members GROUP BY city ORDER BY members_in_city

11-30-2006, 05:13 PM
That's it.
Here is the code.

$result_cities = mysql_query("SELECT ID, name, city, COUNT(ID) AS members_in_city FROM members WHERE admin='0' GROUP BY city ORDER BY members_in_city DESC") or die(mysql_error());

while($row = mysql_fetch_array($result_cities)){
echo GetCity($row['city']) . ': '. $row['members_in_city'] .' members.';
echo "<br />";

As I keep the values in an array and the cities are ordered by a number, cities with same values are not ordered alphabetically. I can't add a THEN BY city to the query. Is there a workout?

11-30-2006, 06:25 PM
Your code is incorrect and could produce invalid results.

First you don't need the ID or name column, as you said you wanted the total for each city, not individual people in that city.

Secondly, for future reference, you need to include all columns in your select statement in your group by clause unless it is an aggregate like AVG or COUNT. if you don't you can end up with incorrect results.

Removing ID and name in this case will solve the problem. I'm just letting you know going forward it is a bad idea to leave out columns.

12-01-2006, 02:18 PM
The statement was provided by marek.

I gave your suggestion a try and changed the query to:

$result_cities = mysql_query("SELECT city, COUNT(ID) AS members_in_city FROM members WHERE admin='0' GROUP BY city ORDER BY members_in_city DESC") or die(mysql_error());

As a result, the values have not changed. Only the order the rows are generated. The order between cities which have same results.

12-01-2006, 05:38 PM
The statement was provided by marek.
I just copied the fields over from your initial SQL query. :p Guelphdad is right and the other fields will contain bogus.
You could do an additional order by "city ASC" to have the cities with the same number of members ordered alphabetically.

12-02-2006, 09:01 AM
That doesn't work because city field keeps the city number, not the name. As I said, I keep the city names in an array (see above). So at the end the numbers are sorted and the corresponding names are calculated with the function later.

12-02-2006, 12:15 PM
Why not have them in the DB?

12-04-2006, 01:54 PM
You mean as City names in members table, or as numbers and the ID and cityname in a different table?

12-04-2006, 02:11 PM
You could put the city names in another table corresponding to their city IDs used in your members table.

Then you could query your city table for the city names to go with your city IDs generated from the members table.

That's probably the cleanest, most accessible way to do it.