[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

problems with cfoutput and cfif statements

I have hardcoded (bad-i know) a table for a May events calendar.  Users can search for events by choosing one option at a time.  If the search phrase matches I want to change the color of the cell and output the day number for the event date on the calendar.  If the search phrase does not match I want to just output the day number on the calendar.  I have a query to pull the information from the database and a cfif statement to check if the calendar date matches the start date of the event.  My problem is that if there is more than one event match for the given day the output on the calendar is equal to the number of matches.  For example, if 2 events match there are 2 outputs on the calendar.  To try to fix this I created a cfif statement to check if the recordcount is greater than 0.  This works within the first cfif statement but not in the cfelse part.  I have no idea why it works in one place and not the other.  I assume I have something wrong, but I am stumped!  My code is pasted below.  Also, you can see what is happening here http://r.assoclink.com/cnorris/carolyn/calendar/ by doing a search for "Baltimore County"  .  I have another small problem with this same page.  Do I need to submit a new question for it?

Thanks in advance for any help during this holiday weekend!

<cfset current_date = #DateFormat(cal_date, 'mm/dd/yy')#>                          
<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT DISTINCT startdate
FROM Table1
WHERE event_county = '#form.searchcounty#'
ORDER by startdate
</cfquery>              
<cfoutput query="qry_getdate" group="startdate">
<cfset new_startdate = #Dateformat(qry_getdate.startdate, 'mm/dd/yy')#>
<cfif cal_date EQ new_startdate>
<table border="0" width="100%" height="5">
 <tr>
  <td bgcolor="##99CCFF" align="right" valign="top" height="100%">
    <cfoutput><cfif qry_getdate.recordcount GT 0><a href="dsp_getcountysearch-ORIG.cfm?var=#form.searchcounty#&date=#cal_date#">#DatePart('d', current_date)#</a></cfif></cfoutput>
</td>
 </tr>
</table>
<cfelse>
<table border="0" width="1" height="1">
 <tr>
  <td align="right" valign="top"><cfoutput><cfif qry_getdate.recordcount GT 0><a href="dsp_getcountysearch-ORIG.cfm?var=#form.searchcounty#&date=#cal_date#">#DatePart('d', current_date)#</a></cfif></cfoutput></td>
 </tr>
</table>
</cfif>
</cfoutput>
0
ccnorris
Asked:
ccnorris
  • 11
  • 9
1 Solution
 
RCorfmanCommented:
The query qry_getdate recordcount will never change once you run it. If you are in the middle of a cfoutput loop for that query, the recordcount will ALWAYS be greater than zero, otherwise the cfoutput loop wouln't ever be entered...

When you have a
<cfoutput query="qry">
.....
</cfoutput>

The code inside the cfoutput block is run once for each record, so if recordcount were zero, the code would never run, if it were > 0 , then it would run, and the check for qry.recordcount GT 0 would always be true.  Having a <cfelse> would never be true... the entire check is meaningless.
0
 
ccnorrisAuthor Commented:
OK.  Makes sense.  Thank you for your help so far.

Then, how I can I make the date output only once instead of outputting the number of records in the database?

I changed the cfif statement back to what I had originally. I just took out the cfoutput group attribute, cfif recordcount check and second output tags.  I'll paste it below.  If you look at the URL I gave in my question can see on Tuesday the 3rd there are two blue highlighted 3s and 7 that are not.  There are 2 matches on the 3rd and 7 that are not.   I only want the entries to show up once.  So, basically what I want is if there is a match show the date highlighted once else just show the date.  It seems to me that this shouldn't be that hard, but I'm obviously missing something.

<cfoutput query="qry_getdate">
<cfset new_startdate = #Dateformat(qry_getdate.startdate, 'mm/dd/yy')#>
<cfif cal_date EQ new_startdate>
<table border="0" width="100%" height="5">
 <tr>
  <td bgcolor="##99CCFF" align="right" valign="top" height="100%"><a href="dsp_getcountysearch-ORIG.cfm?var=#form.searchcounty#&date=#cal_date#">#DatePart('d', current_date)#</a>
</td>
 </tr>
</table>
<cfelse>
<table border="0" width="1" height="1">
 <tr>
  <td align="right" valign="top"><a href="dsp_getcountysearch-ORIG.cfm?var=#form.searchcounty#&date=#cal_date#">#DatePart('d', current_date)#</a></td>
 </tr>
</table>
</cfif>
</cfoutput>
0
 
RCorfmanCommented:
I would actually revamp the logic some.  I would output the Days in a loop, then do a query of queries for each day to retrieve any events on that day... If I'm understanding correctly.

This will also help with the problem of how to get the recurring events that I've seen questions on in other queries.  But for purposes of this question, I won't cover that part, I'll try to get it in the other query. For this question, I'll limit the discussion to events that just happen on the given date.

Use a loop that loops through each day, one day at a time.
Use an original Database query that retrieves all the events you want displayed.

Then, inside the loop, use a query of queries (QofQ) to find any events for the current day you are outputting.
The QofQ is just another <cfquery> statement, but you don't specify a datasource, and you indicate the attribute of dbtype="query". Then, for the table, you use the prior query name.

I'm going to put in pieces of the code... (I have NOT tested this code, I'm typing it in right here... I'm usually pretty good at it, but there could well be typos)

<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT * FROM Table1
WHERE event_county = '#form.searchcounty#'
</cfquery>           <!--- note that I took out the orderby because it doesn't matter anymore since we aren't outputting this query at all, just querying it --->
<cfset cur_year=2006>
<cfset cur_month=5>
<!--- for the above, you would loop through the months and set them in a 'larger, driving' loop, but since you mentioned may, I'm limiting this to one loop for may... this is notional to nudge you in a direction that I think is correct, but there are always a lot of ways to do stuff...--->
<table><tr><td colspan=7>MAY</td></tr>
<tr>
<td>Sun</td><td>Mon></td><td>Tues</td><td>Wed</td><td>Thur</td><td>Fri</td><td>Sat</td>
</tr>
<!--- now, we have to line up with the proper day in our table... so if the 1st is a Wednesday, we skip ahead to that. This is generic code for that and should work with any month... There may be better ways, I'm making this up--->
<cfset cur_day_of_Week = dayOfWeek(CreateDate(cur_year,cur_month,1))>
<cfif cur_day_of_week GT 1><!--- we need to accomodate 'blank days at start of month --->
 <tr><td colspan="<cfoutput>#cur_day_of_Week-1#</cfoutput>">&nbsp;</td>
</cfloop>
<cfloop index="cur_day" from="1" to="#DaysInMonth(CreateDate(cur_year,cur_month,1))#">
<cfset cur_date = createDate(cur_year,cur_month,cur_day)>
<cfset cur_day_of_week = dayOfWeek(cur_date)>
<cfif cur_day_of_week EQ 1><!--- start a new week in the table---><tr></cfif>
<!--- now for the meat, query any events on this day for output --->
<cfquery name="qryDaysEvents" dbtype="query">
 select * from qry_getDate
 where qry_getDate.startdate=<cfqueryparam value="#cur_date#" cfsqltype="CF_SQL_DATE">
</cfquery>
<!--- output the td tag with the bgcolor as appropriate if there are events on the day --->
<cfif qry_DaysEvents.RecordCount GTE 1><td bgcolor="##99CCFF" valign="top">
<cfelse><td valign="top"></cfif>
<!--- output the Day Number---><cfoutput>#Cur_Day#</cfoutput>
<!--- now, output the events... I don't know if you have a very short description for the events, But I would recommend that... say Fair, Egg Hunt, whatever, a short description for the calendar...--->
<cfoutput query="qryDaysEvents">
<br><a href="link to event specifics">#qryDaysEvents.ShortEventDescription#</a>
<!--- if you don't have a description, you could output and href like you had for the day's events link... I was just thinking that listing the actual events would be neat, sort of like the calendar my wife keeps of our events with very, very short description of the event on the calendar --->
</cfoutput></td>
<cfif cur_day_of_week EQ 7><!--- end the week in the table---><tr></cfif>
</cfloop>
</table>
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
ccnorrisAuthor Commented:
thanks.  i'll try what you have out this evening.

i don't want to output anything for the events except the day number and/or color the cell.  the reason is because the calendar i have is very small.  if there are events for a day i just want to make the day number a hyperlink that will lead to a page that lists the events for that day.  this is why i was trying to do the cfif statement with the recordcount.

will what you suggested work for outputting the day number only once?
0
 
RCorfmanCommented:
Yes.  Instead of looping through the QofQ though, you can just check the recordcount on the QofQ.
You can change it to output the day-number without the href tag in the case of no records, and output the tag with the href that you had before, if there is one or more events.  Also, the QofQ would need to be modified to accomodate the recurring events.

Basically, replace this section:
<!--- output the td tag with the bgcolor as appropriate if there are events on the day --->
<cfif qry_DaysEvents.RecordCount GTE 1><td bgcolor="##99CCFF" valign="top">
<cfelse><td valign="top"></cfif>
<!--- output the Day Number---><cfoutput>#Cur_Day#</cfoutput>
<!--- now, output the events... I don't know if you have a very short description for the events, But I would recommend that... say Fair, Egg Hunt, whatever, a short description for the calendar...--->
<cfoutput query="qryDaysEvents">
<br><a href="link to event specifics">#qryDaysEvents.ShortEventDescription#</a>
<!--- if you don't have a description, you could output and href like you had for the day's events link... I was just thinking that listing the actual events would be neat, sort of like the calendar my wife keeps of our events with very, very short description of the event on the calendar --->
</cfoutput></td>


With this:
<!--- output the td tag with the bgcolor as appropriate if there are events on the day --->
<cfoutput>
<cfif qry_DaysEvents.RecordCount GTE 1><td bgcolor="##99CCFF" valign="top">
<a href="dsp_getcountysearch-ORIG.cfm?var=#form.searchcounty#&date=#dateFormat(cur_date,"mm/dd/yy")#">#Cur_Day#</a></td>
<cfelse><td valign="top">#Cur_Day#</td>
</cfif>
</cfoutput>
0
 
ccnorrisAuthor Commented:
i'm getting an error on the line  <tr><td colspan="<cfoutput>#cur_day_of_Week-1#</cfoutput>">&nbsp;</td>.  the error is "Just in time compilation error.  
Invalid parser construct found on line 17 at position 45. ColdFusion was looking at the following text:-Invalid expression format. The usual cause is an error in the expression structure."
0
 
RCorfmanCommented:
Yeah, as I said, I typed it in without having access to my coldfusion server at the time... I also modified some of it as I went and didn't pick it all up.
Near the top is this:
<cfif cur_day_of_week GT 1><!--- we need to accomodate 'blank days at start of month --->
 <tr><td colspan="<cfoutput>#cur_day_of_Week-1#</cfoutput>">&nbsp;</td>
</cfloop>

Change the </cfloop> to a </cfif>

Also, near the bottom, I had
<cfif qry_DaysEvents.RecordCount GTE 1>
should be <cfif qryDaysEvents.RecordCount GTE 1>

lastly, in the days header, I had <td>Mon></td>
Should be <td>Mon</td>

I normally try to run examples before I post them.
I had to change the queries and datasource on my server, but I had this...

0
 
RCorfmanCommented:
Normally I try to check my code before I post it.  I have since done that as I'm back to where I can get to my server. In my case, this server is MX7, I don't think there is version specific code requiring 7, and I think this will work on most version of Coldfusion, but if you are on a specific lower version, that would be good to note.
Here is the modified code that has the identified changes. This worked and produced a calendar with links and correct background colors.

<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT * FROM Table1
WHERE event_county = '#form.searchcounty#'
</cfquery>           <!--- note that I took out the orderby because it doesn't matter anymore since we aren't outputting this query at all, just querying it --->
<cfset cur_year=2006>
<cfset cur_month=5>
<!--- for the above, you would loop through the months and set them in a 'larger, driving' loop, but since you mentioned may, I'm limiting this to one loop for may... this is notional to nudge you in a direction that I think is correct, but there are always a lot of ways to do stuff...--->
<table><tr><td colspan=7>MAY</td></tr>
<tr>
<td>Sun</td><td>Mon</td><td>Tues</td><td>Wed</td><td>Thur</td><td>Fri</td><td>Sat</td>
</tr>
<!--- now, we have to line up with the proper day in our table... so if the 1st is a Wednesday, we skip ahead to that. This is generic code for that and should work with any month... There may be better ways, I'm making this up--->
<cfset cur_day_of_Week = dayOfWeek(CreateDate(cur_year,cur_month,1))>
<cfif cur_day_of_week GT 1><!--- we need to accomodate 'blank days at start of month --->
 <tr><td colspan="<cfoutput>#cur_day_of_Week-1#</cfoutput>">&nbsp;</td>
</cfif>
<cfloop index="cur_day" from="1" to="#DaysInMonth(CreateDate(cur_year,cur_month,1))#">
<cfset cur_date = createDate(cur_year,cur_month,cur_day)>
<cfset cur_day_of_week = dayOfWeek(cur_date)>
<cfif cur_day_of_week EQ 1><!--- start a new week in the table---><tr></cfif>
<!--- now for the meat, query any events on this day for output --->
<cfquery name="qryDaysEvents" dbtype="query">
 select * from qry_getDate
 where qry_getDate.startdate=<cfqueryparam value="#cur_date#" cfsqltype="CF_SQL_DATE">
</cfquery>
<!--- output the td tag with the bgcolor as appropriate if there are events on the day --->
<cfoutput>
<cfif qryDaysEvents.RecordCount GTE 1><td bgcolor="##99CCFF" valign="top">
<a href="dsp_getcountysearch-ORIG.cfm?var=#form.searchcounty#&date=#dateFormat(cur_date,"mm/dd/yy")#">#Cur_Day#</a></td>
<cfelse><td valign="top">#Cur_Day#</td>
</cfif>
</cfoutput>
<cfif cur_day_of_week EQ 7><!--- end the week in the table---><tr></cfif>
</cfloop>
</table>
0
 
ccnorrisAuthor Commented:
unfortunately i'm still getting an error.  now, i'm getting the error "CFQUERYPARAM data conversion error.  There is a data conversion error for CFQUERYPARAM #1."  This error is coming from the query of the query:

<cfquery name="qryDaysEvents" dbtype="query">
 select * from qry_getDate
 where qry_getDate.startdate=<cfqueryparam value="#cur_date#" cfsqltype="CF_SQL_DATE">
</cfquery>

i see it has sql in "cfsqltype="CF_SQL_DATE".  is this for an sql database?  i am using an access database.  

for testing i am using a server that is running ColdFusion 5.
0
 
RCorfmanCommented:
Ok, I haven't used 5 ... I went from 4 to 6 and it has been a long time.
To avoid the computation error, let's do this...
Change this:
<cfif cur_day_of_week GT 1><!--- we need to accomodate 'blank days at start of month --->
 <tr><td colspan="<cfoutput>#cur_day_of_Week-1#</cfoutput>">&nbsp;</td>
</cfif>
to:
<cfif cur_day_of_week GT 1><!--- we need to accomodate 'blank days at start of month --->
 <cfset Cur_day_of_Week = cur_day_of_week-1>
 <tr><cfoutput><td colspan="#cur_day_of_Week#"></cfoutput>&nbsp;</td>
</cfif>

The cfsqltype does work in all databases and QofQ, but I don't know if that is the case in CF5... for sure in 6 and 7.  That may not be it though.  In access what is the data type of startdate? Is it a date data type?
0
 
ccnorrisAuthor Commented:
I'm still getting the data conversion error.  I'm not sure if it has to do with the version of ColdFusion on the server or not.  I've never run into any problems between versions before.

The startdate is a Date/time field in Access.
0
 
RCorfmanCommented:
OK, it may be that QofQ in cf5 doesn't like processing dates. I don't have a CF5 server to try it on.  There are definitely differences in features available between the different CF Server versions. There are things that do work on later versions that don't work on earlier. I'm not familiar enough with the earlier versions to say exactly what is going to snag us up, except for things that just aren't there in the earlier version, I can usuall see that coming in advance.

The easiest way around that will be to use character strings once we get to the QofQ.  If you can bear with this...

In your original query, in addition to startdate, add a new column.  This is in access sql.
format(startdate,'YYYYMMDD') as txtStartDate

This will retrieve a text column that is pre-formated in a format that we can easily query.

Now, in the QofQ, try to change it to this:
<cfquery name="qryDaysEvents" dbtype="query">
 select * from qry_getDate
 where qry_getDate.txtstartdate="#dateFormat(cur_date,"YYYYMMDD")#"
</cfquery>
0
 
ccnorrisAuthor Commented:
i'm confused.  In the first query do you mean to add the new column like

<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT *
FROM Table1
WHERE event_county = '#form.searchcounty#'
format(startdate,'YYYYMMDD') as txtStartDate
</cfquery>

Sorry, I don't understand.
0
 
RCorfmanCommented:
yes, in the first query we want to add a new column.  The exact syntax is this:
<cfquery name="qry_getdate" datasource="cnorris_calendar">
SELECT Table1.*,format(startdate,'YYYYMMDD') as txtStartDate
FROM Table1
WHERE event_county = '#form.searchcounty#'
</cfquery>

This way we have a text column to use in the QofQ and won't have to worry about any date conversions there. We are forcing a new column that is in a text format that represents the date. Then in the QofQ, I show a change in that to just search for the entries of that one date.
0
 
ccnorrisAuthor Commented:
Yeah, it's working!  Thank you so much!!  You have no idea how much help you have been.  I also appreciate your patience with my ColdFusion skills and understanding.

There is one thing that is not working right with the link to the page with the list of matching events.  Do I need to enter a new question for this?
0
 
RCorfmanCommented:
In the example that we just put together, I had it so that there is only an href IF there is a matching event, instead of just changing the background color. If that is what the problem is, we can keep going on it. Also, if this technique is working, we can continue on with the query for the Recurring events, just post again over on that question if you want to pursue that one.  It will just be a change to the QofQ that we started in this question.
0
 
RCorfmanCommented:
If it was just the hyperlink for every day, then use this as the if statement block...
Change:
<cfif qryDaysEvents.RecordCount GTE 1><td bgcolor="##99CCFF" valign="top">
<a href="dsp_getcountysearch-ORIG.cfm?var=#form.searchcounty#&date=#dateFormat(cur_date,"mm/dd/yy")#">#Cur_Day#</a></td>
<cfelse><td valign="top">#Cur_Day#</td>
</cfif>
To:
<cfif qryDaysEvents.RecordCount GTE 1><td bgcolor="##99CCFF" valign="top">
<cfelse><td></cfif>
<a href="dsp_getcountysearch-ORIG.cfm?var=#form.searchcounty#&date=#dateFormat(cur_date,"mm/dd/yy")#">#Cur_Day#</a></td>
0
 
ccnorrisAuthor Commented:
There href link from the date to the events page is working.  What is happening is that instead of only listing the events that match the search phrase and date is is listing all the events for the search phrase.  It seems to me that maybe the date variable in the href is not working correctly.
0
 
RCorfmanCommented:
Yeah, if the problem is on the detailed listing page, then, if you have the points to spare (I don't know how you have to fund points...), then it really should be a different question as it is a completely different page and problem.
0
 
ccnorrisAuthor Commented:
Understandable.  It doesn't matter to me.  You definitely deserve the points for all your help!  :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now