Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 650
  • Last Modified:

Cfquery within a Cfoutput

I have a problem creating a menu page.  What happens is the user selects a reports and from a table the report is generated.  Each field that makes the report can contain a list, and the list has to be generated from SQL. The first part works fine, getting the lists and assigning them different cfquery names.  I need a cfif to see if the field required a select box, but when I do the cfif I get errors with an extraneous cfif tag. I do the cfset's so they work with any cfoutput.

How do I do this??

<cfif Format is "Distinct"><cfset GDF=#FieldName#></cfoutput>
     <cfoutput query="GetDistinct#GDF#"><SELECT NAME="#GDF#">              
     <option value="#Field#" >#Field#</option>
     </cfoutput></select>
</cfif>
0
mor4eus
Asked:
mor4eus
  • 10
  • 4
  • 2
  • +1
1 Solution
 
mor4eusAuthor Commented:
Oh yeah.  Before the cfif I already have a <cfoutput> open and after the if I have a </cfoutput>
0
 
SouliveCommented:
First of all, your extraneous cfif error is caused by misplacing cfoutput tags in association with cfif tags.  If you are using conditional logic of any kind, CF requires you to have all your related code residing within the cfif tags.  For example:

<cfoutput>
#myvar#

 <cfif myvar is "me">
  This is myvar #myvar#

</cfoutput>

 </cfif>

The preceding code would generate an extraneous tag error. Basically cf is telling you that it can't have orphaned tags lying around.  In my example if myvar didn't equal "me" than cf would theoretically never see the closing output tag so it wouldn't function.  Hope this starts helping your issue.  I would also advice posting more of your code.
0
 
mor4eusAuthor Commented:
Oh yeah.  Before the cfif I already have a <cfoutput> open and after the if I have a </cfoutput>
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
mor4eusAuthor Commented:
Here is all the code

<cfquery name="GetReports" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1'
</cfquery>
<cfquery name="GetDistincts" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1' AND Format = 'Distinct'
</cfquery>
<cfoutput query="GetDistincts">
<cfquery name="GetDistinct#Field#" datasource="ReportsSQL">
SELECT distinct #Field# from Catalogue
</cfquery>
</cfoutput>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
     <title>Untitled</title>
</head>

<body>
<table>
<tr bgcolor="#FFFFCC"><td align="right"><font size="1">Check to include<br>this field</font></td><td> Matching</td></tr>

<cfoutput query="GetReports">
<tr><td align="right">#Legend# <input type="Checkbox" name="#Field#Tick" value="1" <cfif Checkbox is 1>checked</cfif>></td>
     
<td>
<cfif Format is "Distinct"><cfset GDF=#Field#></cfoutput>
     <cfoutput query="GetDistinct#GDField#"><SELECT NAME="#GDF#">              
     <option value="#Field#" >#Field#</option>
     </cfoutput></select>
</cfif>
#field#
</td>
</tr>
</cfoutput>
</table>
</body>
</html>
0
 
YogCommented:
<cfoutput query="GetReports">
<tr>
<td align="right">
#Legend#
<input type="Checkbox" name="#Field#Tick" value="1"
<cfif Checkbox is 1>checked</cfif>
>
</td>
<td>
<cfset myquery = "GetDistinct#GDField#">
<cfset myquery = evaluate(myquery)>

<cfoutput query="GetDistinct#GDField#">
<Cfloop from="1" to="#myquery.recordcount#" index="i_Index">

<cfif Format is "Distinct">
<cfset GDF=#Field[i_Index]#>
<SELECT NAME="#GDF#">        
<option value="#Field[i_Index]#" >#Field[i_Index]#</option>
</select>
</cfif>
#field[i_Index]#

</cfloop>

</td>
</tr>
</cfoutput>

tell me if this is what you want ..

cheers,
0
 
mor4eusAuthor Commented:
Yeah, just can't get it to work properly.  Not enought </cfoutput> tags.  DOn't know where to put it.
0
 
YogCommented:
hi you dont need to do 3 queries at all, just 2 queries would do i think , is format a field name or you need unique records..



0
 
YogCommented:
hi,
check if this works, if you could tell the query field names that may help coz #field# is confusing for all the query ..is the name same in all cases
-----------------
<cfquery name="GetReports" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1'
</cfquery>
<cfquery name="GetDistincts" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1' AND Format = 'Distinct'
</cfquery>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>Untitled</title>
</head>

