Solved

Cfquery within a Cfoutput

Posted on 2001-07-01
17
626 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now