php and mysql select question

[jetsetter]

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

}

[7stud]

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

[jetsetter]

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