Loop through multiple queries

[ndeeley]

Hi,

I'm outputting the results of a query to generate some select boxes. The query runs through a list of skills to generate the boxes. Inside this I`m looping through another query to show a choice of skill levels:

<cftransaction>
<cfquery datasource="taskbook" name="getSkills">
select        LoginNameFK,
        SkillType,
        SkillLevel
from        tblSkillset
where        LoginNameFK = '#staff#'
</cfquery>
 
<cfquery datasource="taskbook" name="getSkillLevel">
select        SkillLevel
from        tblSkillLevel
</cfquery>
 
<cfquery datasource="taskbook" name="getSkillInfo">
select        Skill,
        SkillInfo,
        ID
from        tblSkillList
order by    Skill
</cfquery>
</cftransaction>
 
 
<cfoutput query="getSkillInfo">
 
 
<table style="width: 99%;">
<tr>
<td style="width: 10%;" rowspan="2"><img src="icons/#Skill#.jpg" style="vertical-align: middle;" /></td>
<td class="titleMinorS" style="width: 75%;">#Skill#</td>
<input type="hidden" name="SType_#ID#" value="#Skill#" />
<td style="width: 15%; padding-right: 6px;" rowspan="2">
 
    
<select name="SLevel_#ID#" class="droplist" size="1">
<option value="No Skill">Select One:</option>
<cfloop query="getSkillLevel">
<option value="#getSkillLevel.SkillLevel#">#getSkillLevel.SkillLevel#</option>
</cfloop>
</select>
 
 </td>
</tr>
 
<tr>
<td class="tabletextL" style="width: 75%; padding-bottom: 6px;">#SkillInfo#</td>
</tr>
 
</table>    
 
</cfoutput>    
 

This works fine with new users, but when they want to update their existing skills I'd like the existing skill level to be selecred in the select box. I've tried the following, but it only pulls back the result for the first skill and doesn't loop through the others.

<select name="SLevel_#ID#" class="droplist" size="1">
<option value="No Skill">Select One:</option>
<cfloop query="getSkillLevel">
<option value="#getSkillLevel.SkillLevel#" <cfif getSkills.Skilllevel EQ SkillLevel>selected</cfif>>#getSkillLevel.SkillLevel#</option>
</cfloop>
</select>
 

Any ideas how I can fix this? I'm assuming that CF doesn't know the value of the users skill level against the particular skill...

Thanks
Neil

[acoder]

You will need the skill level for each skill, so you can either

- make separate queries on each iteration to get the skill level for that particular skill; or

- get all the skills and skill levels and put them in an array/struct, so it's easy to access within the loop.

For the first option, you can use query of queries.

There may be other ways, but these 2 came off the top of my head based on your current code.