Dynamic Dropdown lists using TWO tables

Stuck trying to find the answer

[glenstobbs]

I hope I can explain this headache ....

I have 2 tables, and I want to show the CLAN field from table one in a dropdown. Then on selection of a name in that there dropdown, I want to populate dropdown number two with data from table 2 .... [matched using a text field]

The fields I will use are ......

Table One
=========
id [AutoNumber]
clan_name [Text] - SHOWN in dropdown one

Table Two
=========
id [Autonumber]
clan_id [text]
family_name [Text] - SHOWN in dropdown 2 if clan_id in this table matches the entry selected in first dropdown box [Clan_Name]

I am confusing myself!

============================================
What it is is we have 1000 CLANS in table one, 50,000 FAMILY NAMES in table two - we use two tables, as a single table would have to contain 50,000,000 entries - so please don't tell me to use a single table!
============================================

I guess JScript would enhance it, but if I have to update the page it is not a problem . . . but would be a pain for my client [and for me in trying to explain it to him!]

Looking forward to some responses.

Glen
UK

[Reflect]

Hi,

Well I hate to say it but the best I could find uses JS. This one [javascript.internet.com] will do what you want to a point.

Brian

[glenstobbs]

nah - no go with that one sadly. I think I will just go round it another way using multiple pages which might not work in the end product anyway.

Thanks for your suggestion though

[Brett_Tabke]

I think you were on the right track skipping as much js as possible. If you have a fairly fast server, and can present a low overhead page, I'd go for two form page. The first table they select and then get the second table. It's so much less confusing for the user as it is for you.

What's on the server side that you know, and can use (perl, php, etc)?

[glenstobbs]

I am not sure what platform the client is going to use this on, but I do know he has tried to use ColdFusion to run a query to do what I am hoping to achieve here. He failed.

[Knowles]

But why did he fail? I see where Brett is going with this and it may be much easier to do with server side scripting.

[glenstobbs]

I guess because he has little or no knowledge of the subject!

It all works fine if we use one table - it's when we split it to two tables that the problem hits.

[Knowles]

What are you trying to write this in? ColdFusion? HTML+JS?
If its ColdFusion I assume he can run it since you can get one table to work. Also if it is someone hopefully will see it and respond who knows ColdFusion well.

[glenstobbs]

I am trying to sort a fix in ASP - though ideally he would like a coldfusion solution. I know he has posted to a number of forums and groups with no success to speak of!

[Knowles]

Possibly try one of these to forums to see if they can help further:

Perl, PHP, and Python Scripting [webmasterworld.com] probably the best place for the ColdFusion.

Microsoft .NET and Related Technologies [webmasterworld.com] I am guessing all the ASP gurus hang out there now.

[glenstobbs]

Thanks

I will get him to do this me thinks....

Glen