CF Queries on TWO tables with a linked field.

how to?

[glenstobbs]

Been suggested I ask here ... Client has an Access Db - he is using ColdFusion ... we moved to two tables instead of one to limit duplication ..... now want to either set up a search where a clan name would be entered, follwed by a family name - the family name will belong to a clan, but will have clan attributes as well as family attributes. Up to 100 family names could belong to one clan, so you can see why two tables are needed.

I know no CF/PHP/PERL/CGI - only just about scrape through with ASP. I was going to use a pair of dropdowns to limit the searches to records held on file, but I know he wants the whole lot - ability to add, edit records, with cascaded updates and deletes.

Below is the thread from another forum with more details...

============================================

glenstobbs
11:42 am on July 3, 2002 (utc 0)
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
12:52 pm on July 3, 2002 (utc 0)
Hi,
Well I hate to say it but the best I could find uses JS. This one will do what you want to a point.

Brian

glenstobbs
1:28 pm on July 3, 2002 (utc 0)
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
1:39 pm on July 3, 2002 (utc 0)
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
1:48 pm on July 3, 2002 (utc 0)
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
1:52 pm on July 3, 2002 (utc 0)
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
1:57 pm on July 3, 2002 (utc 0)
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
2:17 pm on July 3, 2002 (utc 0)
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
New User
2:24 pm on July 3, 2002 (utc 0)
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
2:29 pm on July 3, 2002 (utc 0)
Possibly try one of these to forums to see if they can help further:
Perl, PHP, and Python Scripting probably the best place for the ColdFusion.

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

glenstobbs
2:43 pm on July 3, 2002 (utc 0)
Thanks
I will get him to do this me thinks....

Glen

[lorax]

What you need is the ability to query the db with a Join statement after the user selects the clan. Since there is a one to many relationship between the Clan and Family Names tables the Family names table could have a column that indicates which Clan the family belongs to. When you write your query to the DB you'd add a WHERE clause:

Where clan.ID = fam_name.ID

It's been a while since I've written CF code so you'll want to verify the syntax of what I've written.

You'd return the results to the 2nd drop down using a loop. In essence the form action would call the same page and keep the Clan info in a session variable and display the family names associated with that Clan. The user then chooses the family name and the form action calls another page with the results of the query. Make sense - need more?

[mavherick]

Let's say you use the 2 pages/form solution:

Page one:

<cfquery name="getClans" datasource="yourdatasourcename" dbtype="ODBC">
SELECT id, clan_name
FROM tableClan
</cfquery>

<html>
<head>
<title>Select Clan</title>
</head>
<body>

<form action="page2.cfm" method="post">
<select name="clan">
<cfloop query="getClans">
<cfoutput><option value="#getClans.id#">#getClans.clan_name#</option></cfoutput>
</cfloop>
</select>
<input type="submit" value="go" />
</form>

</body>
</html>

Page two:

<cfset clanId = form.clan>

<cfquery name="getFamilyNames" datasource="yourdatasource" dbtype="ODBC">
SELECT id, family_name
FROM tableFamilyNames
WHERE clan_id = #clanId#
</cfquery>

<form action="nextstep.cfm" method="post">
<select name="familyname">
<cfloop query="getFamilyNames">
<cfoutput><option value="#getFamilyNames.id#">#getFamilyName.family_name#</option></cfoutput>
</cfloop>
</select>
<input type="submit" value="go" />
</form>

</body>
</html>

mavherick