SQL Statement "on the fly" 1

[jhall01]

SQL statement creation on the fly itself is not too difficult. I currently have a form that allows the user to choose the columns(select statement) and the criteria(where statement)

This then creates a Dynamic SQL statement for report building through the web. Here is my problem:

How do I output dynamic columns into an HTML Table

I can loop through the columnnames to create the header row, but how do I generate the columns and how they line up in the recordset:

EX-

Select choices
fname
lname
addr
zip
state

where choices
gender
state

Now I select fname, lname & gender = 'M'

this dynamically creates a name recordset

how can i display each selected item in a column if I don't know how many columns there will be?

Thanks,

Jon

 

[Fleer]

Hi Jhall01,

I had about the same problem as you have now, what I did was creating a table called fields (or whatever you like) and added fields like fieldname, showname, length, sortorder, etc... so you could get a table looking like this :

FieldName - ShowName - Length - SortOrder
fname - FirstName - 50 - 1
lname - LastName - 35 - 2
addr - Address - 50 - 3
zip - Zip Code - 10 - 4
state - State - 30 - 5

now you can create a query with the select choices = FieldName and get all information about the header row, name, length of the field, the sortorder and maybe more information you want to add to this. The number of records returned from this query is the number of columns you need in your HTML table.

Hope you get the idea.

Fleer

 

[stevebooth]

In general, I use a variable to hold the list of field names to be returned in the select statement. The list is comma delimited. I'd then loop through the list to display the heading. ListLen tells you how many columns. You can also set up a corresponding list of column labels. There used to be a tag CF_DUMP written by Ben Forta that deals with this.

 

[philhege]

Hi Jhall01,

You can use the query.columnlist variable to produce the results you want.

Generically speaking, if you have a query,

select field1, field2, field3
FROM Mytable
WHERE whatever

then the query.columnlist variable will be a list of the fields in the query.
So we need a table:
<table whateverattributesyouneed>
We can use CFLOOP to construct the headers:
<tr>
<CFLOOP index=&quot;thiscol&quot; list=&quot;#query.columnlist#&quot;>
<td>#thiscol#</td>
</cfloop>
</tr>
Now, for the data:
<cfoutput query=&quot;myquery&quot;>
<tr>
<CFLOOP index=&quot;thiscol&quot; list=&quot;#query.columnlist#&quot;>
<td>#evaluate(thiscol)#</td>
</cfloop>
</tr>
</cfoutput>
</table>

Done. Works with any query, any number of columns. You can get fancy and join the query to lookup tables for friendly header labels, etc. as mentioned above.

HTH,



Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....