changing information in a field based on days past a date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

changing information in a field based on days past a date

Post by ReverendDexter »

I'm having difficulty even figuring out the proper language to search for this, so I apologize if there's a thread already on this topic.


What I'd like to do is change the value of a field based on the current date being an arbitrary number of days past a date stored in the row. For sake of argument, we'll say it's 30 days. This would be for an effective Time To Live for the row, when 30 days hits, the "active" field would be switched from "on" to "off".

Unfortunately, this is for MySQL 4.x, so triggers, my first thought, are totally thrown out. I don't have access to the server (beyond phpmyadmin) so no cron jobs. My only other thought is to have a piece of code that does a "set active = 0 where..." placed somewhere arbitrary that I expect users to hit on a daily basis (I'm thinking that it would do this any time the user lists all of the applicable rows).

Is there a better/easier/cleaner way to do this? It seems like an awful lot of excess queries to the server when this bit of code really only needs to fire once a day to be effective.

Thoughts/Comments/Ideas?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

you could add an expire_time column... then you'd simply put WHERE expire_time < NOW() in your other queries. :) That would require adding an extra column, but then you'd have the flexibility to make it expire any time you want... easily. :)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Theres no need for an extra column,

Code: Select all

UPDATE `status` SET active = 'off' WHERE DATE_ADD(`date`, INTERVAL 31 DAY) > NOW()
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

true, but for this particular application (I work with ReverandDexter) I think an extra column makes sense... so that we can set when each post should expire

EDIT: also, I think the question was more WHEN to apply that update rather than what the query should look like
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

Yeah, I'm looking more for a theory answer and less for a code answer. And there's no need for an extra column - that's just extra upkeep. If I go the "days since post" method, I'm just gonna do a "select from blah where date <= post_date + expire_days".

I'm more wondering if there's a way to trigger a "set active = 0 for blah where date > post_date + expire_days" once a day without using a trigger or cron job; currently I'm thinking about having it just run the update query everytime the items get listed (which seems like a major waste of resources, but it's the only thing I know that people WILL be hitting on a frequent basis).
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

You guys work together and communicate in this forum?? :D It's like seeing a couple at a restaurant table, both of them with cell phones, and realizing they're talking to each other!! :D

Anyway, I don't know of any way in MySQL to cause action based on the date/time. Even a trigger requires something in the data to be changed. As far as I know, you're going to have to use a cron job for that.

But maybe you don't really need to cause the action to take place at a particular time... while the data is just sitting there in the database, it can't do anything anyway. It's when you go to USE it that you care, I'm hypothesizing. You can probably code your business rules in the application. Of course, if there will be many applications based on this table, that's hard to control.

Just my thoughts.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

califdon wrote:You guys work together and communicate in this forum?? :D
Well, any questions I have, someone else may have, so might as well make a thread so our discussion can be documented and acessible to anyone else who is having a similar issue.
Post Reply