date / expiration problem mysql

[izbryte]

I have a script where I'm trying to work out an expiration on their uploaded posts

 

I have a field "date" and a field "duration". When they submit their post date is submitted as NOW() and duration is either "30" or "60" (days).

When I pull this back I want to select everythings that NOT expired... so NOW must be less than the DATE they entered it + the DURATION they chose.

Here's my query (that's not working)

 $query  = "SELECT * FROM jobs WHERE NOW() < `date` + INTERVAL `duration` DAY AND paid = '1' ORDER BY `job_id` DESC $limit";

 

Can anyone tell me why this is not working?

 

[toplay]

Because you mention date was populated with NOW() I assume that the date column is a DATETIME type. If you don't care about the time, then just focus the query on the date.

 

Examples:

 

$query  = "SELECT * FROM jobs WHERE CURDATE() < DATE(`date` + INTERVAL `duration` DAY) AND paid = '1' ORDER BY `job_id` DESC $limit";

 

$query  = "SELECT * FROM jobs WHERE CURDATE() < DATE_ADD(`date`, INTERVAL `duration` DAY) AND paid = '1' ORDER BY `job_id` DESC $limit";

[izbryte]

yes I am using datetime.  I'm still stuck though. With the examples you sent me I'm only seeing the posts from today.

[toplay]

My examples are not different than yours except it's focusing on just the date.

 

It's easier if you just show some sample data of rows (dates), and show which ones/rows/dates you want/expect to get back.

 

 

[izbryte]

ok, cool. here are a few of my date entries:

 

2007-11-18 18:14:38

2007-12-07 18:41:31

2007-12-08 08:49:40

 

"duration" is set to 30 on all of them right now

so what I'm looking to get back is the date + 30 days

 

so for example, say they registered on Jan 1, 2008 with 30 days duration (making expiration date Jan 31, 2008). their submission should show till Jan 31, 08

 

but if they registered on Dec 1, 2007 with 30 days duration (making expiration date Dec 31, 2007). the submission would not show

[toplay]

I think what you're saying is that you only want to retrieve rows that have not expired yet. So, just change the where clause to something like this:

 

$query = "

SELECT j.*, DATE_ADD(`date`, INTERVAL `duration` DAY) AS expiration_date

  FROM `jobs` j

WHERE DATE_ADD(`date`, INTERVAL `duration` DAY) >= CURDATE()

          AND paid = '1'

ORDER BY `job_id` DESC

$limit

";

 

That will add the 30 days to their date and if it's greater than or equal to today, the row will be retrieved (and you can display it). If you don't want to show dates expiring today, then take out the equal (=) and leave it as "> CURDATE()".

 

hth.