<body>
<table>
<tr bgcolor="#FFFFCC"><td align="right"><font size="1">Check to include<br>this field</font></td><td>
Matching</td></tr>

<cfloop from="1" query="#GetReports.RecordCount#" index="i">
<tr><td align="right">
<cfoutput>
#Legend[i]#
 <input type="Checkbox" name="#Field[i]#Tick" value="1"
 <cfif Checkbox is 1>checked</cfif>>
</cfoutput>
</td>
   
<td>
<cfif Format is "Distinct">
<cfset GDF=#Field[i]#>
<cfset GDF = evaluate(GDF)>
<cfoutput query="GetDistincts">
     <cfquery name="GetDistinct#Field[i]#" datasource="ReportsSQL">
          SELECT distinct #Field[i]# from Catalogue
     </cfquery>
     <SELECT NAME="#GDF#">
     <cfoutput query="GetDistinct#Field[i]#">
      <option value="#Field#" >#Field#</option>    
      </cfoutput>
     </select>
#field[i]#
</cfoutput>
</cfif>
</td>
</tr>
</cfloop>
</table>
</body>
</html>
0
 
mor4eusAuthor Commented:
Yes the name is the same.  What it does it generate a selection page to genereate a report.  The column field are the different selection criteria.  Etc Album, Artist, CatalogueNo, Format, Price.

A sample line from the sql for the field is like this.

ReportName     TableName     Field     Order     Legend     Format     DisplayFormat     CheckBox     Default     Range
cat1     catalogue     artist     30     Artist Name     text     text     1          

Hope this helps.  STill the same though with the code though.
0
 
mor4eusAuthor Commented:
Oh, the multiple queries are for the multiple fields which require a drop down.  For instance: the fields are first..

Format - alb, single, video...
Genre - Pop, Rock, R&B, Dance...
Label - Sputnik, Jive, Echo
0
 
mor4eusAuthor Commented:
If you want to see an example of what I want send me your email and I will send you a link.
0
 
mor4eusAuthor Commented:
If you want to see an example of what I want send me your email and I will send you a link.
0
 
YogCommented:
ooty_boy2000@yahoo.com
0
 
CF_SpikeCommented:
If you are having problems with nested cfoutput tags you can use cfloop to output a query. The code you posted earlier could be rewritten as below. I am not sure which queries the variables are coming from, so I have guessed the query scope, but the principle is correct.

<cfquery name="GetReports" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1'
</cfquery>
<cfquery name="GetDistincts" datasource="ReportsSQL">
SELECT * FROM Reports WHERE ReportName = 'cat1' AND Format = 'Distinct'
</cfquery>
<cfloop query="GetDistincts">
<cfquery name="GetDistinct#Field#" datasource="ReportsSQL">
SELECT distinct #Field# from Catalogue
</cfquery>
</cfloop>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>Untitled</title>
</head>

<body>
<table>
<tr bgcolor="#FFFFCC"><td align="right"><font size="1">Check to include<br>this field</font></td><td>
Matching</td></tr>
<CFOUTPUT>
<cfloop query="GetReports">
<tr><td align="right">#Legend# <input type="Checkbox" name="#Field#Tick" value="1" <cfif Checkbox is
1>checked</cfif>></td>
   
<td>
<cfif Format is "Distinct"><cfset GDF=#getreports.Field#>
    <cfloop query="GetDistinct#GDField#"><SELECT NAME="#GDF#">              
    <option value="#Evaluate('getdistinct' & getreports.GDField & '.' & 'Field')#" >#Evaluate('getdistinct' & getreports.GDField & '.' & 'Field')#</option>
    </cfloop></select>
</cfif>
#getreports.field#
</td>
</tr>
</cfloop>
</CFOUTPUT>
</table>
</body>
</html>

Spike
0
 
mor4eusAuthor Commented:
That works fine.  But why the
('getdistinct' & getreports.GDField & '.' & 'Field')
0
 
CF_SpikeCommented:
In order to dynamically evaluate the correct query and colunn name I had to combine the static parts - 'getdistinct', '.' and 'field' with the dynamic part -getreports.GTField. This could be rewritten as:

"getdistinct#getreports.GTField#.field" without the evaluate function. It's just a question of syntax preference.

Spike
0
 
mor4eusAuthor Commented:
Thanks again Spike and Yog.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 10
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now