subselect

OK guys, this is quite a problem I have here, so let me explain the situation.
I have two tables in one database. One of the tables is for countries and the other is for towns. Both tables have one column in common which is the countryid and the code. The countryid is the id of the country eg, England = 657 (id) and the code is what tells us that a town belongs to a which country, so if the town is london the code will be 657.

OK so I have two databases countries_en and towns_en
in countries_en I have two columns, country and countryid
and in towns_en I have three: town, town id and code

Now here is the problem: I want to create two drop down tables so that when I choose a country in the first table the list in the second table changes depending on which country I have chosen.
So if I choose England on the fist table, the second table will only have the towns of that country eg london, pouthampton, portsmouth etc...

I hope someone has the brains to figure this one out cause I have tried just about everything. I'm not putting any code not to confuse anyone. You have thh problem as I have it.

WHAT IS THE PHP CODE I MUST USE

version 4

If anyone dares, thanks in advance

Xander

It's not a subselect you need. To build the country box, just select countries from the database and build the box with the country id as the value and the name as the label. Default value is "Select a country." Build the initial town box with only a "Select a town" option.

Onchange for the country box, submit the form, passing the country id. Use this id to select the appropriate towns and build the town box appropriately. When rebuilding the country box this time around, make sure the code for the country passed as a parameter is selected.

In pseudocode:

From a users perspective, you want the city droplist to auto-populate when you choose a country, correct?

Here's how i would approach it... use dhouston's method to populate the country box... add an onchange event to the select box which submits the form to itself... then in php, test if the form variable for country exists... then populate the city select with the query results based on the data passed... [pseudo-example: SELECT * FROM city, country WHERE country = city AND country = POST_DATA]

i hope i'm being clear... i dont feel like writing the code out for you, although i hope i'm clear enough to give you that push you need to get the ol' wheels turning...

I think we're saying pretty much the same thing, right MadCowDzz? On rereading my post today, I can't help thinking I wasn't very clear. Hopefully your post clarified mine a bit.

I got the countries displaying and working fine but I don’t know how to make a different list of towns come up depending on which country I select. The code is the following:




thanks

Xander

Still no luck, maybe its because I didn't really understand what you were trying to say. You must know that I am very new to php and mysql. I was hopeing you could write me an exact piece of code that I could put in to try it out. I hope I'm not asking for too much.

thanks again

It's just a matter of piecing together two snippets of code already provided above, roughly as follows. You'll need to check all the column names, etc.

I tried everything, still nothing. The code makes no difference, there isn't even a list displayed, just an empty table. I don't know what more I can do but thanks for your help anyway

Try running your queries manually (at the command line or through a tool like MysqlAdmin) to make sure they're ok. It could be that your code's fine but your queries are wrong.

I tried that too, with phpmyadmin, the queries are fine, this is the most annoying problem I have come across and all I want to do is what madcowdzz says in his first line and be able to save the settings for the user. Guess this stuff is more complicated than I imagined. I'm getting on with other stuff but it would be good if I could solve this problem once and for all. Thanks again dhouston.

Xander