Building a select list inside a loop
How do you NOT use a query of queries inside a loop to solve the problem of having to build a select list for each row?
In this example, every customer has a list of actions unique to that customer:
<cfquery name="qryAction" datasource="myDataSource">
SELECT ActionID,CustID,ActionName FROM AvailableActions
</cfquery>
<cfquery name="qryOrderHeader" datasource="myDataSource">
SELECT CustID FROM OrderHeader
</cfquery>
<html>
<body>
<cfform preservedata="yes">
<cfloop query="qryOrderHeader">
<cfquery name="qry3" dbtype="query">
SELECT ActionID,ActionName FROM qryAction
WHERE CustID = #qryOrderHeader.CustID#
</cfquery>
<cfselect name="ActionID" query="qry3" display="ActionName" value="ActionID" />
</cfloop>
</cfform>
</body>
</html>
Here's the SQL if it helps illustrate my example.
use tempdb
GO
create table Cust(
CustID Int Identity Primary Key,
CustName Varchar(255)
)
GO
INSERT INTO Cust(CustName) values('One')
INSERT INTO Cust(CustName) values('Two')
GO
create table AvailableActions(
ActionID Int Identity Primary Key,
CustID Int,
ActionName Varchar(255)
)
GO
INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Insert')
INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Edit')
INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Delete')
INSERT INTO AvailableActions(CustID,ActionName) VALUES(2,'Insert')
INSERT INTO AvailableActions(CustID,ActionName) VALUES(2,'Edit')
GO
CREATE TABLE OrderHeader(
OrderHeaderID Int Identity Primary Key,
开发者_JS百科 CustID Int
)
INSERT INTO OrderHeader(CustID) VALUES(1)
INSERT INTO OrderHeader(CustID) VALUES(2)
INSERT INTO OrderHeader(CustID) VALUES(2)
I suppose the best result would be to include ActionID and ActionName in qryOrderHeader.
It's pretty straightforward to build the select lists manually (instead of using cfselect) by using cfoutput with the group attribute:
<cfquery name="qryAction" datasource="myDataSource">
SELECT ActionID,CustID,ActionName
FROM AvailableActions
ORDER BY CustID
</cfquery>
<cfoutput query="qryAction" group="CustID">
<select name="actionid">
<cfoutput>
<option value="#val(ActionID)#">#htmlEditFormat(ActionName)#</option>
</cfoutput>
</select>
</cfoutput>
Note you need to specify the CustID as the first (or only) order by in order to make the nested cfoutputs work correctly. Not tested, so there may be typos.
加载中,请稍侯......
精彩评论