Copy link to clipboard
Copied
Hello
I can't get this where statement to work. I have it now so it is not throwing any errors, but it also isn't doing what it is supposed to.
I am trying to match records date in my table with the actual calendar date, if there is a match, my cfif will take that match, and make it a link in the proper date. (it is a small calendar with the month, and the days of week with dates, each date will be normal, accept if there is a record for that date, it makes a link.
This is my code, like I said, It doesn't throw an error, but it is not working either.
My Query:
<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT events.eventDate, events.ID AS ID
FROM events
WHERE eventDate >= #CreateODBCDate("07/12/2005")# AND eventDate = #Days#
</cfquery>
This is the cfif statement:
<cfoutput query="CaleventRec">
<cfif Days EQ '#eventdate#'>
<a href = "detail.cfm?id=#ID#">#Days#</a>
</cfif>
</cfoutput>
this is all the code together:
<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT events.eventDate, events.ID AS ID
FROM events
WHERE eventDate >= #CreateODBCDate("07/12/2005")# AND eventDate = #Days#
</cfquery>
<!--- Set the ThisDay variable to 0. This value will remain 0 until the day of the week on which the first day of the month falls on is reached. --->
<cfset ThisDay = 0>
<!--- Loop through until the number of days in the month is reached. --->
<cfloop condition = "ThisDay LTE Days">
<tr>
<!--- Loop though each day of the week. --->
<cfloop from = "1" to = "7" index = "LoopDay">
<!--- This turns each day into a hyperlink if it is a current or future date --->
<cfoutput query="CaleventRec">
<cfif Days EQ '#eventdate#'>
<a href = "detail.cfm?id=#ID#">#Days#</a>
</cfif>
</cfoutput>
<!---
If ThisDay is still 0, check to see if the current day of the week in the loop matches the day of the week for the first day of the month.
If the values match, set ThisDay to 1.
Otherwise, the value will remain 0 until the correct day of the week is found.
--->
<cfif ThisDay IS 0>
<cfif DayOfWeek(ThisMonthYear) IS LoopDay>
<cfset ThisDay = 1>
</cfif>
</cfif>
<!---
If the ThisDay value is still 0, or is greater than the number of days in the month, display nothing in the column. Otherwise, dispplay
the day of the mnth and increment the value.
--->
<cfif (ThisDay IS NOT 0) AND (ThisDay LTE Days)>
<cfoutput>
<!--- I choose to highlight the current day of the year using an IF-ELSE. --->
<cfif (#ThisDay# EQ #currentday#) AND (#month# EQ #startmonth#) AND (#year# EQ #startyear#)>
<td align = "center" bgcolor="##FFFF99">
<cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
<font class = "calendartoday">#ThisDay#</font>
</td>
<cfelse>
<td align = "center">
<cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
<font class = "calendar">#ThisDay#</font>
</td>
</cfif>
</cfoutput>
<cfset ThisDay = ThisDay + 1>
<cfelse>
<td></td>
</cfif>
</cfloop>
</tr>
</cfloop>
I know my where statement is incorrect, I am also not sure if my cfif statement is correct either. Can anyone help me figure this out?
thank you!
CFmonger
Start by dumping the query result and see if you get what you expect to get. If not, look at the value of your days variable.
By the way, this
AND eventDate = #Days#
makes this
<cfif Days EQ '#eventdate#'>
redundant.
Copy link to clipboard
Copied
Start by dumping the query result and see if you get what you expect to get. If not, look at the value of your days variable.
By the way, this
AND eventDate = #Days#
makes this
<cfif Days EQ '#eventdate#'>
redundant.
Copy link to clipboard
Copied
Ok, I dumped the query and changed the way I coded it a little and now I get [empty string] as a result of my dump of my variable 'eventDate'. So my where statement is not correct. What do I need to change this where statement to so it will match records and use an if statement to make it work?
I didn't mean to click on the button to say this was answered.... accident. sorry
Copy link to clipboard
Copied
That's too much code to read for me but one thing that I noticed is
that Days is a number and eventDate is a date and you're comparing
them.
Mack
Copy link to clipboard
Copied
That is what I am trying to do, compair the date in eventDate to the number date in the calendar, how to I just compair the 2 as numbers? It also need to make sure it is in the same month that the records appear in, if there are 5 records for june, they link in the 5 dates in the month, in july same thing, as you cycle through the months, if there are records for that month, they appear as links.Is it possible to do this?
Copy link to clipboard
Copied
You probably don't mean to say this:
eventDate >= #CreateODBCDate("07/12/2005")# AND eventDate = #Days#
Undoubtedly you intended to use a different variable in the second case.
Copy link to clipboard
Copied
what code do I write to make this work? That is my question.In my other post it is currently what I am using, I have to not only sort the records for the month, but sort through the days and match them to a day of the week if they fall in the month. How do I make this happen? It is a little over my head and really would like to understand it.
Copy link to clipboard
Copied
I do not clearly visualize what you are trying to do here, and I daresay no one else does either. The comments are simply "running commentary," and the variable names really don't imply anything. So, I suggest that the best thing to do here is ... start over.
I do not mean this response to be flippant: sometimes you just get a first-draft piece of code "all bolluxed up" when you go whacking at it "trying to get the damn thing to work." Frankly, this snippet of code looks pretty well whacked-out by now. So you need to step well away from the computer and get a clear mental image of what you want to do here. Throw away the code that you have. Clear you head and try again.
Copy link to clipboard
Copied
All the rest of the code works. The only part of this that doesn't work is the trying to make a link part.
Let me show you where I am, and what I did, then maybe you will see I am closer to the solution... I think. I made this calendar so you can cycle through the months. So if you are looking at June, you see Sun - Sat and the dates for each day of the week in the month of june. You can then choose to click the next button that will bring up the month of july, and you can keep on going as long as you want.
That is one function and it working as of right now. This Calendar also shows what day of the week we are on. If it is June 16th then June 16 is yellow. This also works.NOw, what I need to do it to have my query, not only filter out the records for the month, (if we are in June, then the Query separates out the records for June, the date in my DB is set to mm/dd/yyyy) This query also needs to match it to a number, the days of the month. So, if there is a record on the 25th of June, then the 25th of June will be a link to another page using it's ID in the address line. If you go to July and there are lets say 3 records for that month, then all 3 dates will become a link to the same other page using it's ID in the url.
This is what I have now. I tied the Query into the next / prev function so it filters the month, so far it seems to work, I can do a cfdump up to the point of my cfif statement and get the proper number from the date, my cfif is not firing so If I do a dump after it, it doesn't dump anyhting.
I will post JUST the query code, then post the whole calander code so you can see it all together.
The Query / link code:
<!--- This is part of the next / prev nav to cycle through the months --->
<cfset NextMonthYear = DateAdd('m', 1, ThisMonthYear)>
<cfset NextMonth = DatePart('m', NextMonthYear)>
<!--- My Query --->
<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT events.eventDate, events.ID AS ID
FROM events
WHERE eventDate Between #NextMonthYear# and #NextMonth#
</cfquery>
<!--- my output / cfif stement --->
<cfoutput query="CaleventRec">
<cfset comparison = DateCompare('#dateFormat(eventDate, 'dd')#', '#Days#', 'd')>
<!--- When I do a cfdump here, I get the number of the first record with a day date in the DB for the month of june
<cfdump var="#dateFormat(eventDate, 'dd')#">
<cfabort>--->
<cfif #Days# EQ ('#dateFormat(eventDate, 'dd')#')>
<!--- When I do a dump here, I do not get anyhting, no numbers it doesn't fire
<cfdump var="#eventDate#">
<cfabort>--->
<a href = "detail.cfm?id=#ID#">#Days#</a>
</cfif>
</cfoutput>
ok, now that is the part to stream in the DB info into the calander.
Here is all the code, like I said, it all works, just not making each day a link that has a record in the DB.
<!--- Declaration of the variables --->
<cfparam name = "month" default = "#DatePart('m', Now())#">
<cfparam name = "year" default = "#DatePart('yyyy', Now())#">
<cfparam name = "currentday" default = "#DatePart('d', Now())#">
<cfparam name = "startmonth" default = "#DatePart('m', Now())#">
<cfparam name = "startyear" default = "#DatePart('yyyy', Now())#">
<!--- Set a requested (or current) month/year date and determine the number of days in the month. --->
<cfset ThisMonthYear = CreateDate(year, month, '1')>
<cfset Days = DaysInMonth(ThisMonthYear)>
<!--- Set the values for the previous and next months for the back/next links.--->
<cfset LastMonthYear = DateAdd('m', -1, ThisMonthYear)>
<cfset LastMonth = DatePart('m', LastMonthYear)>
<cfset LastYear = DatePart('yyyy', LastMonthYear)>
<cfset NextMonthYear = DateAdd('m', 1, ThisMonthYear)>
<cfset NextMonth = DatePart('m', NextMonthYear)>
<cfset NextYear = DatePart('yyyy', NextMonthYear)>
<cfset PreviousDay = DateAdd('d', -1, ThisMonthYear)>
<cfset CurrentYear = DatePart('yyyy', Now())>
<table border="0" width="100%" bgcolor ="#ffffff">
<tr>
<td align = "center" valign="top">
<table border="0" width="100%" height="100%">
<tr>
<th align="center" colspan="7" bgcolor="#2b4e6e">
<cfoutput>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="15%" align="left" valign="middle">
<cfif (LastYear lt CurrentYear) OR (LastYear lte CurrentYear AND LastMonth lt startmonth)>
<cfelse>
<a href ="index.cfm?month=#LastMonth#&year=#LastYear#" class="calNav">Prev</a></cfif></td>
<td width="72%" align="center" valign="middle"><FONT SIZE="3" face="Arial, Helvetica, sans-serif" color="##ffffff">#MonthAsString(month)# #year#</FONT></td>
<td width="13%" align="right" valign="middle">
<cfif (NextYear lt CurrentYear) OR (NextYear lte CurrentYear AND NextMonth lt startmonth)>
<cfelse>
<a href = "index.cfm?month=#NextMonth#&year=#NextYear#" class="calNav">Next</a> </cfif></td>
</tr>
</table></cfoutput></th>
</tr>
<tr>
<td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Sun</FONT></td>
<td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Mon</FONT></td>
<td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Tue</FONT></td>
<td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Wed</FONT></td>
<td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Thu</FONT></td>
<td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Fri</FONT></td>
<td><FONT SIZE="2" face="Arial, Helvetica, sans-serif" color="#333366">Sat</FONT></td>
</tr>
<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT events.eventDate, events.ID AS ID
FROM events
WHERE eventDate Between #NextMonthYear# and #NextMonth#
</cfquery>
<!--- Set the ThisDay variable to 0. This value will remain 0 until the day of the week on which the first day of the month falls on is reached. --->
<cfset ThisDay = 0>
<!--- Loop through until the number of days in the month is reached. --->
<cfloop condition = "ThisDay LTE Days">
<tr>
<!--- Loop though each day of the week. --->
<cfloop from = "1" to = "7" index = "LoopDay">
<!--- This turns each day into a hyperlink if it is a current or future date --->
<cfoutput query="CaleventRec">
<cfset comparison = DateCompare('#dateFormat(eventDate, 'dd')#', '#Days#', 'd')>
<cfdump var="#dateFormat(eventDate, 'dd')#">
<cfabort>
<cfif #Days# EQ ('#dateFormat(eventDate, 'dd')#')>
<cfdump var="#eventDate#">
<cfabort>
<a href = "detail.cfm?id=#ID#">#Days#</a>
</cfif>
</cfoutput>
<!---
If ThisDay is still 0, check to see if the current day of the week in the loop matches the day of the week for the first day of the month.
If the values match, set ThisDay to 1.
Otherwise, the value will remain 0 until the correct day of the week is found.
--->
<cfif ThisDay IS 0>
<cfif DayOfWeek(ThisMonthYear) IS LoopDay>
<cfset ThisDay = 1>
</cfif>
</cfif>
<!---
If the ThisDay value is still 0, or is greater than the number of days in the month, display nothing in the column. Otherwise, dispplay
the day of the mnth and increment the value.
--->
<cfif (ThisDay IS NOT 0) AND (ThisDay LTE Days)>
<cfoutput>
<!--- I choose to highlight the current day of the year using an IF-ELSE. --->
<cfif (#ThisDay# EQ #currentday#) AND (#month# EQ #startmonth#) AND (#year# EQ #startyear#)>
<td align = "center" bgcolor="##FFFF99">
<cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
<font class = "calendartoday">#ThisDay#</font>
</td>
<cfelse>
<td align = "center">
<cfset dayview = #dateformat(createdate(#year#, #month#, #thisday#), "mm/dd/yyyy")#>
<font class = "calendar">#ThisDay#</font>
</td>
</cfif>
</cfoutput>
<cfset ThisDay = ThisDay + 1>
<cfelse>
<td></td>
</cfif>
</cfloop>
</tr>
</cfloop>
</table>
</td>
</tr>
</table>
It is kind of complicated and this is why I need help. Maybe I am wrong in the variables I am pulling, but like I say in my comments, so far I pulled the day part of the month I am in out of the records up until my cfif stement.Can this be done with what I have done so far? No need to rewrite the whole calander, just the part with the query if I miss what you are saying.
Copy link to clipboard
Copied
I'd approach the problem this way:
WHERE YEAR(EventDate) = #WhatYear#
AND MONTH(EventDate) = #WhatMonth#
... or if you prefer, something like YEAR(EventDate) = YEAR(#date_of_interest#), etc.
ORDER BY EventDate ASC
The appeal, to me, of this strategy is that I understand it. And the person who comes after you will too. Notice the "separation of concerns" here: I didn't mix the process of building the calendar with the process of determining what should go in it. I like to design code that way.
Copy link to clipboard
Copied
I understand what you are saying:
<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT events.eventDate, events.ID AS ID
FROM events
WHERE YEAR(EventDate) = #NextMonthYear#
AND MONTH(EventDate) = #NextMonth#
</cfquery>
(Not sure if those are the variables I should be grabbing)
I don't mean to seem like I am trying to get someone else to do my work, but how would I write this as an if statement that will work?
I totally understand what you are saying, but do not know how to write it.CFmonger
Copy link to clipboard
Copied
That sql will probably give you what you want. It might not be the most efficient way to do it. If your table has a lot of records, it will be slow because you are using functions in your where clause.
The sql I gave you in the other thread is logically equivalent to this sql. However, if your table has a lot of records, and EventDate is indexed, my sql will be faster.
Also, you still have to strip the time part away from the EventDate. You have already been told how.
Copy link to clipboard
Copied
It's now time for you to take a stab at it, based on what you now know. Get a clear picture in your head of what you are shooting for, then, dive in. It's the only way.
You know that you're looking for records where the event-date is this year and this month (not "next year/month").
This will give you the set of events that you need to display somewhere on the calendar for that month. Accumulate them in a data-structure, as I said, then separately build the table.
Let go of the code that you now have. A replacement that will be much clearer and understandable will occupy about two pages of source-code. Time to get off the edge of the pool and get back into the water.
Copy link to clipboard
Copied
I am working out a new query and it's own rules, so it will, hopfully slip right in. This is what I have started. right now it is throwing an error, I am missing an operator in my query. Can you help me figure out what I missed, and actually, am I on the right path?
New Query:
<cfset StartDate = CreateDate(year(now()), month(now()), 1)>
<cfset EndDate = DateAdd("m", 1, StartDate)>
<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT events.eventDate, events.ID AS ID
FROM events
WHERE YEAR(eventDate) = #DateFormat(StartDate, "mm/dd/yy")#
AND MONTH(eventDate) = #DateFormat(EndDate, "mm/dd/yy")#
ORDER BY MONTH(eventDate) ASC
</cfquery>
The Error:
Parameter validation error for the DATEFORMAT function. | |
The value of parameter 1, which is currently StartDate, must be a class java.util.Date value. | |
The error occurred in C:\website\event\index.cfm: line 160 | |
158 : SELECT events.eventDate, events.ID AS ID 159 : FROM events 160 : WHERE YEAR(eventDate) = #DateFormat('StartDate', "mm/dd/yy")# 161 : AND MONTH(eventDate) = #DateFormat('EndDate', "mm/dd/yy")# 162 : ORDER BY MONTH(eventDate) ASC |
what do you think? Is this a good start?
thanks
CFmonger
Copy link to clipboard
Copied
You are making a couple of errors. Here:
WHERE YEAR(eventDate) = #DateFormat(StartDate, "mm/dd/yy")#
AND MONTH(eventDate) = #DateFormat(EndDate, "mm/dd/yy")#
you are comparing numbers to strings. It won't work.
The error message you are getting shows different code than you posted. Where did those single quotes come from?
Copy link to clipboard
Copied
I am attempting to rewrite the query,I made it up like you had posted, I get a different error:
this is how I have the query now:
SELECT events.eventDate, events.ID AS ID
FROM events
WHERE eventDate >= #StartDate# and eventDate < #EndDate#
ORDER BY eventDate ASC
this is the error:
Error Executing Database Query. | ||||||||
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. | ||||||||
The error occurred in C:\website\event\index.cfm: line 160 | ||||||||
158 : SELECT events.eventDate, events.ID AS ID 159 : FROM events 160 : WHERE eventDate >= #StartDate# and eventDate < #EndDate# 161 : ORDER BY eventDate ASC 162 : </cfquery> | ||||||||
| ||||||||
Resources: |
I take it I need to do a dateformat function someplace in here? to cut it down.
do I put it on the WHERE eventDate?
Copy link to clipboard
Copied
Your error message may be misleading. You might be using a reserved word, ID, as a column alias. Take it away and see if you still get the error.
Another possible source would be the mismatch between the select clause, events.eventdate, and the order by clause, eventdate. Stranger things have happened.
Regarding dateformat, that returns a string, so if you want to use it, you have to get a string from your db to compare it with. It would be a lot simpler to use month() and year(), but I'm not sure why you have to add anything to your where clause.
Copy link to clipboard
Copied
I have it down to a syntax error now.
this is what I am working on:
<cfset StartDate = CreateDate(year(now()), month(now()), 1)>
<cfset EndDate = DateAdd("m", 1, StartDate)>
<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT MONTH(eventDate), events.ID AS ID
FROM events
WHERE MONTH(eventDate) >= #StartDate# AND MONTH(eventDate) <= #EndDate#
</cfquery>
this is the error:
Error Executing Database Query. | |
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'YEAR(eventDate) >= #6/1/2009 00:00:00# AND YEAR(eventDate) <= #7/1/2009 00:00:00#'. | |
The error occurred in C:\website\event\index.cfm: line 160 | |
158 : SELECT YEAR(eventDate), events.ID AS ID 159 : FROM events 160 : WHERE YEAR(eventDate) >= #StartDate# AND YEAR(eventDate) <= #EndDate# 161 : </cfquery> |
think we are close! Any ideas?
next question is how to write the if statement.
Copy link to clipboard
Copied
I worked up this code most of the night. I have all new code and this should work. I ripped it off another calendar but I am again back to my cfif statement not firing. I know it is because the date in the db is read as mm/dd/yyyy and right now my code is not reading it that way.
here is the new code:
<cfset firstOfTheMonth = createDate(year(now()), month(now()), 1)>
<!--- Add new variable for the end of the month--->
<cfset endOfTheMonth = createdatetime(year(now()), month(now()), #daysinmonth(firstOfTheMonth)#, 23, 59, 59)>
<cfquery name="CaleventRec" datasource="#APPLICATION.dataSource#">
SELECT eventDate, events.ID AS ID
FROM events
WHERE eventDate Between #firstOfTheMonth# and #endOfTheMonth#
</cfquery>
<cfset day = ValueList(CaleventRec.eventDate)>
<cfoutput query="CaleventRec">
<cfif listFind(day,LoopDay)>
<a href = "detail.cfm?id=#ID#">#Days#</a>
</cfif>
</cfoutput>
There has to be a way to make this work! I have pspent weeks on this thing and for some reason no one seems to know how to do it?! Come on! Please help mne get this fixed it really can't be that hard. I have tried a ton of different approaches and find it hard that someone doesn't know how to make this work.Please help.
Thank you
Copy link to clipboard
Copied
If loopday is a date variable, you can change this:
<cfset day = ValueList(CaleventRec.eventDate)>
<cfoutput query="CaleventRec">
<cfif listFind(day,LoopDay)>
<a href = "detail.cfm?id=#ID#">#Days#</a>
</cfif>
</cfoutput>
to this
<cfoutput query="CaleventRec">
<cfif Dateformat(loopday, "mask") is DateFormat(eventdate, "mask")>
<a href = "detail.cfm?id=#ID#">#Days#</a>
</cfif>
</cfoutput>
The Dateformat takes care of the time portion of your db value
Copy link to clipboard
Copied
the cfif is still not responding. I can do a dump before the cfif still and when I dump it after teh tag, it doesn't do anything. This is driving me crazy.....
Copy link to clipboard
Copied
I solve if/else problems like this:
<cfif a is b>
yes
<cfelse>
cfdump a and b
</cfif>
Copy link to clipboard
Copied
just tried that, it does the dump after the cfelse not the other. I know ther eis a record in this month that should make a link. I put it there on purpos.
Copy link to clipboard
Copied
I took the advice of chuck it and start over. I had success with this, so i am going to start a new threat. This one has gotten too jumbled up and think it is best to look at it totally differently.I am sorry for the double posting on this.