Forum Moderators: coopster
I have a PHP script now that queries a mySQL database for store locations within a particular state - then organizes the results based upon the city of these locations. That script has been working very well, but in some larger states - the output list is just messy. So, I want to offer at the top of this page, the anchor links for all cities that have a store located within that state.
To make matters a bit more complicated, I know that there are a number of larger cities that have multiple listings, so I do need to be careful with that as well.
Right now, I'm having the most difficulty with learning how to pull all of the city names out, writing them to a list of anchor tags, then moving on to publishing the complete list - with the anchor tags in the appropriate places.
As a self-admitted newbie, I know I'm lost already. Can anyone offer some insight or perhaps some good reference sources that may walk me through such an issue?
Any help would be great. Thanks!
It's a little tricky to answer you but I've had some great discussions back here by posting simplified snippets of code and or of table structure...
Why not give everyone a little bit of code/structure to work with, we can all learn somthing with a bit of luck ;-)
Nick
First off, a major example for this site is the state of California, as it offers the most storefront locations selling this brand of products. So, I need to list out the distributors within CA.
Right now, I'm using the following SQL Select Statement:
-----
SELECT * FROM table WHERE stateabr = 'CA' ORDER BY city
-----
The results of this query are 56 actual stores selling the product in 31 cities.
Now, using the following code, I get each store's details within a small table. So currently on this page, I am writing out 56 of the following tables:
-----
<table>
<tr>
<td>$title</td>
</tr>
<tr>
<td>
Location: $city, $stateabr
<br><hr>
$description
</td>
</tr>
</table>
-----
-- Having 56 of these tables isn't a big deal to me. It's only showing the store name, location, then the copy that the distributor has given me. Doesn't take up much space at all.
If I can figure out what I plan to do, the script should start off by listing out the following cities... such as:
Anaheim (2)
Los Angeles (12)
San Francisco (7)
...etc.
The number within the parenthesis would correspond to the number of stores within that city. So, once clicked on "Los Angeles" you should be taken to the anchor called "#LosAngeles" on this page, and there, you should get the 12 locations listed out.
Does any of this make sense? I'm having a hard time knowing what I should, and shouldn't publish here on the forums.
Thanks Nick!
The first way is with two queries. The first query would give you the Cities and the counts:
select city, count(city)'count'
FROM table WHERE stateabr = 'CA'
group by city
ORDER BY city
I didn't test that, I never seem to be able to get the syntax exactly right the first try, but that's the general idea.
That'll give you results that look like this:
city count
--------------------
Anaheim 2
Los Angeles 10
San Francisco 7
etc...
That'll take care of your top list and you can use the city names as the anchors.
The second query is as you've already done.
The other way to do this might be to something with arrays to query the database only once and store all the data in a complex data structure. (see Arrays [php.net]
Now that I've thought about that bit, the former is probably the easiest.
[edited by: bonanza at 4:39 pm (utc) on May 26, 2003]
Thanks everyone!
The pages look, and work, flawlessly.
-- Okay, I jumped the gun and THOUGHT it was flawless. I've got a full list of the cities up top, but when I select the city, it takes me to the proper anchor tags, but only ONE listing is appearing per city. Not all of them consecutively. So when I select Los Angeles, I get 1 listing as opposed to the 12 I should get.
The way I have it set up now, I'm only using one SQL select statement, and that reads as:
"SELECT * FROM table WHERE stateabr = 'CA' GROUP BY city
-- EDIT --
"SELECT * FROM Sheet1 WHERE stateabr = 'CA' ORDER BY city
..as a SELECT statement has all of them listed out, so now it works okay within the bottom portion. Now, I'm generating multiple links to the same anchors in the top though.
-- EDIT --
-- Okay, I'm through talking with myself... I got it all figured out. I just needed two SELECT statements in there. Thanks to everyone again!
That, I think will give you the anount of stores in each city and the everything else...
If not, give us the db schema and we'll see, I'm no MySQL expert, i've just had a few blinding threads on it recently and they involved 'GROUP BY'. I still don't fully get it but it's POWERFUL ;-)
Nick
As you've now seen, you need two queries. One to get the counts (with the group by), the other to get all of the stores in each city (no group by).
Looks like you've got it licked now though.
