SfC Home > Web Design > SQL > ColdFusion >
Populating a Matrix Table with ColdFusion
by Ron Kurtus (26 December 2002)
Taking information from a database and linearly populating the rows of a table is relatively simple in Macromedia ColdFusion. It is more difficult to populate a table that is a matrix, with both columns and rows. The method is similar to populating a simple table, except that you, must define the number of rows and then loop through the matrix.
Questions you may have include:
- How is a simple table populated?
- How do you populate a matrix?
- What does the output look like?
This lesson will answer those questions.
Simple table
The method to simply populate the rows of a table in a given sequence is:
<CFQUERY NAME="list" DATASOURCE="xyz" DBTYPE="ODBC">
select * from table_name
order by seq_no
</CFQUERY>
<TABLE>
<CFOUTPUT QUERY="list">
<TR> <TD>#seq_no#: #name#</TD></TR>
</CFOUTPUT>
</TABLE>
This is pretty straightforward.
Populate matrix
To populate a matrix table of 3 columns by a number of rows determined by the amount of data you have, you first make the same query as in the simple case. The data in this table is ordered by a sequence number. You could also order the data by ID value.
<CFQUERY NAME="list" DATASOURCE="xyz" DBTYPE="ODBC">
select * from table_name
order by seq_no
</CFQUERY>
Define number of rows
Find the number of records in your table. Then set the maximum rows as the integer of the number of records + 2, divided by 3. If the number of columns was 5, the maximum rows would be the integer of the number of records + 4, divided by 5.
<CFSET max_seq = #set_count.recordcount#>
<CFSET max_rows = INT((#max_seq# +2)/3)>
Loop through matrix
First set a variable x = 0. Loop through the number of rows and then the columns.
<CFSET x = 0>
<TABLE>
<CFLOOP INDEX="seq_no" FROM="1" TO="#max_rows#">
<TR>
<CFLOOP INDEX="sequence" FROM="1" TO="3">
<CFSET x = x + 1>
<CFQUERY NAME="populate" DATASOURCE="xyz">
SELECT * FROM logo_info
WHERE sequence = #x#
ORDER BY sequence
</CFQUERY>
<TD>
<CFOUTPUT QUERY="populate"> #seq_no#: #name#</CFOUTPUT>
</TD>
</CFLOOP>
</TR>
</CFLOOP>
</TABLE>
Output
The output table would look something like:
1: Joe |
2: Harry |
3: Betty |
4: Mary |
5: Bill |
6: Jose |
7: Sue |
This is an example where the maximum rows is the integer of (7 + 2)/3 = 3
Summary
You can populate a table that is a matrix, with both columns and rows, by defining the maximum number of rows, looping through the rows, plus looping through the columns for each row.
Maintain a healthy attitude toward your work
Resources and references
Websites
Books
Questions and comments
Do you have any questions, comments, or opinions on this subject? If so, send an email with your feedback. I will try to get back to you as soon as possible.
Share this page
Click on a button to bookmark or share this page through Twitter, Facebook, email, or other services:
Students and researchers
The Web address of this page is:
www.school-for-champions.com/coldfusion/
populate_matrix.htm
Please include it as a link on your website or as a reference in your report, document, or thesis.
Where are you now?
Populating a Matrix Table with ColdFusion