Populating a Matrix Table with ColdFusion
About 2 min read
(updated 27 May 2023)
Taking information from a database and linearly populating the rows of a table is relatively simple in Adobe 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