Eliminating CFM extensions in IIS 7
I'm no expert on this, and struggle with IIS config, so hopefully someone on
the list can help. I'm trying to use URLs like
mysite.com/news/2014/the-article.
Below is a url rewrite that rewrites a request like mysite.com/news/ to
myside/news.cfm. That part works. But, if you add another directory like
mysite.com/news/2014 it tries to rewrite to mysite.com/news/2014.cfm and
that does no good. Does anyone know if there is a way to get it this to
rewrite the .cfm only on the first url var after the first /?
Thanks
?xml version=1.0 encoding=UTF-8?
configuration
system.webServer
rewrite
rules
rule name=AddCFM enabled=true
match url=.* negate=false /
conditions
add input={REQUEST_FILENAME} matchType=IsFile negate=true /
add input={REQUEST_FILENAME} matchType=IsDirectory negate=true /
add input={URL} pattern=(.*)\.(.*) negate=true /
/conditions
action type=Rewrite url={R:0}.cfm /
/rule
/rules
/rewrite
/system.webServer
/configuration
Robert Harrison
Full Stack Developer
AIMG
rharri...@aimg.com
Main Office: 704-321-1234 ext.121
Direct Line: 516-302-4345
www.aimg.com
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359786
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Eliminating CFM extensions in IIS 7
You need to specify a rewrite handler for everything in the /news/ path.
Assuming /news.cfm handles all of your requests for that path, your rule would
look something like this:
rule name=News Handler stopProcessing=true
match url=^news/(.*)
ignoreCase=false /action type=Rewrite url=/news.cfm?{R:1}
appendQueryString=false /
/rule
If you add this rule above the AddCFM rule, it will stop processing
subsequent rules and should give you the behavior you are looking for.
HTH,
Jon
On Dec 10, 2014, at 9:42 AM, Robert Harrison rharri...@aimg.com wrote:
I'm no expert on this, and struggle with IIS config, so hopefully someone on
the list can help. I'm trying to use URLs like
mysite.com/news/2014/the-article.
Below is a url rewrite that rewrites a request like mysite.com/news/ to
myside/news.cfm. That part works. But, if you add another directory like
mysite.com/news/2014 it tries to rewrite to mysite.com/news/2014.cfm and
that does no good. Does anyone know if there is a way to get it this to
rewrite the .cfm only on the first url var after the first /?
Thanks
?xml version=1.0 encoding=UTF-8?
configuration
system.webServer
rewrite
rules
rule name=AddCFM enabled=true
match url=.* negate=false /
conditions
add input={REQUEST_FILENAME} matchType=IsFile negate=true /
add input={REQUEST_FILENAME} matchType=IsDirectory negate=true /
add input={URL} pattern=(.*)\.(.*) negate=true /
/conditions
action type=Rewrite url={R:0}.cfm /
/rule
/rules
/rewrite
/system.webServer
/configuration
Robert Harrison
Full Stack Developer
AIMG
rharri...@aimg.com
Main Office: 704-321-1234 ext.121
Direct Line: 516-302-4345
www.aimg.com
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359787
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Eliminating CFM extensions in IIS 7
News was just an example. Can I do it for every first level item
mysite.com/this or mysite.com/that without needing to add a specific rule
for each?
Robert Harrison
Full Stack Developer
AIMG
rharri...@aimg.com
Main Office: 704-321-1234 ext.121
Direct Line: 516-302-4345
www.aimg.com
-Original Message-
From: Jon Clausen [mailto:jon_clau...@silowebworks.com]
Sent: Wednesday, December 10, 2014 10:05 AM
To: cf-talk
Subject: Re: Eliminating CFM extensions in IIS 7
You need to specify a rewrite handler for everything in the /news/ path.
Assuming /news.cfm handles all of your requests for that path, your rule
would look something like this:
rule name=News Handler
stopProcessing=true
match url=^news/(.*)
ignoreCase=false /action type=Rewrite url=/news.cfm?{R:1}
appendQueryString=false /
/rule
If you add this rule above the AddCFM rule, it will stop processing
subsequent rules and should give you the behavior you are looking for.
HTH,
Jon
On Dec 10, 2014, at 9:42 AM, Robert Harrison rharri...@aimg.com wrote:
I'm no expert on this, and struggle with IIS config, so hopefully
someone on the list can help. I'm trying to use URLs like
mysite.com/news/2014/the-article.
Below is a url rewrite that rewrites a request like mysite.com/news/
to myside/news.cfm. That part works. But, if you add another
directory like
mysite.com/news/2014 it tries to rewrite to mysite.com/news/2014.cfm
and that does no good. Does anyone know if there is a way to get it
this to rewrite the .cfm only on the first url var after the first /?
Thanks
?xml version=1.0 encoding=UTF-8? configuration
system.webServer rewrite rules rule name=AddCFM
enabled=true match url=.* negate=false / conditions add
input={REQUEST_FILENAME} matchType=IsFile negate=true / add
input={REQUEST_FILENAME} matchType=IsDirectory negate=true /
add input={URL} pattern=(.*)\.(.*) negate=true / /conditions
action type=Rewrite url={R:0}.cfm / /rule /rules /rewrite
/system.webServer /configuration
Robert Harrison
Full Stack Developer
AIMG
rharri...@aimg.com
Main Office: 704-321-1234 ext.121
Direct Line: 516-302-4345
www.aimg.com
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359788
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: CF 11 Admin Panel
Thanks for the link Russ After I did the install I went ahead and did the updates and it crash CF. I had to uninstall and re-install. Any ideas or advice? Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edu -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: Tuesday, December 09, 2014 9:27 AM To: cf-talk Subject: Re: CF 11 Admin Panel http://www.adobe.com/content/dam/Adobe/en/products/coldfusion/pdfs/cf11/cf11-lockdown-guide.pdf On Tue, Dec 9, 2014 at 1:24 PM, Steve LaBadie slaba...@po-box.esu.edu wrote: We have just come out of the dark ages from a Window 2003 server with CF MX7 to Windows 2012 R2 with CF11 standard. I just completed the install and want to know if someone could point me in the right direct for best practices for setting up the Admin Panel. Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edumailto:slaba...@esu.edu [facebook-16x16]http://www.facebook.com/eaststroudsburguniversity [twitter-16x16] http://twitter.com/esuniversity [youtube-16x16] http://www.youtube.com/user/esuedu ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359789 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Eliminating CFM extensions in IIS 7
Thank you. I was able to use this and adjust to the solution I needed.
Really appreciate the assist.
Robert Harrison
Full Stack Developer
AIMG
rharri...@aimg.com
Main Office: 704-321-1234 ext.121
Direct Line: 516-302-4345
www.aimg.com
-Original Message-
From: Jon Clausen [mailto:jon_clau...@silowebworks.com]
Sent: Wednesday, December 10, 2014 10:05 AM
To: cf-talk
Subject: Re: Eliminating CFM extensions in IIS 7
You need to specify a rewrite handler for everything in the /news/ path.
Assuming /news.cfm handles all of your requests for that path, your rule
would look something like this:
rule name=News Handler
stopProcessing=true
match url=^news/(.*)
ignoreCase=false /action type=Rewrite url=/news.cfm?{R:1}
appendQueryString=false /
/rule
If you add this rule above the AddCFM rule, it will stop processing
subsequent rules and should give you the behavior you are looking for.
HTH,
Jon
On Dec 10, 2014, at 9:42 AM, Robert Harrison rharri...@aimg.com wrote:
I'm no expert on this, and struggle with IIS config, so hopefully
someone on the list can help. I'm trying to use URLs like
mysite.com/news/2014/the-article.
Below is a url rewrite that rewrites a request like mysite.com/news/
to myside/news.cfm. That part works. But, if you add another
directory like
mysite.com/news/2014 it tries to rewrite to mysite.com/news/2014.cfm
and that does no good. Does anyone know if there is a way to get it
this to rewrite the .cfm only on the first url var after the first /?
Thanks
?xml version=1.0 encoding=UTF-8? configuration
system.webServer rewrite rules rule name=AddCFM
enabled=true match url=.* negate=false / conditions add
input={REQUEST_FILENAME} matchType=IsFile negate=true / add
input={REQUEST_FILENAME} matchType=IsDirectory negate=true /
add input={URL} pattern=(.*)\.(.*) negate=true / /conditions
action type=Rewrite url={R:0}.cfm / /rule /rules /rewrite
/system.webServer /configuration
Robert Harrison
Full Stack Developer
AIMG
rharri...@aimg.com
Main Office: 704-321-1234 ext.121
Direct Line: 516-302-4345
www.aimg.com
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359790
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfmail causing problems for our server
My customer's site allows interested visitors to subscribe to intermittent e-mail notifications regarding his annual event. My hosting provider tells me that use of cfmail to contact his 5,000+ mailing list is triggering blacklisting when we use it to notify subscribers of event changes and additions. He (the hosting provider) insists we use a third party e-mail service instead. Can anyone recommended such a service that can be trusted not to resell the DB? Thank you. Dave Long NorthGoods Merchant Services ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359791 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfmail causing problems for our server
Use a mail relay like STMP.com. Just change the smtp server for your mail blast to send through the relay. Robert Harrison Full Stack Developer AIMG rharri...@aimg.com Main Office: 704-321-1234 ext.121 Direct Line: 516-302-4345 www.aimg.com -Original Message- From: Dave Long [mailto:d...@northgoods.com] Sent: Wednesday, December 10, 2014 1:23 PM To: cf-talk Subject: cfmail causing problems for our server My customer's site allows interested visitors to subscribe to intermittent e-mail notifications regarding his annual event. My hosting provider tells me that use of cfmail to contact his 5,000+ mailing list is triggering blacklisting when we use it to notify subscribers of event changes and additions. He (the hosting provider) insists we use a third party e-mail service instead. Can anyone recommended such a service that can be trusted not to resell the DB? Thank you. Dave Long NorthGoods Merchant Services ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359792 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfmail causing problems for our server
Amazon SES was made for just this type of scenario. On Wed, Dec 10, 2014 at 1:23 PM, Dave Long d...@northgoods.com wrote: My customer's site allows interested visitors to subscribe to intermittent e-mail notifications regarding his annual event. My hosting provider tells me that use of cfmail to contact his 5,000+ mailing list is triggering blacklisting when we use it to notify subscribers of event changes and additions. He (the hosting provider) insists we use a third party e-mail service instead. Can anyone recommended such a service that can be trusted not to resell the DB? Thank you. Dave Long NorthGoods Merchant Services ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359793 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfmail causing problems for our server
My recommendation would be to have them create a MailChimp or Vertical Response (Constant Contact, Exact Target, etc.) account and just send emails through their API. VRs API is especially easy to send custom HTML through (http://developers.verticalresponse.com/docs/read/api_reference/emails ). Then you can also ensure CAN-SPAM compliance as well. If youre using a CMS to send the mail through, then you might need to create a module to hook in to the main mailing list feature, but the long-term result would be better. Jon On Dec 10, 2014, at 1:23 PM, Dave Long d...@northgoods.com wrote: My customer's site allows interested visitors to subscribe to intermittent e-mail notifications regarding his annual event. My hosting provider tells me that use of cfmail to contact his 5,000+ mailing list is triggering blacklisting when we use it to notify subscribers of event changes and additions. He (the hosting provider) insists we use a third party e-mail service instead. Can anyone recommended such a service that can be trusted not to resell the DB? Thank you. Dave Long NorthGoods Merchant Services ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359794 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfmail causing problems for our server
I use and like mail chimp Rob Sent from my Verizon Wireless 4G LTE smartphone div Original message /divdivFrom: Dave Long d...@northgoods.com /divdivDate:12/10/2014 12:23 PM (GMT-06:00) /divdivTo: cf-talk cf-talk@houseoffusion.com /divdivSubject: cfmail causing problems for our server /divdiv /div My customer's site allows interested visitors to subscribe to intermittent e-mail notifications regarding his annual event. My hosting provider tells me that use of cfmail to contact his 5,000+ mailing list is triggering blacklisting when we use it to notify subscribers of event changes and additions. He (the hosting provider) insists we use a third party e-mail service instead. Can anyone recommended such a service that can be trusted not to resell the DB? Thank you. Dave Long NorthGoods Merchant Services ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359795 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: cfmail causing problems for our server
Thanks, everyone. I'll check them out. -Original Message- From: Rob Voyle [mailto:robvo...@voyle.com] Sent: Wednesday, December 10, 2014 12:42 PM To: cf-talk Subject: RE: cfmail causing problems for our server I use and like mail chimp Rob Sent from my Verizon Wireless 4G LTE smartphone div Original message /divdivFrom: Dave Long d...@northgoods.com /divdivDate:12/10/2014 12:23 PM (GMT-06:00) /divdivTo: cf-talk cf-talk@houseoffusion.com /divdivSubject: cfmail causing problems for our server /divdiv /div My customer's site allows interested visitors to subscribe to intermittent e-mail notifications regarding his annual event. My hosting provider tells me that use of cfmail to contact his 5,000+ mailing list is triggering blacklisting when we use it to notify subscribers of event changes and additions. He (the hosting provider) insists we use a third party e-mail service instead. Can anyone recommended such a service that can be trusted not to resell the DB? Thank you. Dave Long NorthGoods Merchant Services ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359796 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: bypassing the site-wide error handler
Thanks Dave, Rodney. That really helped. It turned out our issue was the same cfscript in the application's header and the sitewide error handler's header. When the second instance of cfscript tried to run, it broke the error handler, thus presenting the raw error to the user. best, Chris On Mon, Dec 8, 2014 at 10:02 PM, Rodney Enke renk...@gmail.com wrote: The site wide error handler will not run if there is a local error handler, such as CFTRY/CFCATCH or CFERROR, unless the CFTRY/CFCATCH block rethrows the error. - Rodney On Mon, Dec 8, 2014 at 8:52 PM, Dave Watts dwa...@figleaf.com wrote: Hi, when can an application bypass the CF v9 site-wide error handler? We host an app, that uses cftry/catch, but that appears to not handle the errors correctly. The errors do not trigger the site-wide error handler, and the errors are displayed to the users. The site-wide error handler works fine for other applications, and testing. I don't think the application can, by itself, bypass the site-wide error handler. I'd take a look at the code in the site-wide error handler to see if there's anything in there that would prevent it from doing anything when an error occurs. You might also look at how you're using CFTRY/CFCATCH - maybe you are catching the error after all, and your error trapping code doesn't do anything useful. Finally, the site-wide error handler will only catch run-time exceptions, I think. I could be wrong about this, as I haven't worked with the site-wide error handler in a while, but if this is the case and you have a compile-time error in your code, it's going to be displayed. This is something that used to be handled with the CFERROR tag (type=request), but you really just shouldn't have any compile-time errors anyway. Dave Watts, CTO, Fig Leaf Software 1-202-527-9569 http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Service-Disabled Veteran-Owned Small Business (SDVOSB) on GSA Schedule, and provides the highest caliber vendor- authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359797 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Sending SMS
Verizon: vtext.com Nextel: messaging.nextel.com Sprint: messaging.sprintpcs.com Cingular: cingularme.com Virgin Mobile: vmobl.com T-Mobile: tmomail.net Thanks this was helpful. Dennis Powers UXB Internet - A website Design and Hosting Company P.O. Box 6028, Wolcott, CT 06716 - T:203-879-2844 W: http://www.uxbinternet.com W: http://www.ctbusinesslist.com ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359798 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Sending SMS
you're welcome. Yea this has been working well for us for years, to send out alerts to our registered users. On Wed, Dec 10, 2014 at 3:09 PM, UXB Internet denn...@uxbinternet.com wrote: Verizon: vtext.com Nextel: messaging.nextel.com Sprint: messaging.sprintpcs.com Cingular: cingularme.com Virgin Mobile: vmobl.com T-Mobile: tmomail.net Thanks this was helpful. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359799 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfmail causing problems for our server
if it is just bulk SMTP you need then www.mandrill.com allows 12,000 per month for free. For something more advanced try www.sendgrid.com, they also offer an API which allows you to send emails without using CFMAIL or SMTP which has its benefits. If you want an actual mailing list manager then mailchimp.com On Wed, Dec 10, 2014 at 6:23 PM, Dave Long d...@northgoods.com wrote: My customer's site allows interested visitors to subscribe to intermittent e-mail notifications regarding his annual event. My hosting provider tells me that use of cfmail to contact his 5,000+ mailing list is triggering blacklisting when we use it to notify subscribers of event changes and additions. He (the hosting provider) insists we use a third party e-mail service instead. Can anyone recommended such a service that can be trusted not to resell the DB? Thank you. Dave Long NorthGoods Merchant Services ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359800 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: CF 11 Admin Panel
the lockdown guide is not perfect and in my experience just following the guide exactly does cause problems, so you do need to really know what you are doing on the server with permissions to cover the bits it doesn't tell you. I do not follow it myself, I have my own process, so I cannot tell you exactly where it fails. The best bet would be to test CF after each step and see which one is buggering it up for you. The bare minimum bits you MUST do is 1. run CF under a separate user account which only has access to the folders that CF needs access to. So CF itself, the folder where the websites are store, OS temp folders, the temp folder of the user under which CF is now running (windows only requires this I think) 2. make sure the internal cf web server is not enabled 3. lock down the cfadmin so that it is not publicly accessible from any website. Personally I do this by using IP address restrictions so the entire default website where cfadmin resides is only accessible via localhost and via our secure VPN. 4. make a copy of the CFIDE with only the files that your site requires and use this for the Vdir. Ideally you should avoid using any CF features that require the CFIDE anyway, as they all pretty much suck and you will not find many in the community who wont say the same. So this is any of the UI tags, flash forms, CFFORM tags etc, there are better alternatives. Avoiding the CFIDE will also most likely make your site Railo compatible too. 5. On Wed, Dec 10, 2014 at 3:30 PM, Steve LaBadie slaba...@po-box.esu.edu wrote: Thanks for the link Russ After I did the install I went ahead and did the updates and it crash CF. I had to uninstall and re-install. Any ideas or advice? Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edu -Original Message- From: Russ Michaels [mailto:r...@michaels.me.uk] Sent: Tuesday, December 09, 2014 9:27 AM To: cf-talk Subject: Re: CF 11 Admin Panel http://www.adobe.com/content/dam/Adobe/en/products/coldfusion/pdfs/cf11/cf11-lockdown-guide.pdf On Tue, Dec 9, 2014 at 1:24 PM, Steve LaBadie slaba...@po-box.esu.edu wrote: We have just come out of the dark ages from a Window 2003 server with CF MX7 to Windows 2012 R2 with CF11 standard. I just completed the install and want to know if someone could point me in the right direct for best practices for setting up the Admin Panel. Steve LaBadie, Web Manager East Stroudsburg University 570-422-3999 slaba...@esu.edumailto:slaba...@esu.edu [facebook-16x16]http://www.facebook.com/eaststroudsburguniversity [twitter-16x16] http://twitter.com/esuniversity [youtube-16x16] http://www.youtube.com/user/esuedu ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359801 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Creating columns that aren't included in a query result
I am hoping I can explain this correctly. I have a table which houses responses to a survey. The columns can have any of the following values: Agree,Disagree,Neither Agree nor Disagree,Strongly Agree,Strongly Disagree I have a table with the headers equal to all of the values above. I have a 2nd row that loops through the query and adds a cell in the row for each value: EX: td#query.value#/td Here is my problem: There are times when not all of the values exists, so I don't have enough of the dynamic cells in the row. EX: I have a header called Agree but the value agree is not returned from the query so it messes up the row. My question is how can I create a fake column from the query OR create an empty cell in the row if a value doesn't exist? NOTE: The values don't exist so I can test for existence if that makes sense. Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359802 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
why do you need the 2nd loop rather than simply outputting the value of the query column from the main loop? On Thu, Dec 11, 2014 at 12:02 AM, Torrent Girl moniqueb...@gmail.com wrote: I am hoping I can explain this correctly. I have a table which houses responses to a survey. The columns can have any of the following values: Agree,Disagree,Neither Agree nor Disagree,Strongly Agree,Strongly Disagree I have a table with the headers equal to all of the values above. I have a 2nd row that loops through the query and adds a cell in the row for each value: EX: td#query.value#/td Here is my problem: There are times when not all of the values exists, so I don't have enough of the dynamic cells in the row. EX: I have a header called Agree but the value agree is not returned from the query so it messes up the row. My question is how can I create a fake column from the query OR create an empty cell in the row if a value doesn't exist? NOTE: The values don't exist so I can test for existence if that makes sense. Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359803 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Creating columns that aren't included in a query result
If I understand correctly Select response1 from [table] The resulting Query can equal 'response1' == 'Agree' or 'Disagree' or 'Neither Agree nor Disagree' or 'Strongly Agree' or 'Strongly Disagree' Then you loop over the results from the query and (I am guessing here) you want to place an 'X' in the box under the correct header? Simplest I can think of would be cfoutput query=[qname] tr cfswitch expression=#[qname].response1# cfcase value=Agree tdX/tdtd/tdtd/tdtd/tdtd/td /cfcase cfcase value=Disagree td/tdtdX/tdtd/tdtd/tdtd/td /cfcase cfcase value=Neither Agree nor Disagree td/tdtd/tdtdX/tdtd/tdtd/td /cfcase cfcase value=Strongly Agree td/tdtd/tdtd/tdtdX/tdtd/td /cfcase cfcase value=Strongly Disagree td/tdtd/tdtd/tdtd/tdtdX/td /cfcase /cfswitch /tr /cfoutput -- William Seiter -Original Message- From: Torrent Girl [mailto:moniqueb...@gmail.com] Sent: Wednesday, December 10, 2014 4:03 PM To: cf-talk Subject: Creating columns that aren't included in a query result I am hoping I can explain this correctly. I have a table which houses responses to a survey. The columns can have any of the following values: Agree,Disagree,Neither Agree nor Disagree,Strongly Agree,Strongly Disagree I have a table with the headers equal to all of the values above. I have a 2nd row that loops through the query and adds a cell in the row for each value: EX: td#query.value#/td Here is my problem: There are times when not all of the values exists, so I don't have enough of the dynamic cells in the row. EX: I have a header called Agree but the value agree is not returned from the query so it messes up the row. My question is how can I create a fake column from the query OR create an empty cell in the row if a value doesn't exist? NOTE: The values don't exist so I can test for existence if that makes sense. Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359804 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
Not quite. The value returned in a COUNT of the number of each response (sorry I should have said that earlier. So, it no one answered disagree it is not returned, but in the loop I want it represented as 0. If I understand correctly Select response1 from [table] The resulting Query can equal 'response1' == 'Agree' or 'Disagree' or 'Neither Agree nor Disagree' or 'Strongly Agree' or 'Strongly Disagree' Then you loop over the results from the query and (I am guessing here) you want to place an 'X' in the box under the correct header? Simplest I can think of would be cfoutput query=[qname] tr cfswitch expression=#[qname].response1# cfcase value=Agree tdX/tdtd/tdtd/tdtd/tdtd/td /cfcase cfcase value=Disagree td/tdtdX/tdtd/tdtd/tdtd/td /cfcase cfcase value=Neither Agree nor Disagree td/tdtd/tdtdX/tdtd/tdtd/td /cfcase cfcase value=Strongly Agree td/tdtd/tdtd/tdtdX/tdtd/td /cfcase cfcase value=Strongly Disagree td/tdtd/tdtd/tdtd/tdtdX/td /cfcase /cfswitch /tr /cfoutput -- William Seiter I am hoping I can explain this correctly. I have a table which houses responses to a survey. The columns can have any of the following values: Agree,Disagree,Neither Agree nor Disagree,Strongly Agree,Strongly Disagree I have a table with the headers equal to all of the values above. I have a 2nd row that loops through the query and adds a cell in the row for each value: EX: td#query.value#/td Here is my problem: There are times when not all of the values exists, so I don't have enough of the dynamic cells in the row. EX: I have a header called Agree but the value agree is not returned from the query so it messes up the row. My question is how can I create a fake column from the query OR create an empty cell in the row if a value doesn't exist? NOTE: The values don't exist so I can test for existence if that makes sense. Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359805 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
Sorry let me try that again...what I meant was Not quite. The value returned is a COUNT of the number of each response (sorry I should have said that earlier. So, if no one answered disagree it is not returned, but in the loop I want it represented as 0. :) If I understand correctly Select response1 from [table] The resulting Query can equal 'response1' == 'Agree' or 'Disagree' or 'Neither Agree nor Disagree' or 'Strongly Agree' or 'Strongly Disagree' Then you loop over the results from the query and (I am guessing here) you want to place an 'X' in the box under the correct header? Simplest I can think of would be cfoutput query=[qname] tr cfswitch expression=#[qname].response1# cfcase value=Agree tdX/tdtd/tdtd/tdtd/tdtd/td /cfcase cfcase value=Disagree td/tdtdX/tdtd/tdtd/tdtd/td /cfcase cfcase value=Neither Agree nor Disagree td/tdtd/tdtdX/tdtd/tdtd/td /cfcase cfcase value=Strongly Agree td/tdtd/tdtd/tdtdX/tdtd/td /cfcase cfcase value=Strongly Disagree td/tdtd/tdtd/tdtd/tdtdX/td /cfcase /cfswitch /tr /cfoutput -- William Seiter I am hoping I can explain this correctly. I have a table which houses responses to a survey. The columns can have any of the following values: Agree,Disagree,Neither Agree nor Disagree,Strongly Agree,Strongly Disagree I have a table with the headers equal to all of the values above. I have a 2nd row that loops through the query and adds a cell in the row for each value: EX: td#query.value#/td Here is my problem: There are times when not all of the values exists, so I don't have enough of the dynamic cells in the row. EX: I have a header called Agree but the value agree is not returned from the query so it messes up the row. My question is how can I create a fake column from the query OR create an empty cell in the row if a value doesn't exist? NOTE: The values don't exist so I can test for existence if that makes sense. Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359807 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Creating columns that aren't included in a query result
It seems illogical that a column would not exist from the query. Could you post the query that is being run? As to your display issue... cfif structKeyExists([qName],'[colName]') td#[qName].[colName]#/td cfelse td0/td /cfif One of these per column... -- William Seiter -Original Message- From: Torrent Girl [mailto:moniqueb...@gmail.com] Sent: Wednesday, December 10, 2014 4:29 PM To: cf-talk Subject: Re: Creating columns that aren't included in a query result Russ there is only one loop. It's a very simple table with a static first row with headers and a simple query loop to add columns. So here is my header row: Agree, Disagree, Neither Agree nor Disagree, Strongly Agree, Strongly Disagree And here is my dynamic row created from the query: Agree, Disagree, Strongly Agree, Strongly Disagree So, if 'Neither Agree nor Disagree' is not returned from the query, I am short of one cell and it throws it off. I need to create an empty cell for each one that wasn't returned. why do you need the 2nd loop rather than simply outputting the value of the query column from the main loop? On Thu, Dec 11, 2014 at 12:02 AM, Torrent Girl moniqueb...@gmail.com wrote: ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359808 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
I could be wrong, but it seems like you might be making it harder than it needs to be from the query side, if you are looking for totals of responses by question. You might be able to just use a GROUP BY clause to handle it. SELECT questions.id,questions.question, count(r.disagree) as disagreed, /*Disagreed Count*/ count(r.agree) as agreed, /*Agreed Count*/ count(r.neither) as neutral, /*Neutral Count*/ count(r.strongly_agree) as strongly_agreed, /*SA Count*/ count(r.strongly_disagree) as strongly_disagreed, /*SD Count*/ (SELECT count(*) FROM responses r2 /*No Response Count*/ where question_id = questions.id AND r2.agree IS NULL AND r2.disagree IS NULL AND r2.neither IS NULL AND r2.strongly_agree IS NULL AND r2.strongly_disagree IS NULL) as no_response FROM questions INNER JOIN responses r on r.question_id = questions.id GROUP by questions.id,questions.question Note the following was tested on MySQL and Im sure theres an SQL ninja that could probably find a better way to aggregate the sub-select. HTH, Jon On Dec 10, 2014, at 7:32 PM, Torrent Girl moniqueb...@gmail.com wrote: Sorry let me try that again...what I meant was Not quite. The value returned is a COUNT of the number of each response (sorry I should have said that earlier. So, if no one answered disagree it is not returned, but in the loop I want it represented as 0. :) If I understand correctly Select response1 from [table] The resulting Query can equal 'response1' == 'Agree' or 'Disagree' or 'Neither Agree nor Disagree' or 'Strongly Agree' or 'Strongly Disagree' Then you loop over the results from the query and (I am guessing here) you want to place an 'X' in the box under the correct header? Simplest I can think of would be cfoutput query=[qname] tr cfswitch expression=#[qname].response1# cfcase value=Agree tdX/tdtd/tdtd/tdtd/tdtd/td /cfcase cfcase value=Disagree td/tdtdX/tdtd/tdtd/tdtd/td /cfcase cfcase value=Neither Agree nor Disagree td/tdtd/tdtdX/tdtd/tdtd/td /cfcase cfcase value=Strongly Agree td/tdtd/tdtd/tdtdX/tdtd/td /cfcase cfcase value=Strongly Disagree td/tdtd/tdtd/tdtd/tdtdX/td /cfcase /cfswitch /tr /cfoutput -- William Seiter I am hoping I can explain this correctly. I have a table which houses responses to a survey. The columns can have any of the following values: Agree,Disagree,Neither Agree nor Disagree,Strongly Agree,Strongly Disagree I have a table with the headers equal to all of the values above. I have a 2nd row that loops through the query and adds a cell in the row for each value: EX: td#query.value#/td Here is my problem: There are times when not all of the values exists, so I don't have enough of the dynamic cells in the row. EX: I have a header called Agree but the value agree is not returned from the query so it messes up the row. My question is how can I create a fake column from the query OR create an empty cell in the row if a value doesn't exist? NOTE: The values don't exist so I can test for existence if that makes sense. Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359809 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
disagree is not an actual column it is a value. I could be wrong, but it seems like you might be making it harder than it needs to be from the query side, if you are looking for totals of responses by question. You might be able to just use a GROUP BY clause to handle it. SELECTquestions.id,questions.question, count(r.disagree) as disagreed, /*Disagreed Count*/ count(r.agree) as agreed, /*Agreed Count*/ count(r.neither) as neutral, /*Neutral Count*/ count(r.strongly_agree) as strongly_agreed, /*SA Count*/ count(r.strongly_disagree) as strongly_disagreed, /*SD Count*/ (SELECT count(*) FROM responses r2 /*No Response Count*/ where question_id = questions.id AND r2.agree IS NULL AND r2.disagree IS NULL AND r2.neither IS NULL AND r2.strongly_agree IS NULL AND r2.strongly_disagree IS NULL) as no_response FROM questions INNER JOIN responses r on r.question_id = questions.id GROUP by questions.id,questions.question Note the following was tested on MySQL and Im sure theres an SQL ninja that could probably find a better way to aggregate the sub-select. HTH, Jon On Dec 10, 2014, at 7:32 PM, Torrent Girl moniqueb...@gmail.com wrote: Sorry let me try that again...what I meant was Not quite. The value returned is a COUNT of the number of each response (sorry I should have said that earlier. So, if no one answered disagree it is not returned, but in the loop I want it represented as 0. :) If I understand correctly Select response1 from [table] The resulting Query can equal 'response1' == 'Agree' or 'Disagree' or 'Neither Agree nor Disagree' or 'Strongly Agree' or 'Strongly Disagree' Then you loop over the results from the query and (I am guessing here) you want to place an 'X' in the box under the correct header? Simplest I can think of would be cfoutput query=[qname] tr cfswitch expression=#[qname].response1# cfcase value=Agree tdX/tdtd/tdtd/tdtd/tdtd/td /cfcase cfcase value=Disagree td/tdtdX/tdtd/tdtd/tdtd/td /cfcase cfcase value=Neither Agree nor Disagree td/tdtd/tdtdX/tdtd/tdtd/td /cfcase cfcase value=Strongly Agree td/tdtd/tdtd/tdtdX/tdtd/td /cfcase cfcase value=Strongly Disagree td/tdtd/tdtd/tdtd/tdtdX/td /cfcase /cfswitch /tr /cfoutput -- William Seiter I am hoping I can explain this correctly. I have a table which houses responses to a survey. The columns can have any of the following values: Agree,Disagree,Neither Agree nor Disagree,Strongly Agree,Strongly Disagree I have a table with the headers equal to all of the values above. I have a 2nd row that loops through the query and adds a cell in the row for each value: EX: td#query.value#/td Here is my problem: There are times when not all of the values exists, so I don't have enough of the dynamic cells in the row. EX: I have a header called Agree but the value agree is not returned from the query so it messes up the row. My question is how can I create a fake column from the query OR create an empty cell in the row if a value doesn't exist? NOTE: The values don't exist so I can test for existence if that makes sense. Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359810 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
Are you looking for a count of responses, or the actual value, though? In my test I used a bit datatype on the column so count() is all I needed for my example. If youve got a text value of YES or something else, though, you could use a sub-function or a conditional inside count to convert that to a bit value for your purposes. Either way, the end result is that you have one query, grouped by question with your response counts for each question. Then you only have to loop the query once instead of keeping track of iterations, cases and conditionals. Jon On Dec 10, 2014, at 9:15 PM, Torrent Girl moniqueb...@gmail.com wrote: disagree is not an actual column it is a value. I could be wrong, but it seems like you might be making it harder than it needs to be from the query side, if you are looking for totals of responses by question. You might be able to just use a GROUP BY clause to handle it. SELECT questions.id,questions.question, count(r.disagree) as disagreed, /*Disagreed Count*/ count(r.agree) as agreed, /*Agreed Count*/ count(r.neither) as neutral, /*Neutral Count*/ count(r.strongly_agree) as strongly_agreed, /*SA Count*/ count(r.strongly_disagree) as strongly_disagreed, /*SD Count*/ (SELECT count(*) FROM responses r2 /*No Response Count*/ where question_id = questions.id AND r2.agree IS NULL AND r2.disagree IS NULL AND r2.neither IS NULL AND r2.strongly_agree IS NULL AND r2.strongly_disagree IS NULL) as no_response FROM questions INNER JOIN responses r on r.question_id = questions.id GROUP by questions.id,questions.question Note the following was tested on MySQL and Im sure theres an SQL ninja that could probably find a better way to aggregate the sub-select. HTH, Jon On Dec 10, 2014, at 7:32 PM, Torrent Girl moniqueb...@gmail.com wrote: Sorry let me try that again...what I meant was Not quite. The value returned is a COUNT of the number of each response (sorry I should have said that earlier. So, if no one answered disagree it is not returned, but in the loop I want it represented as 0. :) If I understand correctly Select response1 from [table] The resulting Query can equal 'response1' == 'Agree' or 'Disagree' or 'Neither Agree nor Disagree' or 'Strongly Agree' or 'Strongly Disagree' Then you loop over the results from the query and (I am guessing here) you want to place an 'X' in the box under the correct header? Simplest I can think of would be cfoutput query=[qname] tr cfswitch expression=#[qname].response1# cfcase value=Agree tdX/tdtd/tdtd/tdtd/tdtd/td /cfcase cfcase value=Disagree td/tdtdX/tdtd/tdtd/tdtd/td /cfcase cfcase value=Neither Agree nor Disagree td/tdtd/tdtdX/tdtd/tdtd/td /cfcase cfcase value=Strongly Agree td/tdtd/tdtd/tdtdX/tdtd/td /cfcase cfcase value=Strongly Disagree td/tdtd/tdtd/tdtd/tdtdX/td /cfcase /cfswitch /tr /cfoutput -- William Seiter I am hoping I can explain this correctly. I have a table which houses responses to a survey. The columns can have any of the following values: Agree,Disagree,Neither Agree nor Disagree,Strongly Agree,Strongly Disagree I have a table with the headers equal to all of the values above. I have a 2nd row that loops through the query and adds a cell in the row for each value: EX: td#query.value#/td Here is my problem: There are times when not all of the values exists, so I don't have enough of the dynamic cells in the row. EX: I have a header called Agree but the value agree is not returned from the query so it messes up the row. My question is how can I create a fake column from the query OR create an empty cell in the row if a value doesn't exist? NOTE: The values don't exist so I can test for existence if that makes sense. Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359811 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
can you show us the actualy query, as a query shouldn't return nothing, it should return 0 if the count is 0 The only instance where you should get nothing is if there are no rows of data returned On Thu, Dec 11, 2014 at 12:23 AM, Torrent Girl moniqueb...@gmail.com wrote: Not quite. The value returned in a COUNT of the number of each response (sorry I should have said that earlier. So, it no one answered disagree it is not returned, but in the loop I want it represented as 0. If I understand correctly Select response1 from [table] The resulting Query can equal 'response1' == 'Agree' or 'Disagree' or 'Neither Agree nor Disagree' or 'Strongly Agree' or 'Strongly Disagree' Then you loop over the results from the query and (I am guessing here) you want to place an 'X' in the box under the correct header? Simplest I can think of would be cfoutput query=[qname] tr cfswitch expression=#[qname].response1# cfcase value=Agree tdX/tdtd/tdtd/tdtd/tdtd/td /cfcase cfcase value=Disagree td/tdtdX/tdtd/tdtd/tdtd/td /cfcase cfcase value=Neither Agree nor Disagree td/tdtd/tdtdX/tdtd/tdtd/td /cfcase cfcase value=Strongly Agree td/tdtd/tdtd/tdtdX/tdtd/td /cfcase cfcase value=Strongly Disagree td/tdtd/tdtd/tdtd/tdtdX/td /cfcase /cfswitch /tr /cfoutput -- William Seiter I am hoping I can explain this correctly. I have a table which houses responses to a survey. The columns can have any of the following values: Agree,Disagree,Neither Agree nor Disagree,Strongly Agree,Strongly Disagree I have a table with the headers equal to all of the values above. I have a 2nd row that loops through the query and adds a cell in the row for each value: EX: td#query.value#/td Here is my problem: There are times when not all of the values exists, so I don't have enough of the dynamic cells in the row. EX: I have a header called Agree but the value agree is not returned from the query so it messes up the row. My question is how can I create a fake column from the query OR create an empty cell in the row if a value doesn't exist? NOTE: The values don't exist so I can test for existence if that makes sense. Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359812 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
Your task shouldn't be to handle values that were not returned from the query. You need to think of a way to make sure they are returned. You should be able to accomplish that by using case statements in your query. Syntax may vary depending on your database, but should be something like this (assume you db field is called response): Sum(case response = 'agree' then 1 else 0 end) as agree, Sum(case response = 'disagree' then 1 else 0 end) as disagree, Sum(case response = 'strongly agree' then 1 else 0) as strongly_agree And so on for each possible value. Your resultset will then contain a total for each possible value, and a 0 if a value wasn't found at all. On Wednesday, December 10, 2014, Torrent Girl moniqueb...@gmail.com wrote: Russ there is only one loop. It's a very simple table with a static first row with headers and a simple query loop to add columns. So here is my header row: Agree, Disagree, Neither Agree nor Disagree, Strongly Agree, Strongly Disagree And here is my dynamic row created from the query: Agree, Disagree, Strongly Agree, Strongly Disagree So, if 'Neither Agree nor Disagree' is not returned from the query, I am short of one cell and it throws it off. I need to create an empty cell for each one that wasn't returned. why do you need the 2nd loop rather than simply outputting the value of the query column from the main loop? On Thu, Dec 11, 2014 at 12:02 AM, Torrent Girl moniqueb...@gmail.com javascript:; wrote: ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359813 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
Jon can I do this even though disagree, etc. are columns but actual values? I could be wrong, but it seems like you might be making it harder than it needs to be from the query side, if you are looking for totals of responses by question. You might be able to just use a GROUP BY clause to handle it. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359814 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
can you show us the actualy query, as a query shouldn't return nothing, it should return 0 if the count is 0 The only instance where you should get nothing is if there are no rows of data returned On Thu, Dec 11, 2014 at 12:23 AM, Torrent Girl moniqueb...@gmail.com wrote: Here is my query: select overAllQuality, COUNT(overAllQuality) as total FROM table_name where dateAdded BETWEEN '#startDate#' AND '#endDate#' group by overAllQuality ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359815 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
I am looking for how many people answered disagree etc. BUT, if no one answered disagree I want to be able to put a 0 in the disagree column. The problem is if no one answered disgree I have to fake it which is where I am stuck. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359816 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
If you implement the query syntax I sent earlier you will have a 0 in the disagree column. On Wednesday, December 10, 2014, Torrent Girl moniqueb...@gmail.com wrote: I am looking for how many people answered disagree etc. BUT, if no one answered disagree I want to be able to put a 0 in the disagree column. The problem is if no one answered disgree I have to fake it which is where I am stuck. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359817 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Creating columns that aren't included in a query result
Yes, using Steves example query as a foundation. I assumed you were using a relational model with separate questions/answer tables but that is not the case. You wont need joins or conversions. Steves Sum(case response = 'agree' then 1 else 0 end) as agree query is what you are looking for. Jon On Dec 10, 2014, at 9:39 PM, Torrent Girl moniqueb...@gmail.com wrote: Jon can I do this even though disagree, etc. are columns but actual values? I could be wrong, but it seems like you might be making it harder than it needs to be from the query side, if you are looking for totals of responses by question. You might be able to just use a GROUP BY clause to handle it. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:359818 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
