Displaying data from an unknown table

[Blazeix]

I have a simple search I need to implement, but it needs to work for any table.
I need to display all the data in #form.itemtype#, I have the following
cfquery that I have written:

<cfquery datasource=#sysDB# name="results">
SELECT *
FROM #FORM.itemType#
WHERE #FORM.FieldToSearch# LIKE '%#FORM.searchTerm#%'
</cfquery>

This works well, as I can see from a cfdump. The problem is I don't know how
to display the information in an html table, because I don't know any of the
columns. Can somebody please help? thanks.

[Dan Bracuk]

<blockquote>quote:<br><hr><i>Originally posted by: <b><b>Blazeix</b></b></i>
I have a simple search I need to implement, but it needs to work for any
table. I need to display all the data in #form.itemtype#, I have the following
cfquery that I have written:

<cfquery datasource=#sysDB# name="results">
SELECT *
FROM #FORM.itemType#
WHERE #FORM.FieldToSearch# LIKE '%#FORM.searchTerm#%'
</cfquery>

This works well, as I can see from a cfdump. The problem is I don't know how
to display the information in an html table, because I don't know any of the
columns. Can somebody please help? thanks.<hr></blockquote>
Did you try reading the answer to the question you asked about field names?

[paross1]

One method.

<cfquery datasource=#sysDB# name="results">
SELECT *
FROM #FORM.itemType#
WHERE #FORM.FieldToSearch# LIKE '%#FORM.searchTerm#%'
</cfquery>


<cfoutput>
<cfset colHeaderNames = ArrayToList(results.getColumnList())/>
</cfoutput>
<table border="1">
<tr><cfloop list="#colHeaderNames#" index="col" delimiters=",">
<th align="left" nowrap><cfoutput>#col#</cfoutput></th>
</cfloop></tr>
<cfoutput query="results">
<tr><CFLOOP LIST="#colHeaderNames#" INDEX="col">
<td align="left" nowrap>#Evaluate(col)#</td></CFLOOP></tr>
</cfoutput>
</table>

Phil

[Blazeix]

I'm sorry, I did. I got the fieldnames, but then I can't figure out how to use
them. I got the first column title off the list, and tried a simple
#ListFirst(columnlist)#, but it of course just listed the the column name in
plain text. I have tried various other methods, such as double pound sign, but
that just listed it in plain text with pound signs around it. I'm sorry I'm
asking so many questions, I'm rather new to ColdFusion. I've read the
Coldfusion MX7 manual, but unfortunately I found that it didn't talk to much
about dynamic sql, there were only a few short pages on it. I don't want to
really get all my answers from the forum, but if someone would even just point
me in the right direction or something it would be helpful.

[Blazeix]

Ah. Paross posted as I was typing my reply.
Thanks, it was the evaluate method that I didn't know about. I can't seem to
find it in my coldfusion manual, although I just looked it up online at <a
target=_blank class=ftalternatingbarlinklarge
href="http://livedocs.macromedia.com.">http://livedocs.macromedia.com.</a> I
didn't know that website existed prior to just now; I had done everything out
of the manual. Thanks!

[Dan Bracuk]

<blockquote>quote:<br><hr><i>Originally posted by: <b><b>Blazeix</b></b></i>
I'm sorry, I did. I got the fieldnames, but then I can't figure out how to use
them. I got the first column title off the list, and tried a simple
#ListFirst(columnlist)#, but it of course just listed the the column name in
plain text. I have tried various other methods, such as double pound sign, but
that just listed it in plain text with pound signs around it. I'm sorry I'm
asking so many questions, I'm rather new to ColdFusion. I've read the
Coldfusion MX7 manual, but unfortunately I found that it didn't talk to much
about dynamic sql, there were only a few short pages on it. I don't want to
really get all my answers from the forum, but if someone would even just point
me in the right direction or something it would be helpful.<hr></blockquote>
What you appear to be attempting is efficient in terms of getting the most
bang for the keystroke but it is not necessarily going to result in the most
user freindly result. For one thing, you are using select *. A well designed
database will likely have fields that shouldn't be shown to normal people,
numeric primary key identifiers being a prime example.

The code you have so far is going to give you the actual column names in
alphabetical order. Once again, that might not be the best way to display data.

Finally if you have any date fields, and you don't format them, you are in for
an unpleasant surprise.

Another approach is to create string variables based on what you receive from
the form. These variables will contain your sql.

[paross1]

Dan,

Actually, doing it this way would return the rows in alpha order:

<table border="1">
<tr><cfloop list="#results.ColumnList#" index="col" delimiters=",">
<th align="left" nowrap><cfoutput>#col#</cfoutput></th>
</cfloop></tr>
<cfoutput query="results">
<tr><cfloop list="#results.ColumnList#" index="col">
<td align="left" nowrap>#results[col][CurrentRow]#</td></cfloop></tr>
</cfoutput>
</table>

Doing it the way that I suggested in my other post should return the columns
in the same order as the are in the database.

Phil