I am trying to Output query through form variables .....

Forum|Forum|16 years ago
December 16, 2009
2 replies
642 views

<cfquery name="cr" datasource="#session.db#" maxrows="10">
SELECT #form.collist# FROM #session.tn#
</cfquery>
<cfoutput>
<strong>SELECT</strong> #form.collist# <strong>FROM</strong> #session.tn#
</cfoutput>

<table align="center" border="1">
<tr>
<cfloop index="z" from="1" to="#total#">
<td><cfoutput>#trim(listGetAt('#index#',Z,','))#</cfoutput></td>
</cfloop>
</tr>
<cfoutput query = "cr">
<tr>
<td>what shoud I use your to display query</td>
</tr>
</cfoutput>
</table>

This topic has been closed for replies.

Correct answer JR__Bob__Dobbs-qSBHQ2

Here is a quick sample that might help you.

<cfset colList="name,email" />
<cfset tableName="users" />

<cfquery name="report" datasource="mydsn">
     SELECT #colList# FROM #tableName#
</cfquery>

<html>

<head>
     <title>Report</title>
</head>

<body>

<table border="1" cellpadding="2">

     <thead>
          <tr>
          <cfoutput><cfloop list="#colList#" index="colItem"><th>#HtmlEditFormat(colItem)#</th></cfloop></cfoutput>
          </tr>
     </thead>

     <tbody>
     <cfoutput query="report">
          <tr>
               <cfloop list="#colList#" index="colItem">
                    <td>#HtmlEditFormat(report[colItem][report.currentRow])#</td> <!--- use array notation to access column in query object syntax is query["COLUMN_NAME"][ROW_NUMBER] --->
               </cfloop>
          </tr>

     </cfoutput>

     </tbody>


</table>

</body>
</html>

J

JR__Bob__Dobbs-qSBHQ2

Inspiring

Can you clarify your question? I'm not exactly sure what you're trying to accomplish. Can you provide a sample query and a sample of the desired results as well as any error messages you are receiving.

You should be careful when using variables inside a SQL statement. This could result in data tampering. For example a user could submit a form field containing or named ";DELETE FROM users;" resulting in a query that removes data. You should use a white list of acceptable column/table names if you need to support user created queries.

More information on SQL injection
http://en.wikipedia.org/wiki/Sql_injection

B

bebright09Author

Known Participant

what I am trying to do is to create report generation form

1) first form user select DAtbase, tabel and column names (#form.colllist#)

then i n action page I had hrdcored this variables into cfquery to pull records

It works fine if dump but I am unable to display in table format because all columns that user selected

are in a single form variable

J

JR__Bob__Dobbs-qSBHQ2Correct answer

Inspiring

Here is a quick sample that might help you.

<cfset colList="name,email" />
<cfset tableName="users" />

<cfquery name="report" datasource="mydsn">
     SELECT #colList# FROM #tableName#
</cfquery>

<html>

<head>
     <title>Report</title>
</head>

<body>

<table border="1" cellpadding="2">

     <thead>
          <tr>
          <cfoutput><cfloop list="#colList#" index="colItem"><th>#HtmlEditFormat(colItem)#</th></cfloop></cfoutput>
          </tr>
     </thead>

     <tbody>
     <cfoutput query="report">
          <tr>
               <cfloop list="#colList#" index="colItem">
                    <td>#HtmlEditFormat(report[colItem][report.currentRow])#</td> <!--- use array notation to access column in query object syntax is query["COLUMN_NAME"][ROW_NUMBER] --->
               </cfloop>
          </tr>

     </cfoutput>

     </tbody>


</table>

</body>
</html>