SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    May 2001
    Location
    London
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    php and mysql select question

    Hi,

    I'm fairly new to PHP and mySQL and I'm trying to write some code to do the following:

    1. I have a database called stories which has a field called citycode which relates to a table called city that has the city long name.
    2. I want to select from stories and list out the cities, and number of stories in each city alphabetically.
    3. I've written some code below that 'tries' to do this but doesn't output the cities alphabetically. They come out ordered by the citycode.
    4. How can I do this?

    Here's the code:

    $result=mysql_query("SELECT citycode, count(citycode) as citycount FROM stories GROUP BY citycode");

    while(list($citycode, $citycount) = mysql_fetch_row($result)) {
    $result2 = mysql_query("SELECT cid, city FROM city WHERE $citycode=cid ORDER by city");
    list($cid, $city) = mysql_fetch_row($result2);
    echo "$city - $citycount";

    }
    Last edited by jetsetter; May 27, 2001 at 01:43.

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    I have a database called stories which has a field called citycode
    I doubt that. What you mean is that you have a table called stories. Databases have tables, and tables have fields.

    $result2 = mysql_query("SELECT cid, city FROM city WHERE $citycode=cid ORDER by city");
    Last edited by 7stud; May 26, 2001 at 18:54.

  3. #3
    SitePoint Member
    Join Date
    May 2001
    Location
    London
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course. I have a database with a table called stories.

    You suggest:

    $result2 = mysql_query("SELECT cid, city FROM city WHERE $citycode=cid ORDER by city");

    over my original code (cityindex is just a numeric id that is ordered by city)

    $result2 = mysql_query("SELECT cid, city FROM city WHERE $citycode=cid ORDER by cityindex");

    Since the first result (from $result in first post above) is not ordered by city (the long name) but by citycode (the auto-id) the output will come out in the order I entered the cities into the table not alphabetically by city.

    I want to do a join like this (city.city holds the long name):

    select stories.citycode, count(stories.citycode) as citycount, city.city from city, stories where city.cid=stories.citycode group by stories.citycode order by city.city

    How can that be done in mysql?

    <edit>I am just plain dumb. the above code works in mysql. I'm not sure why i thought it wouldn't!</edit>

    Thanks. Joe
    Last edited by jetsetter; Jun 20, 2001 at 06:14.


Bookmarks

Posting Permissions

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