cfloop/cfoutput problem

Report · Dec 07, 2012

After many years of dire predictions from myself about using MS Access for a production website, my office has finally bitten the bullet and provided me with MSSQL Enterprise. FINALLY I no longer have to append disclaimers and apologies to my requests for assistance (unless it's about my sloppy coding).

One of the changes in this switch is that my 14 or so existing Access databases have been "squished" into one huge MSSQL database. The resulting database has around 115 tables, and with no MDB file to open up and look at, I decided to build a "table browser" for my coders to use. Most of the information that we change frequently already has a web interface, but some of the tables we rarely touch. Still, I wanted a way to "look around" inside the database.

The page I've built basically creates an unordered list of table names in a scrolling left column. When you click a table name, it expands into a nested list of row names. This all works quite well. Code is as follows:

<cfquery name="GetTables" datasource="hep">
USE hep
SELECT name, max_column_id_used AS cols, object_id
FROM sys.Tables
</cfquery>
<ul>
<cfoutput query="GetTables">
<li><a href="dbmanage.cfm?table=#name###a#object_id#" name="a#object_id#">#name#</a> (#cols#)
<cfif #URL.table# eq #name#>
<ul>
<cfquery name="GetColumns" datasource="hep">
SELECT data_type, column_name
FROM Information_Schema.Columns
WHERE table_name = <cfqueryparam value="#URL.table#" cfsqltype="cf_sql_varchar">
</cfquery>
<cfloop query="GetColumns">
<li><a href="dbmanage.cfm?table=#URL.table#&column=#column_name#">#column_name#</a> (#data_type#)</li>
</cfloop>
</ul>
</cfif>
</li>
</cfoutput>
</ul>

The next thing I'm going for is that when you click a table name, the contents of that table appear in the right column. I started by using <cfdump> for this, but the results of that are rather hard to manipulate, so I've started coding my own:

<cfquery name="GetRows" datasource="hep">
SELECT *
FROM #URL.table#
</cfquery>
<table class="dbtable">
<tr><cfloop query="GetColumns"><th><cfoutput>#GetColumns.column_name#</cfoutput></th></cfloop></tr>
<tr><cfloop query="GetColumns"><td><cfoutput><strong>#GetColumns.data_type#</strong></cfoutput></td></cfloop></tr>
<cfloop query="GetColumns"><tr>
<cfloop query="GetRows"><td><cfoutput>#evaluate(GetColumns.column_name)#</cfoutput></td>
</cfloop></tr>
</cfloop>
</table>
</cfif>

The problem with the above code is that it builds the table in the wrong direction. Easy to fix, right? I mean, I just switch the loops around, like this:

<cfloop query="GetRows"><tr>
<cfloop query="GetColumns"><td><cfoutput>#evaluate(GetColumns.column_name)#</cfoutput></td>
</cfloop></tr>
</cfloop>

Unfortunately, if I do that, I get an error message that my variables are undefined. I've tried switching the <cfloop>s to <cfoutput>s, I've tried doing from="1" to="GetRows.recordcount", I've tried combinations of each, and nothing works. Why on earth will this table build fine in one direction but no the other?? I'm completely baffled!

Report · Dec 07, 2012

This has nothing to do with your question, but I'd put a maxrows attribute into your getrows query. Web browsers do not play nicely with huge amounts of data.

Report · Dec 07, 2012

That's a good idea,especially since one of those tables has 9000 rows.

Anyway, I made it work. I added a loop index and voila!

<cfoutput query="GetRows">
<tr><cfloop from="1" to="#GetColumns.recordcount#" index="i"><td>#evaluate(GetColumns.column_name)#</td></cfloop></tr>
</cfoutput>

On to the next horror in this project.