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?
changing information in a field based on days past a date
Moderator: General Moderators
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Theres no need for an extra column,
Code: Select all
UPDATE `status` SET active = 'off' WHERE DATE_ADD(`date`, INTERVAL 31 DAY) > NOW()- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
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).
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).
You guys work together and communicate in this forum??
It's like seeing a couple at a restaurant table, both of them with cell phones, and realizing they're talking to each other!!
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.
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.
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA