Query and Table Fields

[KeyserSoze1877]

Let see if I can explain this.
I have a table with the fields:
TestName, VoiceFile, Time01, Time 02......Time34

Each record is a test transaction.
The time fields records, well times in seconds.
Not all records have all 34 time fields filled. The last time is a 999.

So some records could end at Time12 and other Time27, etc...
Using a cfloop index=TimeHold from 1 to 34 I cant figure out how to display the times, I cant just use
<cfoutput>#Time##TimeHold#</cfoutput> that just doesnt work.

I am trying to avoid harcoding an array structure, that would have to build an array for each record. Is there a way to loop through the actual field names.

Does this make sense?

 

[webmigit]

I don't quite get what you mean but you might try this...

<CFOUTPUT><CFLOOP from=&quot;1&quot; to=&quot;999&quot; index=&quot;currItem&quot;>
#Evaluate(&quot;Time#CurrItem#&quot#
</CFLOOP></CFOUTPUT>

 

[webron]

Or you could use:
<cfloop query=&quot;qName&quot;>
<cfloop list=&quot;#columnList#&quot; index=&quot;currentfield&quot;>
<CFOUTPUT>#evaluate(currentfield)#<CFOUTPUT>
</cfloop>
</cfloop>

The first loop loops over the rows and the second over every field.

webron

 

[Guest_imported]

A better way would be to fix your database design so that you don't have repeating values in a table. This is called &quot;normalization&quot; and you should strive for 3rd normal form. that means that you have a parent table (with fields&quot; Test_ID&quot;, &quot;TestName&quot; and &quot;VoiceFile&quot and a child table (with fields &quot;Test_ID&quot;,&quot; sequence_no&quot;, and &quot;time&quot. All the times for a particular test would be records in the child table. Each record would have the test_id of the test (so it can be associated with the test in the parent table), a sequence number (so that the test times can be ordered), and the time itself. Those tests that have 12 times would have 12 records in the child table, those records that have 27 times would have 27 records in the child table, etc. This avoids wased space that you have now. More importantly, this design allows you to do what you want and provides the flexibility to easily extract data from the database according to whatever criteria you can dream up. You current database design requires that you write very complex queries and code, such as your need to dynamically create fieldnames. Plus, with your current design, you have no easy way to determine how many of your 34 time fields were used in a test. With this design, you would do this to display the times:

<cfquery name=&quot;all_times&quot; datasource=&quot;...&quot;>
select p.test_id, p.TestName, c.time
from parent p, child c
where p.test_id = c.test_id
order by p.test_id, c.sequence_no
</cfquery>

<table>
<cfoutput query=&quot;all_times&quot; group=&quot;test_id&quot;>
<tr><td>#TestName#</td><td>#time#</td></tr>
<cfoutput>
<tr><td>&nbsp;</td><td>#time#</td></tr>
</cfoutput>
</cfoutput>
</table>

 

[KeyserSoze1877]

Thank you. I understand normalization. I understand what it does, I understand how 99% of the problems I have will be fixed by normalizing this 'architecture' we have now. Unfortunately, the individuals who did come up with this one-table system do not want to change the DBs, then they have to change the code and they would rather deal with the problems then change everything around.

Domino effect coming.

 

[Guest_imported]

Yes, I understand. I believe that they're making a mistake--perpetuating a bad design. It will (already has!) lead to cumbersome code which is hard to maintain as well as complex and inefficient code due to the repeating values in the table.

Oh well, I hope that webron's suggestion to use evaluate() helps.