Populating 2 drop-downs based on 1 query 1

[Meredith]

I have two drop-down menus (A and B) that I would like to populate from one query. I have already managed to populate the drop-downs based on two separate queries but I'd rather use one and use a loop (I think) and/or an if statement to remove records that don't belong in each drop-down.

To clarify:
I have two customer revenue territories with IDs of 26 (A) & 38 (B). I would like one drop-down (A) to only have the customers assigned to revenue ID 26 and the other drop-down (B) to only show customers in revenue territory 38.

My basic query looks like this for drop-down A:

    <cfquery name="dropCustListMid" datasource="example">   
    SELECT Organization, EndUserID
    FROM EndUser_table
    WHERE RevenueID=26

And the drop-down population code looks like this for A:

<cfform name="custListForm" action="action.cfm" method="post">
   <select name="custListMid">
   <cfoutput query="dropCustListMid">
   <option value="#EndUserID#">#Organization#(#EndUserID#)</option>
   </cfoutput>
   </select>        
   <input type="Submit" value="Go">

I then do the same thing for B but with another query that has a WHERE statement of RevenueID=38. Is there any way to have just one query and then loop through each drop-down and remove the IDs I don't want to see?

Sorry if this is unclear. Any help is greatly appreciated!

 

[r937]

just add RevenueID to the query, then use it in a CFIF for each dropdown...

<cfquery name="singleQ" datasource="foo">
SELECT Organization
, EndUserID
, RevenueID
FROM EndUser_table
WHERE RevenueID in (26,38)
</cfquery>

<select name="One">
<cfoutput query="singleQ">
<cfif RevenueID is 26>
<option value="#EndUserID#">#Organization#(#EndUserID#)</option>
</cfif>
</cfoutput>
</select>

<select name="Two">
<cfoutput query="singleQ">
<cfif RevenueID is 38>
<option value="#EndUserID#">#Organization#(#EndUserID#)</option>
</cfif>
</cfoutput>
</select>



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[Meredith]

I lot easier than I thought. Thank you!