Auto expire after 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
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Auto expire after a date

Post 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?
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Auto expire after a date

Post by aceconcepts »

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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Auto expire after a date

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Auto expire after a date

Post 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");
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Auto expire after a date

Post by aceconcepts »

Your database setup seems quite convoluted. Why have you set it up like this? Why not use DATE type and TIME type?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Auto expire after a date

Post 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 ... :twisted: :twisted: :twisted:
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Auto expire after a date

Post 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.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Auto expire after a date

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Auto expire after a date

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Auto expire after a date

Post by aceconcepts »

ah, ok. hehe

Sorry I thought I had made 4 identical posts by accident!!!

I am quick off the trigger today :D I just need to keep it up though :wink:
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Auto expire after a date

Post 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.
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: Auto expire after a date

Post 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?
Post Reply