Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post

    How many members live in which city

    PHP Code:
    <?php

    // 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(
    =>"Berlin",
    =>"Istanbul",
    =>"Newyork",
    =>"Stockholm");

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


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

    dbconnect();
    $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?

  • #2
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    It should be a matter of the SQL query.
    Code:
    SELECT ID, name, city, COUNT(ID) AS members_in_city FROM members GROUP BY city ORDER BY members_in_city
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #3
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    That's it.
    Here is the code.

    PHP 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?

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

  • #5
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    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.

  • #6
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by guvenck View Post
    The statement was provided by marek.
    I just copied the fields over from your initial SQL query. 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.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #7
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    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.

  • #8
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Why not have them in the DB?
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #9
    Regular Coder
    Join Date
    Jan 2006
    Posts
    377
    Thanks
    8
    Thanked 1 Time in 1 Post
    You mean as City names in members table, or as numbers and the ID and cityname in a different table?

  • #10
    Regular Coder Tyree's Avatar
    Join Date
    Sep 2003
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •