3 Replies Latest reply on Aug 15, 2013 6:49 AM by p.sim

    cfloop - cfoutput bewilderment

    Leonard B Level 2

      Hello.

       

      Versions:

      CF8, MySQL

       

      I have one individual who fills the MSO postion for two different departments. I need to search a DB Table of submitted reports and return all submitted reports the MSO is associated with. Nine for dept-01 and four for dept-02. When I output the results as a text list, all displays as intended with no grouping or separations. However, when I cfloop the output, over a <cfselect> the results are grouped and two separate <cfselect> boxes are created. Dept-01 individuals in first one and dept-02 are displayed in second, each with their own submit button.

       

      What am I missing/doing wrong? How would I accomplish the output so there is only one <cfselect> containing all nine names to choose from?

       

      Below is what I have at the moment.

       

      This query below obtains agency_cert_number for each agency and MSO is associated with. In this case it will return two departments.

       

      <cfquery name="rsCheckMSO" datasource="DSN">

        SELECT

          individual_records.last_name,

          individual_records.first_name,

          individual_records.cert_number,

          individual_records.agency_cert_number,

          county_agencies.org_name

        FROM

          individual_records

        INNER JOIN

          county_agencies ON individual_records.agency_cert_number = county_agencies.agency_cert_number

        WHERE

          individual_records.cert_number = <cfqueryparam value="ES60043003" cfsqltype="cf_sql_varchar">

      </cfquery>

       

      <cfoutput>

      Dept - 01 agency_cert_number > ES00000639

      Dept - 02 agency_cert_number > ES00000643

      </cfoutput>

       

      I then cfloop through the reports to get all records associated with the two above agency_cert_numbers

       

      <cfloop query="rsCheckMSO">

      <cfquery name="rsAirwayReports" datasource="snocountyemssql01">

      SELECT DISTINCT ParamedicName, ParamedicCertNumber

      FROM airway_management_reports

      WHERE AgencyCertNumber = <cfqueryparam value="#rsCheckMSO.agency_cert_number#" cfsqltype="cf_sql_varchar">

      ORDER BY ParamedicName

      </cfquery>

       

      <cfquery name="rsAirwayReports" returns Nine records (Dept-01=5, Dept-02=4).

       

      --- Output 1 ---

      This displays a list of Paramedics who have submitted reports. The list is display in ASC order with no grouping/separation between names.

      <cfoutput query="rsAirwayReports">

      #ParamedicName#, #ParamedicCertNumber#<br />

      </cfoutput>

       

      --- Output 2 ---

      This displays two <cfselects> listing all the names grouped by associated department. Five for dept-01 and 4 for dept-02

      <cfform action="#cgi.script_name#" method="post" enctype="multipart/form-data" name="Test-01" id="Form-01" lang="en" dir="ltr">

      <label>Paramedic</label>

      <cfselect name="Paramedic" size="1" id="Paramedic" dir="ltr" lang="en">

      <option value="None" selected="selected">- Select Paramedic -</option>

      <cfoutput query="rsAirwayReports">

      <option value="#ParamedicCertNumber#">#ParamedicName#</option>

      </cfoutput>

      </cfselect>

      <cfinput type="submit" name="ButtonDisplay" id="ButtonDisplay" dir="ltr" lang="en" value="DISPLAY">

      </cfform>

      </cfloop>

       

      TIA for insights and suggestions.

       

      Leonard B

        • 1. Re: cfloop - cfoutput bewilderment
          p.sim Level 3

          You don't need a loop, but you need IN in your rsAirwayReports. Here's the code:

           

          <cfquery name="rsAirwayReports" datasource="snocountyemssql01">

                    SELECT DISTINCT ParamedicName, ParamedicCertNumber, AgencyCertNumber

                    FROM airway_management_reports

                    WHERE AgencyCertNumber IN (<cfqueryparam value="#ValueList(rsCheckMSO.agency_cert_number)#" list="true" cfsqltype="cf_sql_varchar">)

                    ORDER BY ParamedicName

          </cfquery>

           

           

          <cfoutput query="rsAirwayReports" group="AgencyCertNumber">

                    #AgencyCertNumber#<br />

                    <cfoutput>

                    #ParamedicName#, #ParamedicCertNumber#<br />

                    </cfoutput>

          </cfoutput>

           

           

          <cfform action="#cgi.script_name#" method="post" enctype="multipart/form-data" name="Test-01" id="Form-01" lang="en" dir="ltr">

                    <label>Paramedic</label>

                    <cfselect name="Paramedic" size="1" id="Paramedic" dir="ltr" lang="en">

                              <option value="None" selected="selected">- Select Paramedic -</option>

                              <cfoutput query="rsAirwayReports">

                                        <option value="#ParamedicCertNumber#">#ParamedicName#</option>

                              </cfoutput>

                    </cfselect>

                    <cfinput type="submit" name="ButtonDisplay" id="ButtonDisplay" dir="ltr" lang="en" value="DISPLAY">

          </cfform>

          1 person found this helpful
          • 2. Re: cfloop - cfoutput bewilderment
            p.sim Level 3

            Antoher thing that you could improve is to group the options.

             

            http://www.w3schools.com/tags/tag_optgroup.asp

            • 3. Re: cfloop - cfoutput bewilderment
              p.sim Level 3

              Use standard HTML form tag if you are not planning taking advantages of cfform. Also, enctype="multipart/form-data" usually is a form for submitting attachment.