Automatically eliminating a record at the begining of a new month

[CFmonger]

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

[davidsimms]

Select only those records from July forward in your SQL statement.

[CFmonger]

How would I write that into my where statement as it is? I did some changes in the code and got ride of some old functions that are not being used any longer.Would I add mm, Now() to the where statement?

These are th query's now. there are 3 of them, and this change will have to effect all 3, I believe.

Query 1:

<cfquery name="gTempx1" datasource="#APPLICATION.dataSource#" blockfactor="5">
         SELECT DISTINCT(YEAR(eventDate)) AS gTempx2
         FROM events
         WHERE YEAR(eventDate) >= #dateFormat(now(), 'yyyy')#
         ORDER BY YEAR(eventDate) ASC
</cfquery>

Query 2

<cfquery name="gTempx3" datasource="#APPLICATION.dataSource#" blockfactor="5">
       SELECT DISTINCT(MONTH(eventDate)) AS gTempx4
       FROM events
       WHERE YEAR(eventDate) = #gTempx5#
       ORDER BY MONTH(eventDate) ASC
</cfquery>

Query 3

<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 YEAR(eventDate) = #gTempx5# AND MONTH(eventDate) = #gTempx3.gTempx4#
      ORDER BY DAY(eventDate) ASC
</cfquery>

So would I add something like this to my where:

WHERE YEAR(eventDate) >= #dateFormat(now(), 'yyyy','mm')#

I know that isn't right, I am not distinguishing the month now()

[davidsimms]

I only glanced over your code, but I don't understand why the YEAR() function exists in your queries given what you've described you're trying to do. Getting rid of that it seems would give a desirable outcome.

[CFmonger]

So I should change it to this:

                   SELECT DISTINCT(MONTH(eventDate)) AS gTempx2
                   FROM events
                   WHERE MONTH(eventDate) >= #dateFormat(now(), 'mm')#
                   ORDER BY MONTH(eventDate) ASC

then change my cfset to

<cfset distintMonthEventDate = DateFormat(now(), "mm")>

or is there more then this?

thanks

CF

[davidsimms · Accepted solution]

WHERE eventDate > #NOW()#

[CFmonger]

ok, I am adding that in, but now it isn't showing anything on the page, not an error either.

this is what I did:

q1

<cfquery name="gTempx1" datasource="#APPLICATION.dataSource#" blockfactor="5">
                   SELECT DISTINCT(YEAR(eventDate)) AS gTempx2
                   FROM events
                   WHERE eventDate > #NOW()#
                   ORDER BY YEAR(eventDate) ASC
                </cfquery>

q2

<cfquery name="gTempx3" datasource="#APPLICATION.dataSource#" blockfactor="5">
                      SELECT DISTINCT(MONTH(eventDate)) AS gTempx4
       FROM events
       WHERE eventDate > #NOW()# = #gTempx5#
                      ORDER BY MONTH(eventDate) ASC
                    </cfquery>

q3

<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 YEAR(eventDate) = #gTempx5# AND MONTH(eventDate) = #gTempx3.gTempx4#
      ORDER BY DAY(eventDate) ASC
                      </cfquery>

not sure what to do in q3, all 3 depend on each other.

do I use the query I showed with the distinct month and change all the years to month and add your query?
kind of tried that and so far not working

[CFmonger]

I am getting someplace now.
I have been messing with the where statement. I have it so it will eliminate the previous months records, but it is duplicating the records now and makeing them for lets say I have a record is set for July 2009 that same record will appear as July 2010 as well.

This is what I changed my 2 querys to:

q1

<cfquery name="gTempx1" datasource="#APPLICATION.dataSource#" blockfactor="5">
                SELECT DISTINCT(YEAR(eventDate)) AS gTempx2, display
                FROM events
                WHERE YEAR(eventDate) >= #dateFormat(now(), 'yyyy')#
    ORDER BY YEAR(eventDate) ASC
               </cfquery>

q2.

<cfquery name="gTempx3" datasource="#APPLICATION.dataSource#" blockfactor="5">
                      SELECT DISTINCT(MONTH(eventDate)) AS gTempx4, display
       FROM events
       WHERE eventDate > #NOW()#
                      ORDER BY MONTH(eventDate) ASC
                    </cfquery>

q3.

<cfquery name="GetRecordevent" datasource="#APPLICATION.dataSource#">
                        SELECT events.title AS ViewField2, events.eventDate AS ViewField3, events.eventTime
                        AS ViewField4, events.location AS ViewField5, events.ID AS ID
      FROM events
      WHERE eventDate > #NOW()# = #gTempx5# AND MONTH(eventDate) = #gTempx3.gTempx4#
      ORDER BY DAY(eventDate) ASC
                      </cfquery>

can anyone help me fix this so it works properly. for some reason it is making the same records appear every year.