Auto expire after a date
Moderator: General Moderators
Auto expire after a date
I am trying to create expiry functionality for articles on a site.
I want to define an expiry date. I would get the current date and when the query executes it would compare the date values and if current date > expiry date, then the news article won't be listed. How can I do this in a mysql query?
I want to define an expiry date. I would get the current date and when the query executes it would compare the date values and if current date > expiry date, then the news article won't be listed. How can I do this in a mysql query?
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Auto expire after a date
Code: Select all
$query=mysql_query("SELECT * FROM table_name WHERE curdate() < expiry_date");
Last edited by aceconcepts on Wed Nov 12, 2008 3:33 am, edited 1 time in total.
Re: Auto expire after a date
First, I'm not sure what you need - explicit expiry date (i.e. column) or expiry period (i.e. application defined, bu still need post_date column)?
Either of them is trivial (sorry
) to be done.
What have you done so far?
Either of them is trivial (sorry
What have you done so far?
There are 10 types of people in this world, those who understand binary and those who don't
Re: Auto expire after a date
I don't use DATE datatype, I have several columns for them because I need more control, so it's like
article_expire_day | article_expire_month | article_expire_year | article_expire_hour | article_expire_minute | article_expire_second
article_expire_day | article_expire_month | article_expire_year | article_expire_hour | article_expire_minute | article_expire_second
Can I do this to match my way?$query=mysql_query("SELECT * FROM table_name WHERE curdate() < expiry_date");
Code: Select all
$query=mysql_query("SELECT * FROM articles WHERE $current_day < article_expire_day AND $current_month < article_expire_month AND $current_year < article_expire_year");- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Auto expire after a date
Your database setup seems quite convoluted. Why have you set it up like this? Why not use DATE type and TIME type?
Re: Auto expire after a date
Hm... Are you sure it gives you more control? You have a bunch of MySQL date-time functions which are ready for use...Sindarin wrote:I don't use DATE datatype, I have several columns for them because I need more control, so it's like
article_expire_day | article_expire_month | article_expire_year | article_expire_hour | article_expire_minute | article_expire_second
@aceconcepts - this is your 4th time you do this today - let it be the last or ELSE ...
There are 10 types of people in this world, those who understand binary and those who don't
Re: Auto expire after a date
First, I find it hard to use them, they never work correctly with my setup. Second I have read somewhere there is a limit on max year value (2039 I think) DATE accepts.
problem is they don't seem to work good for me.Hm... Are you sure it gives you more control? You have a bunch of MySQL date-time functions which are ready for use...
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Auto expire after a date
@ VladSun - 4th time. What are you talking about dude? I'm trying to figure out why he doesn't use a date and time type.
Re: Auto expire after a date
So did I, but it was the 4th time in an hour you beat me at postingaceconcepts wrote:@ VladSun - 4th time. What are you talking about dude? I'm trying to figure out why he doesn't use a date and time type.
There are 10 types of people in this world, those who understand binary and those who don't
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Auto expire after a date
ah, ok. hehe
Sorry I thought I had made 4 identical posts by accident!!!
I am quick off the trigger today
I just need to keep it up though 
Sorry I thought I had made 4 identical posts by accident!!!
I am quick off the trigger today
Re: Auto expire after a date
Still I'll have to explode the string into different variables all the time to use separately the values. Which is kind of tedious.Hm... Are you sure it gives you more control? You have a bunch of MySQL date-time functions which are ready for use...
Plus I find the other datatypes a bit confusing generally. I like going with the variants of int, char and text.I'm trying to figure out why he doesn't use a date and time type.
Re: Auto expire after a date
I am trying this and doesn't work,
as non expire value I am using '--'
How do I do it?
as non expire value I am using '--'
I can't compare the datetime. Isn't AND supposed to check if ALL of those values I ask are what I want?$query = "SELECT * FROM articles WHERE article_showhome='Yes' AND $getday <= article_expire_day AND $getmonth <= article_expire_month AND $getyear <= article_expire_year AND $gethour <= article_expire_hour AND $getminute <= article_expire_minute AND $getsecond<=article_expire_second OR article_expire_day='--' OR article_expire_month='--' OR article_expire_hour='--' OR article_expire_minute='--' OR article_expire_second='--' OR article_expire_year='' ORDER BY article_importance ASC LIMIT 10";
How do I do it?