4 Replies Latest reply on Mar 27, 2009 12:52 PM by jbird5k

    Formatting date extracted from DB

    jbird5k Level 1
      I have a database table which holds responses from mulitiple online questionnaires, each record represent a question and its response. each instance of a questionnaire is represented by an item_id.

      the customer would like the data from this questionnaire to be displayed in a table format as part of a report.

      the columns names in the display table correspond to the questions not the columns in the response table.


      Below is the code i have tried thus far, the first block works the best but does not repeat the table structure.

      [code]
      <cfloop query="getQ75Items"> [COLOR="Red"]<!--- unique item ids associated with questionnaire 75 --->[/COLOR]
      <cfset row_count = row_count + 1>

      <cfquery name="getQ75Response" datasource="#application.insp#">
      select *
      from response_tbl
      Where inspection_id = #session.inspection# and questionaire_id = 75 and item_id = #getQ75Items.item_id#
      </cfquery> [COLOR="Red"]<!--- query sets up an instance of the questionnaire --->[/COLOR]
      <!--- <cfoutput >[COLOR="Red"]this code block lays out the data properly, however, the table structure does not repeat[/COLOR]

      <tr >
      <td><cfloop query="getQ75Response"><cfif question_id IS 1196 >#response#</cfif></cfloop></td>
      <td> <cfloop query="getQ75Response"> <cfif question_id IS 1197 >#response#</cfif></cfloop></td>
      <td> <cfloop query="getQ75Response"> <cfif question_id IS 1198>#response#</cfif></cfloop></td>
      <td><cfloop query="getQ75Response"><cfif question_id IS 1200>#response#</cfif> </cfloop></td>
      <td> <cfloop query="getQ75Response"><cfif question_id IS 1201>#response#</cfif></cfloop></td>
      <td> <cfloop query="getQ75Response"><cfif question_id IS 1202>#response#</cfif></cfloop></td>
      <td> <cfloop query="getQ75Response"><cfif question_id IS 1554>#response#</cfif></cfloop></td>
      <td> <cfloop query="getQ75Response"><cfif question_id IS 1555>#response#</cfif> </cfloop> </td>
      </tr>


      </cfoutput> --->


      <!--- <cfoutput >[COLOR="DarkOrange"]This code block "stair steps" the data between row 1 - col 1 and Row 8 - col 8 for a single record, and the table structure does not repeat [/COLOR]
      <cfloop query="getQ75Response">
      <tr >
      <td><cfif question_id IS 1196 >#response#</cfif></td>
      <td> <cfif question_id IS 1197 >#response#</cfif></td>
      <td> <cfif question_id IS 1198>#response#</cfif></td>
      <td><cfif question_id IS 1200>#response#</cfif> </td>
      <td><cfif question_id IS 1201>#response#</cfif></td>
      <td> <cfif question_id IS 1202>#response#</cfif></td>
      <td><cfif question_id IS 1554>#response#</cfif></td>
      <td><cfif question_id IS 1555>#response#</cfif> </td>
      </tr>
      </cfloop>

      </cfoutput> --->



      <cfoutput query="getQ75Response">
      [COLOR="RoyalBlue"]This code block "stair steps" the data between row 1 - col 1 and Row 8 - col 8 for a single record, and the table structure does not repeat[/COLOR]
      <tr>
      <td><cfif question_id IS 1196 >#response#</cfif></td>
      <td> <cfif question_id IS 1197 >#response#</cfif></td>
      <td> <cfif question_id IS 1198>#response#</cfif></td>
      <td><cfif question_id IS 1200>#response#</cfif> </td>
      <td><cfif question_id IS 1201>#response#</cfif></td>
      <td> <cfif question_id IS 1202>#response#</cfif></td>
      <td><cfif question_id IS 1554>#response#</cfif></td>
      <td><cfif question_id IS 1555>#response#</cfif> </td>
      </tr>


      </cfoutput>



      </table>
      </cfloop>


      [/code]
        • 1. Re: Formatting date extracted from DB
          Dan Bracuk Level 5
          That sure is a lot of code. If you can figure out a way to combine your 2 queries you can probably get rid of 90% of it.

          Also, this sentence, "the columns names in the display table correspond to the questions not the columns in the response table" suggests that your database design is poor. Are you able to change it?
          • 2. Re: Formatting date extracted from DB
            jbird5k Level 1
            Dan

            its a lot of code because it shows three options that I have tried.
            This option works best, but the table tags do not get repeated after the first row.

            <cfset row_count = row_count + 1>

            <cfquery name="getQ75Response" datasource="#application.insp#">
            select *
            from response_tbl
            Where inspection_id = #session.inspection# and questionaire_id = 75 and item_id = #getQ75Items.item_id#
            </cfquery>
            <cfoutput >

            <tr >
            <td><cfloop query="getQ75Response"><cfif question_id IS 1196 >#response#</cfif></cfloop></td>
            <td> <cfloop query="getQ75Response"> <cfif question_id IS 1197 >#response#</cfif></cfloop></td>
            <td> <cfloop query="getQ75Response"> <cfif question_id IS 1198>#response#</cfif></cfloop></td>
            <td><cfloop query="getQ75Response"><cfif question_id IS 1200>#response#</cfif> </cfloop></td>
            <td> <cfloop query="getQ75Response"><cfif question_id IS 1201>#response#</cfif></cfloop></td>
            <td> <cfloop query="getQ75Response"><cfif question_id IS 1202>#response#</cfif></cfloop></td>
            <td> <cfloop query="getQ75Response"><cfif question_id IS 1554>#response#</cfif></cfloop></td>
            <td> <cfloop query="getQ75Response"><cfif question_id IS 1555>#response#</cfif> </cfloop> </td>
            </tr>


            </cfoutput> --->
            </table>



            I am not able to redesign the db at this time, I inherited this project with just the reporting piece left to complete. and I don't believe extracting data to a narative report with display tables was discussed.

            The response table looks like this:

            response_id, inspection_id, questionnaire_id, question_id, response, case_id, locked, user_id,people_id, question_comment, item_id,item, and manager_response

            I have elimated the Items query,

            I still have the problem of the table tags not repeating

            thanks for your input Dan
            • 3. Re: Formatting date extracted from DB
              Dan Bracuk Level 5
              Your table design looks good. If you use cfoutput with a query attribute you should be able to get rid of all those loops and cfifs.

              Is the intent to put everything into a single table row? That's what you appear to be attempting.
              • 4. Re: Formatting date extracted from DB
                jbird5k Level 1
                What the customer wants: a table with the following colum headings:

                assigned to (answer is held in the response field)

                item (answer is held in the response field)


                Make (answer is held in the response field)


                model (answer is held in the response field)


                Serial Number (answer is held in the response field)


                Barcode (answer is held in the response field)


                Action taken (answer is held in the response field)



                How the data is stored.

                place the phrase "what is the..." in front the column heading mentioned above and you have the questions that make up Questionnaire 75.

                in the response table, i have the following columns

                response_id, inspection_id, questionaire _id, question_id, response, case_id, locked, user_id,people_id, question_comment, item_id, item, and manager_response


                so an instance questionnaire 75 contains 8 questions,
                answer the questionnaire 1 time generates 8 records in the response table all grouped together as Item id 1 item 1

                so for the first question is Assigned to

                response id =1
                inspection id = 9
                questionnaire id = 75
                question id = 1196
                response = me (or what the answer provide was)
                case id = null
                locked = 0
                user id = 12
                people id = null
                question_comment = null
                item id 1
                item 1
                manager response = null

                and this repeats each question and each instance of the questionnaire.

                so the only way I have been able to get the correct response in the correct column is to id the specific question id associated with that column. Ugly as sin, I know, if you have a suggestion for a more efficient way I am all for it.

                So how do I get the table tags to repeat with each record, actually that was my reasoning for looping through unique id first and passing it to the query for the responses.