[truesilentassassin]

Hello,
Here is the problem I have:

I have a table for state, city, category, citydetails

I am able to query the state table on my php script to display the cities from that state.

Now, I am also able to display the category for the city selected. But it's not accurate.

Here is why:

My category table has just 2 columns: id, and category. It does not have any details about the city or state (as fk's)

The reason I did not add that is beacuse all cities from all state have the same category.

So I am just selecting * from category whenever a city is clicked. The problem is if I want details on a particular city, lets say Los Angeles, the query is not displaying values from Los Angeles, it is displaying values from all the cities with the category, lets say 'Cars'.

Is there a way to overcome this?

I hope my question is clear!

Just to iterate, I have a page which displays all the major cities based on the state selected. Now when a city is clicked, categories for that city should be displayed for example: Cars, Schools, Parks, and so on. Since these categories are the same for all the cities in all the states, I don't know to specify what category is for what state in my mysql table. Apparently, when I do the query, it does not have any details about the city and state so my query is displaying all values for that category.

Can anyone please help me? Should I alter my table to have city and state as fk's or can i query in such a way I can display only details of a category of the city selected.

Just so to let you know, when i click a state I am able to get all the cities on that state and when I click a city, $_GET does have the value of the city I selected, but I am just querying for the same results for all cities.

Please help.

Thanks in advance.

Regards,

[Stef Levolger]

Is there any table which tells which city has which categories?

If so, let's say citydetails does so, you can do

SELECT category.category
FROM category, citydetails, city
WHERE category.ID=citydetails.category AND city.name=citydetails.city

This query should basically do what you want, although I dun know exactly if the collumn names are ok, you should check this out yourself.

It only works if your city details kinda looks like
New York 2
New York 4
New York 9
California 11
California 23
Los Angelos 3
Los Angelos 4

Order ofcourse doesn't matter, but multiple entries for each city, not like:
New York 2, 4, 9
California 11, 23
Los Angelos 3, 4

If you would have it like the second way you could solve it too, although it's just a bit more complicated and to late for me now to explain.

[truesilentassassin]

Stef,
Thanks for your response. I do have the category column on my citydetails table. I am successfully able to query in my database and get the results I want.

But how do I do it in my php script, where the user selects the city and the category?

Will your query work in php without using $_GET?

I am using $_GET to list the cities based on the state clicked.

I can use $_GET to list the categories based on the city clicked, but since category table does not have a city column, I cannot use city.

Thats the reason I am stuck. I wanna know if I have to have a column in my category table for city so that I can use $_GET to see what category was clicked and from what city was it clicked.

Your help is greatly appreciated.

Regards,

[Stef Levolger]

Let it place the city and category in the link

So you will get something like:
index.php?state=wherevernewyorkis&city=newyork&cat egory=3

The variables would just be $city and $category.

The basic page would be displayed:
if(!$state) {
state choose page
} else {
if(!$city) {
city choose page
} else {
if(!$category) {
category choose page
} else {
listing
}
}
}

And the variables will just be $state, $city, $category.

So when you want to get a listing of cities just do:
SELECT * FROM city WHERE state='$state'

Same goes for the other queries.