Page 1 of 1
Auto expire after a date
Posted: Wed Nov 12, 2008 3:22 am
by Sindarin
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?
Re: Auto expire after a date
Posted: Wed Nov 12, 2008 3:32 am
by aceconcepts
Code: Select all
$query=mysql_query("SELECT * FROM table_name WHERE curdate() < expiry_date");
Re: Auto expire after a date
Posted: Wed Nov 12, 2008 3:33 am
by VladSun
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?
Re: Auto expire after a date
Posted: Wed Nov 12, 2008 3:58 am
by Sindarin
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
$query=mysql_query("SELECT * FROM table_name WHERE curdate() < expiry_date");
Can I do this to match my way?
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");
Re: Auto expire after a date
Posted: Wed Nov 12, 2008 4:01 am
by aceconcepts
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
Posted: Wed Nov 12, 2008 4:03 am
by VladSun
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
Hm... Are you sure it gives you more control? You have a bunch of MySQL date-time functions which are ready for use...
@aceconcepts - this is your 4th time you do this today - let it be the last or ELSE ...

Re: Auto expire after a date
Posted: Wed Nov 12, 2008 4:07 am
by Sindarin
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.
Hm... Are you sure it gives you more control? You have a bunch of MySQL date-time functions which are ready for use...
problem is they don't seem to work good for me.
Re: Auto expire after a date
Posted: Wed Nov 12, 2008 5:47 am
by aceconcepts
@ 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
Posted: Wed Nov 12, 2008 9:07 am
by VladSun
aceconcepts 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.
So did I, but it was the 4th time in an hour you beat me at posting

Re: Auto expire after a date
Posted: Wed Nov 12, 2008 10:21 am
by aceconcepts
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

Re: Auto expire after a date
Posted: Wed Nov 19, 2008 3:48 am
by Sindarin
Hm... Are you sure it gives you more control? You have a bunch of MySQL date-time functions which are ready for use...
Still I'll have to explode the string into different variables all the time to use separately the values. Which is kind of tedious.
I'm trying to figure out why he doesn't use a date and time type.
Plus I find the other datatypes a bit confusing generally. I like going with the variants of int, char and text.
Re: Auto expire after a date
Posted: Thu Nov 20, 2008 7:53 am
by Sindarin
I am trying this and doesn't work,
as non expire value I am using '--'
$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";
I can't compare the datetime. Isn't AND supposed to check if ALL of those values I ask are what I want?
How do I do it?