SQL or Array for City Names/Zip Codes?
Posted: Wed Apr 16, 2008 7:04 pm
I have a mySQL database table "orders" that includes each customer's zip code. I have another table, "zipcodes", that contains all the US zip codes and their corresponding cities/towns.
I want to generate a report on order data, along the lines of:
City # of Orders
Chicago 5
San Francisco 4
Cleveland 3
Smalltown 1
etc.
In the "orders" table, I might have separate orders from zip codes 60601 and 60614, but both correspond to "Chicago" in the zip codes table, and I would want my report to lump them together as such (I'm concerned that customers may not have typed in city names in common fashion, or I'd do it that way). Can I generate this sort of report with a single SQL statement or would I have to utilize arrays? If arrays, would I need to do some sort of check where (as I'm looping through), if the city is not already in the orders array, add a new entry, and, if it is, then increment a separate count value? What type of array would that be?
Any help would be appreciated. Thanks.
I want to generate a report on order data, along the lines of:
City # of Orders
Chicago 5
San Francisco 4
Cleveland 3
Smalltown 1
etc.
In the "orders" table, I might have separate orders from zip codes 60601 and 60614, but both correspond to "Chicago" in the zip codes table, and I would want my report to lump them together as such (I'm concerned that customers may not have typed in city names in common fashion, or I'd do it that way). Can I generate this sort of report with a single SQL statement or would I have to utilize arrays? If arrays, would I need to do some sort of check where (as I'm looping through), if the city is not already in the orders array, add a new entry, and, if it is, then increment a separate count value? What type of array would that be?
Any help would be appreciated. Thanks.