Automatically eliminating a record at the begining of a new month
Hello;
I have a little app I am building that makes a list of records organized by the month the record falls in. this is how it looks:
JUNE
Record title goes here as a link
record date
the location of the event
<!--- all the records for JUNE fall in here, the link goes to a page with all the details --->
JULY
same thing as June
and so on....Now as it is the code works fine, BUT when lets say it goes to JULY, the JUNE records are still visible, I want them to go away after the month passes. Is that possibly an easy task using the code I wrote for this? The don't have to be deleted from the DB, but they need to "disappear" in the web site when the month passes.
This is the code:
<cfquery name="gTempx1" datasource="#APPLICATION.dataSource#" blockfactor="5">
SELECT DISTINCT(YEAR(eventDate)) AS gTempx2, display
FROM events
WHERE display = 0 AND YEAR(eventDate) >= #dateFormat(now(), 'yyyy')# ORDER BY YEAR(eventDate) ASC
</cfquery>
<cfset distintYearEventDate = DateFormat(now(), "yyyy")>
<cfset tempdx = DateFormat(now(), "mm/dd/yy")>
<cfif DateFormat(now(), "mm/dd/yy") GTE "#tempdx#">
<cfoutput query="gTempx1">
<cfset gTempx5 = "#gTempx1.gTempx2#">
<cfif DateFormat(now(), "mm/dd/yy") GTE "#tempdx#">
<cfset gTempx6 = DateFormat(now(), "yyyy")>
<cfset gTempx7 = DateFormat(now(), "mmmm")>
<cfset distintYear = DateFormat(now(), "yyyy")>
</cfif>
<cfquery name="gTempx3" datasource="#APPLICATION.dataSource#" blockfactor="5">
SELECT DISTINCT(MONTH(eventDate)) AS gTempx4, display
FROM events
WHERE display = 0 AND YEAR(eventDate) = #gTempx5#
ORDER BY MONTH(eventDate) ASC
</cfquery>
<cfset dateAdd = DateFormat(now(), "mmmm")>
<cfset thirtyTempx1 = "#dateFormat(dateAdd('d',-30,Now()),'mm/dd/yyyy')#">
<cfloop query="gTempx3">
<cfset gTempx6 = "#gTempx4#/01/#gTempx5#">
<b><u>#ucase("#DateFormat(gTempx6,'mmmm YYYY')#")#</u></b>
<cfquery name="GetRecordevent" datasource="#APPLICATION.dataSource#">
SELECT events.title AS ViewField2, events.eventDate AS ViewField3, events.location AS ViewField5, events.ID AS ID
FROM events
WHERE display = 0 AND YEAR(eventDate) = #gTempx5# AND MONTH(eventDate) = #gTempx3.gTempx4#
ORDER BY DAY(eventDate) ASC
</cfquery>
<cfloop query="GetRecordevent">
<a href="detail.cfm?ID=#ID#" class="subNav">
#ViewField2#
#dateformat(ViewField3, "mmm-dd-yyyy")#<br>
Location: #ViewField5#
</cfloop>
</cfloop>
</cfoutput>
</cfif>
can anyone help me out? Don't know what to tweekout on this to make it rotate through the months like a normal calendar.
thank you
CFmonger
