Solved

Cfquery within a Cfoutput

Posted on 2001-07-01
17
640 Views
Last Modified: 2013-12-24
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
Comment
Question by:mor4eus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 2
  • +1
17 Comments
 
LVL 1

Author Comment

by:mor4eus
ID: 6243556
Oh yeah.  Before the cfif I already have a <cfoutput> open and after the if I have a </cfoutput>
0
 
LVL 1

Expert Comment

by:Soulive
ID: 6243579
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
 
LVL 1

Author Comment

by:mor4eus
ID: 6243645
Oh yeah.  Before the cfif I already have a <cfoutput> open and after the if I have a </cfoutput>
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 1

Author Comment

by:mor4eus
ID: 6243647
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
 
LVL 5

Expert Comment

by:Yog
ID: 6243806
<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
 
LVL 1

Author Comment

by:mor4eus
ID: 6243818
Yeah, just can't get it to work properly.  Not enought </cfoutput> tags.  DOn't know where to put it.
0
 
LVL 5

Expert Comment

by:Yog
ID: 6243838
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
 
LVL 5

Expert Comment

by:Yog
ID: 6243870
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
 
LVL 1

Author Comment

by:mor4eus
ID: 6243900
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
 
LVL 1

Author Comment

by:mor4eus
ID: 6243907
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
 
LVL 1

Author Comment

by:mor4eus
ID: 6243928
If you want to see an example of what I want send me your email and I will send you a link.
0
 
LVL 1

Author Comment

by:mor4eus
ID: 6243951
If you want to see an example of what I want send me your email and I will send you a link.
0
 
LVL 5

Expert Comment

by:Yog
ID: 6243971
ooty_boy2000@yahoo.com
0
 
LVL 4

Accepted Solution

by:
CF_Spike earned 200 total points
ID: 6244323
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
 
LVL 1

Author Comment

by:mor4eus
ID: 6244492
That works fine.  But why the
('getdistinct' & getreports.GDField & '.' & 'Field')
0
 
LVL 4

Expert Comment

by:CF_Spike
ID: 6244507
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
 
LVL 1

Author Comment

by:mor4eus
ID: 6244516
Thanks again Spike and Yog.
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question