I am hoping this is an easy one I just don't know how to google the right answer.
I have a dropdown that is populated using a query.
<cfquery name="getFruits" datasource="#application.dsnName#" username="#application.dsnUser#" password="#application.dsnPass#">
SELECT TOP 3 fruit, color, size FROM fruit_table
</cfquery>
<cfselect name="fruits" query="getFruits" display="fruit" value="fruit" selected="#form.fruit#" queryPosition="below" required="yes" >
<option value="">Select Fruit</option>
</cfselect>
Is it possible when a user selects a 'fruit' from the dropdown to reference the 'size' and 'color' both associated with the 'fruit' they chose in another query?
For example:
<cfquery name="getFruits" datasource="#application.dsnName#" username="#application.dsnUser#" password="#application.dsnPass#">
SELECT vegetable FROM vegetable_table WHERE size = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#form.size#"> AND color = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#form.color#">
</cfquery>
Thanks!
When your form gets submitted, the value form.fruits
gets posted without the color
and size
attributes. So the way you would recode your <cfquery>
(which I renamed to "getVegetables") to get those attributes from the selected fruit would be like this.
<cfquery name="getVegetables" datasource="#application.dsnName#" username="#application.dsnUser#" password="#application.dsnPass#">
SELECT
vegetable
FROM
vegetable_table
WHERE (size, color) IN
(SELECT
size, color
FROM
fruit_table
WHERE
fruit = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#form.fruits#">)
</cfquery>
BTW, I'm not a fan of using <cfselect>
or any <cfform>
tags, but that wasn't your question. However, I would highly suggest discarding it and refactoring your code.
EDIT (second attempt):
As an alternate answer, if you want to pass the size,color
columns concatenated with a comma and aliased as sizeColor
from the form instead of the the fruit
column from your <cfquery>
, then what you can do instead is change the first select statement to
<cfquery name="getFruits" datasource="#application.dsnName#" username="#application.dsnUser#" password="#application.dsnPass#">
SELECT TOP 3
fruit,
size || ',' || color AS sizeColor
FROM fruit_table
</cfquery>
then change the value attribute to value="sizeColor"
in your <cfselect>
which will be the aforementioned comma delimited list of the size and color. So the code would instead be
<cfselect name="fruits" query="getFruits" display="fruit" value="sizeColor" selected="#form.fruit#" queryPosition="below" required="yes" >
<option value="">Select Fruit</option>
</cfselect>
Then you can change the <cfquery>
on the posted page to this.
<cfquery name="getVegetables" datasource="#application.dsnName#" username="#application.dsnUser#" password="#application.dsnPass#">
SELECT vegetable FROM vegetable_table
WHERE
size = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#form.fruits.listGetAt(1)#"> AND
color = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#form.fruits.listGetAt(2)#">
</cfquery>
Thank you, that is exactly what I envisioned but didn't know how to implement. I am attempting this now but running into an error. When I use the comma delimited value I get an error "Query column invalid or missing..." even though these are two valid columns from the cfquery and work fine if used individually. Any advice?
Ah, this comes from my lack of experience with
<cfselect>
which doesn't allow variable names in thevalue
field but a string literal of a specific column from your SQL select statement. What you can do instead is create a computed column of size, comma, color concatenated and aliased and use the aliased column e.g.size || ',' || color sizeColor
(presuming double pipe is your concatenation operator) and usesizeColor
as yourvalue
attribute. I'll modify my answer.You are a wise man user1203119, thanks again for your assistance.