Formatting date extracted from DB

Report · Mar 27, 2009

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.


<cfloop query="getQ75Items"> <!---
unique item ids associated with questionnaire 75 --->

 <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> <!--- query sets up an
instance of the questionnaire --->

 <!--- <cfoutput >this code block lays
out the data properly, however, the table structure does not
repeat

 

 <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 >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 

 <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">

 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

 <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>

Report · Mar 27, 2009

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?

Report · Mar 27, 2009

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

Report · Mar 27, 2009

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.

Report · Mar 27, 2009

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.