Inserting an expiration date into a table and having messages expire without breaking functionality

Report · Mar 23, 2011

I’m using Coldfusion 9,0,0,251028 on Windows 7 64-bit, with a Microsoft Access 97 database.

The code is for an application that takes a submitted message from a form and posts it to a different page (breakingnews.cfm). The form values are inserted into the "news" table on a database, including the date the message was submitted (mes_dat) and the date it should expire (exp_dat).

On the database, the is_current and display columns have a default value of 0.

The form action is new_process.cfm, which does two things:

1) Does <cfset expdate = createdate(end_year, end_month, end_day)>. End_year, end_month, and end_day are dropdown selectors that set the expiration date. A problem I have had is expdate not being able to be inserted by post_breaking.cfm, but only when it is put online, on my local testing server there seems to be no problem.

2) Includes a template named "post_breaking.cfm", which runs these queries:

<cfquery name="get_init_info" datasource="#db#">
select id
from news
where is_current = 1
</cfquery>

<cfquery name="update_info_1" datasource="#db#">
update news
set is_current = 0, scrollshow = 0
</cfquery>

    <cfquery name="update_info_2" datasource="#db#">
     update news
        set display = 1
        where id = #get_init_info.id#
    </cfquery>

</cfif>

<cfquery name="put_in_info" datasource="#db#">
insert into news
(is_current, display, mes_dat,mes_tim,mes_sub,mes_text,scrollshow, exp_dat)
values
(1,0, #createodbcdate(now())#, #createodbctime(now())#, '#subject#', '#message#',1, #expdate#)
</cfquery>

The message is then displayed on breakingnews.cfm if the column is_current on the news table is 1. This is code I have inherited, so I'm not sure how it does it, but the code only allows 5 messages at a time to have is_current = 1.

The part I'm trying to make happen is having a query run when breakingnews.cfm loads that checks if exp_dat is between now() and mes_dat to set is_current to 1, while still maintaining only 5 items having an is_current of 1.

When breakingnews.cfm is accessed it runs these queries:

<cfquery name="get_info" datasource="#db#">
select *
     from news
     where
         <cfif not isdefined("id")>
         is_current = 1
         <cfelse>
         id = #id#
         </cfif>
    order by mes_dat desc, mes_tim desc
</cfquery>

<cfquery name="add_exp" datasource="#db#">
   UPDATE news
   SET is_current = 1
   WHERE now() BETWEEN mes_dat AND exp_dat
</cfquery>

<cfquery name="remove_exp" datasource="#db#">
   UPDATE news
   SET is_current = 0
   WHERE now() NOT BETWEEN mes_dat AND exp_dat
</cfquery>

This will cause the messages that have an exp_dat between now() and mes_dat to be displayed and otherwise not display on breakingnews.cfm.

However, when a new message is submitted, upon accessing breakingnews.cfm the first time, only the newly submitted message is displayed.

If the page is refreshed, the messages that were filtered correctly will be displayed under the new message, but there will now be six items displaying on the page when there should only be five.

How can I get the correct amount of messages, and have them display the first time without needing a refresh?

Report · Mar 23, 2011

The is_current field is not only unnecessary, but probably messing you up. If you have an expiry date and time, use it in the where clause of your query. Something like

select whatever

from whereever

where expirydatetime > currentdatetime

Report · Mar 23, 2011

use it in the where clause of your query

Which query?

Report · Mar 23, 2011

The query you named "get_init_info".

Report · Mar 23, 2011

Ok, I changed get_init_info from:

<cfquery name="get_init_info" datasource="#db#">
select id
from news
where is_current = 1
</cfquery>

to:

<cfquery name="get_init_info" datasource="#db#">
select id
from news
where exp_dat > now()
</cfquery>

and it caused all other rows in is_current and scrollshow to be changed to 0 after creating a new message (though the new message's scrollshow and is_current are 1).

Report · Mar 23, 2011

If you simply output the results of your first query, do you get the desired results?

Report · Mar 23, 2011

Yes, and it returns the correct result set.

Instead of editing the get_init_info query, I tried creating a new one under update_info_1:

<cfquery name="filter_exp" datasource="#db#">
UPDATE news
SET is_current = 1
where exp_dat > now()
</cfquery>

and the message stays and no longer needs the odd refresh before it displays correctly on breakingnews.cfm,

but it's still broken in the fact that the amount of rows that can have is_current = 1 is no longer limited to 5

(it simply keeps every new message at is_current = 1).

Report · Mar 23, 2011

Google "ms access top n query" and that won't be a problem.

Report · Mar 24, 2011

Sorry, I jumped the gun on that one. The query output is returning the rows and sorting them correctly, but with this query:

<cfquery name="get_info" datasource="#db#">
SELECT TOP 5 *
FROM news
WHERE exp_dat > now()
ORDER BY mes_dat DESC
</cfquery>

...it's still returning more than 5 rows.

Message was edited by: BarrettChamberlain

Report · Mar 25, 2011

How many distinct values of mes_dat is your query returning?

Report · Mar 25, 2011

Out of the five returned, three: three of them are 2011-03-11 00:00:00, the other two are 2011-01-25 00:00:00.0 and 2010-12-20 00:00:00.

Report · Mar 25, 2011

Did the behaviour change? You said earlier the query was returning more than 5 rows.

Even more importantly, can you figure out why I asked my earlier question?

Report · Mar 25, 2011

Did the behaviour change? You said earlier the query was returning more than 5 rows.

No, sorry, I put that in a confusing way. I have a test database setup that has 5 rows that fulfill the where clause. When I added the sixth through the form, the query was getting six rows.

Even more importantly, can you figure out why I asked my earlier question?

Yes, I changed the get_info query to:

<cfquery name="get_info" datasource="#db#">
SELECT TOP 5 *
FROM news
WHERE exp_dat > now()
ORDER BY id DESC
</cfquery>

and now it's doing what it should...though I'm a little bit confused as to why it happened.

By default does the query check how many results are returned by what it's ordered by (meaning that duplicates don't count as a selected row)?

Report · Mar 25, 2011

Regarding, "By default does the query check how many results are returned by what it's ordered by (meaning that duplicates don't count as a selected row)?"

Not exactly.

Top n queries are based on what it's in your order by clause. Also, in the case of a tie for the nth record, all those records will be returned. I thought that is what had happened to you earlier.

Inserting an expiration date into a table and having messages expire without breaking functionality

1 Correct answer