cfloop help

[AlwaysWilling]

Hi, I have a looping problem.

I would like tp display all the programs in a select box and only select the ones that are coming from the database (the user's saved programs).

This is my code:

<cfquery name="query1" datasource="#DB#">
	SELECT	 F.ID, F.First_Name, F.Last_Name, F.a, F.b, F.c, F.d
	FROM	 Table1 F
	WHERE	 F.Id = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.IID#">
</cfquery>

<form name="" action="" method="post">
<cfoutput query="query1">
<table width="605" border="0" cellpadding="0" cellspacing="0">
	<tr>
		<td valign="top">
			<table width="605" border="0" cellpadding="0" cellspacing="4">
				<tr valign="top">
					<td width="205">
						<p>First Name*<br><input name="First_Name" type="text" id="First_Name" value="#First_Name#"></p>
						<p>Last Name*<br><input name="Last_Name" type="text" id="Last_Name" value="#Last_Name#"></p>
						...
						MORE FIELDS
						...
					</td>
				</tr>
			</table>
			<table width="605" border="0" cellpadding="0" cellspacing="4">
				<tr valign="top">
					<td>
						[COLOR=red]
						<cfquery name="query2" datasource="#DB#">
							SELECT	FATP.table2ID, FATP.ProgramID as FATPPID, t3.Name as t3Name
							FROM	table2 FATP INNER JOIN table3 t3 ON FATP.ProgramID = t3.ProgramID
							WHERE	FATP.table2ID = #URL.myID#
						</cfquery>
						
						<cfquery name="query3" datasource="#DB#">
							SELECT ProgramID, Name FROM table3
						</cfquery>
						
						<select name="ProgramID" multiple>
							<option value="">Please Select</option>			<cfloop query="query3">
									<option value="#ProgramID#" <cfif query2.FATPPID EQ query3.ProgramID>selected</cfif>>#Name#</option>
								</cfloop>
						</select>
						[/color]
					</td>
				</tr>										
			</table>
		</td>
	</tr>
</table>
</cfoutput>
</form>

Any ideas? Thanks.

 

[GUJUm0deL]

Create a list of all programid's fron query2 and then do a listfind on that list against the query3's programid.

EX:

<cfquery name="query2" datasource="#DB#">
  SELECT    FATP.table2ID, FATP.ProgramID as FATPPID, t3.Name as t3Name
  FROM    table2 FATP INNER JOIN table3 t3 ON FATP.ProgramID = t3.ProgramID
  WHERE    FATP.table2ID = #URL.myID#
</cfquery>
[COLOR=green]<cfset myVar = valuelist(query2.fatppid)>[/color]

<cfquery name="query3" datasource="#DB#">
  SELECT ProgramID, Name FROM table3
</cfquery>

<select name="ProgramID" multiple>
  <option value="">Please Select</option>            
  <cfloop query="query3">
    <option value="#ProgramID#" [COLOR=green]<cfif listfind(myVar, ProgramID)>selected</cfif>[/color]>#Name#</option>
  </cfloop>
</select>

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[AlwaysWilling]

Great! Works like a charm. Thanks dude.