Newer than 30 Days

[doubledee]

I only want to display Comments that have been made within the last 30 days.

 

In my Comments table, I have a "created_on" field which is populated with NOW() like this...

 

// Build query.
$q3 = "INSERT INTO comment(article_id, member_id, comment_no, body, created_on)
			VALUES(?, ?, ?, ?, NOW())";

 

 

What do I need to do, so when I create my SELECT query to display the Comments, it only shows Comments "created on" from the current moment to back to 30 days old?

 

Thanks,

 

 

Debbie

 

[requinix]

DATE_SUB is a good place to start.

[doubledee]

DATE_SUB is a good place to start.

 

How does this look...

 

SELECT c.member_id, a.slug, a.heading, LEFT(c.body, 30) AS body, c.created_on, c.id
FROM article AS a
INNER JOIN comment AS c
ON a.id=c.article_id
WHERE member_id=35
AND comment_approved=1
AND c.created_on >= DATE_SUB(now(), INTERVAL 30 DAY) 
ORDER BY created_on DESC

 

 

Debbie

 

[Barand]

An alternative is

 

AND c.created_on >= NOW() -  INTERVAL 30 DAY

[doubledee]

An alternative is

 

AND c.created_on >= NOW() -  INTERVAL 30 DAY

 

Okay, thanks.

 

 

Debbie