2 Replies Latest reply on May 17, 2007 10:42 AM by creelove

    Listbox cfloop two different tables

    creelove
      What i'm trying to do is this:
      On my edit page I have a listbox that needs to be pre-filled with people already selected. But here is my problem - I have one table named "people" and another named "records". Now the "people" table have their permissions in it, and the records table has a field that list the people (ie. susie,lisa,jason) that were selected. So, my list box needs to hit the "peoples" table to pull everyone that has the correct permissions (in case they need to add someone) AND the records table so that I can retrieve who was previously entered. Right now my code looks like this:

      <cfquery name="qry_people">
      select *
      from people
      where permissions = 3
      </cfquery>

      <cfquery name="qry_support_people">
      select support_people
      from reocords
      where id = #id#
      </cfquery>

      <select name="support_people" multiple>
      <cfloop query="qry_people">
      <cfloop list="#qry_support_people#" index="i">
      <option value="<cfoutput>#people#</cfoutput>" <cfif #people# IS i>selected</cfif>><cfoutput>#people#</cfoutput></option>
      </cfloop>
      </cfloop>
      </select>

      Right now this ALMOST works - it grabs everyone from the "people" table AND it even highlights the ones that are pre-selected from the "records" table.... BUT each name appears TWICE.....Why? I've tried changing the one cfloop to cfoutput, but then I don't get the whole list of names. Any help would be great - THANKS!