Q of Q

[quiero mas]

<cfquery name="get_area" datasource="dependent">
SELECT group_name
FROM tAbyL</cfquery>

<cfquery name="get_language" dbtype="query">
select
hospital_name
from
get_area
WHERE (([tAbyL]![subgroup_id]=[tHbyL]![subgroup_id] And [tHbyL]![group_id]));
</cfquery>

Im trying to query a database with a Q of Q - first time for me.
On the search page i have used a cf two select related tag. The categories for
the drop are not filled from the database. (probably should be but thought
would do it the easy way first)
I manually input options.

Now on the result page i have attempt the code for a Q of Q.

Objective - a user chooses a option from the first drop down which populates
the second then the criteria are sent to the result page. Then this is where it
gets fuzzy for me - the results are displayed

<cfquery name="get_area" datasource="dependent">
SELECT group_name
FROM tAbyL</cfquery>

<cfquery name="get_language" dbtype="query">
select
hospital_name
from
get_area
where (([tAbyL]![subgroup_id]=[tHbyL]![subgroup_id] And [tHbyL]![group_id]));
</cfquery>

The database
2 tables
table 1 name: tAbyL
fields:hospital_id , Hospital_name , subgroup_id , subgroup_name , group_id ,
group_name

table 2 name: tAbyL
fields: subgroup_id , subgroup_name , group_id , group_name

where group = Area Names
where subgroup = language type

[Dan Bracuk]

You are trying to select a field called hospital_name from a query that only has one field, called group_name.

[quiero mas]

Couple of questions
1. Dumb question im sure but this is for the result page right. I m still
living in the fog a bit.

2. How is this looking? Im getting an erorr (my os is in Japanese) that says
the "HOSPITAL_NAME of GET_AREA is unfixed , undefined" - pretty vague i know
sorry.

<cfoutput>#get_area.hospital_name#</cfoutput>
<cfquery name="get_area" datasource="dependent">
SELECT tHbyL.hospital_name, tHbyL.group_name
FROM tHbyL;
</cfquery>

<cfquery name="get_language" dbtype="query">
SELECT hospital_name
FROM get_area
WHERE (([tHbyL]![subgroup_id]=[tHbyL]![group_id]));
</cfquery>

3. Does anyone know a good reference page for cf terms - im new to the game
so some of the terminology is new to me.

e.g onload event handler

regards Mark

[Dan Bracuk]

Originally posted by: quiero mas
Couple of questions
1. Dumb question im sure but this is for the result page right. I m still
living in the fog a bit.

2. How is this looking? Im getting an erorr (my os is in Japanese) that says
the "HOSPITAL_NAME of GET_AREA is unfixed , undefined" - pretty vague i know
sorry.

<cfoutput>#get_area.hospital_name#</cfoutput>
<cfquery name="get_area" datasource="dependent">
SELECT tHbyL.hospital_name, tHbyL.group_name
FROM tHbyL;
</cfquery>

<cfquery name="get_language" dbtype="query">
SELECT hospital_name
FROM get_area
WHERE (([tHbyL]![subgroup_id]=[tHbyL]![group_id]));
</cfquery>

3. Does anyone know a good reference page for cf terms - im new to the game
so some of the terminology is new to me.

e.g onload event handler

regards Mark

Take the table name out of the select clause of your original query.

If you have a query where you are joining tables and actually need to include
the table name, use aliases in your select clause. This also applies when you
are selecting counts, sums, and other aggregates.

[quiero mas]

??????????????????? the same error - feel like getting close though

<cfoutput>#get_area.hospital_name#</cfoutput>
<cfquery name="get_area" datasource="dependent">
SELECThospital_name, group_name
FROM tHbyL;
</cfquery>

<cfquery name="get_language" dbtype="query">
SELECT hospital_name
FROM get_area
WHERE (([tHbyL]![subgroup_id]=[tHbyL]![group_id]));
</cfquery>

[zoeski80]

Mark

You are trying to output get_area.hospital_name before you even do the
get_area query
The cfoutput should be AFTER the query is run.
this should fix the error you're getting - "HOSPITAL_NAME of GET_AREA is
unfixed, undefined"

in the get_language query:
You cannot do a QoQ on a real table from the database ie. cannot use tAbyL or
tHbyL in a cfquery where dbtype=query.

From your description it sounds like you want to use what the user selected on
the form to be in your query ??
To narrow down your query results based on what the user selected on the form
you need to use form.FormFieldName eg. #form.Hospital_name# within your query
to get the correct records.

I don't really understand what you're trying to do so don't know how to help.
what are in the select fields on the form and what are they called?
what are you trying to do on the results screen?

HTH

[quiero mas]

???????Thanks for your reply
firstly i am using a two select related tag

search page
first drop down - area (east west etc)
second drop down - language (english sapnish etc) #populated

objective - a user first selects an area and then the language
based on this i want to query the database and out the names of hospitals in
that area with the whatever language was chosen.
I am very new to this - the above codes are my attempts to get the result page
working properly. AS you can tell i dont have a clue!

Please help Zoe -
regards MArk

[quiero mas]

Ohh thats right - i have dynamically populated the drop downs simple manually writen the selects in the options.

[Dan Bracuk]

Originally posted by: quiero mas
???????Thanks for your reply
firstly i am using a two select related tag

search page
first drop down - area (east west etc)
second drop down - language (english sapnish etc) #populated

objective - a user first selects an area and then the language
based on this i want to query the database and out the names of hospitals in
that area with the whatever language was chosen.
I am very new to this - the above codes are my attempts to get the result page
working properly. AS you can tell i dont have a clue!

Please help Zoe -
regards MArk



Go read the other thread again. What you are doing is not necessary.

[quiero mas]

???dan - is there another way to get this done? Im not really sure what you
mean. IM using the wrong tag? or is it that two selects aren't user friendly? -
Do you think it is more appropriate to just keep it simple?

Sorry for the banal questions but not really sure
Regards MArk

[Dan Bracuk]

Did you read the other thread again? It contains the answer.

But you have to read the entire thing.

[quiero mas]

Sorry got confused about which thread - now i got it

HI Dan I undrstand the first part post in tha t thread but i dont really
understan this terminology - do you think you could spell it out for me. Sorry
You wrote " You already have the js function written.( - this is from the
search page isnt it?)

Just call it as part of your body onLoad event handler." i dont understand
what this means

Thanks for your patience

[Dan Bracuk]

I think I led you down the garden path. I have done this once and was
answering from memory. So today I looked at what I did. The logic was
something like this.

Always run
q1 - query that gets all possible values for select1
q2 - query that gets all possible values for select2
q3 - query for an existing record.

Note - I added records in the tables that drive q1 and q2 that indicate that
resemble

id description
1 Not Yet Updated

if you have an existing record
q4 - query that gets all values for select2 that pertain to selected value for
select1 in select1. This is a Q of Q from q1

Then I do some other stuff and eventually get down to the form. The code is
something like this:

if you have an existing record (from q3)
cfselect1 with the existing value selected.
cfselect2 with query="q4" and the existing value selected
else
cfselect1 with record 1 selected
cfselect2 with record 1 as the only option

[quiero mas]

Thanks for taking the time to look into it Dan -
This q of q is really testing me - so i appreciate the help.
I will take some to try work out your post and go from there.

Thanks for the help
Mark