Entries newer than 2 weeks marked with a "new post"

[Amarok]

I would like to pursue a "new post" function that would mark entried to my database newer than 2 weeks with a special graphic or "new post" in red, but I'm not sure how it would work. My "date_post" field has the following - TYPE: timestamp / ATTRIBUTES: ON UPDATE CURRENT_TIMESTAMP / DEFAULT: CURRENT_TIMESTAMP so a particular entry is getting a time stamp that ends up looking like this: 2007-08-23 13:30:17

 

I would like to have the "new post" notification show up only if the entry is less than 2 weeks old. Is this possible? I am sure there is a way to compare the TIMESTAMP to the current time minus 2 weeks, but I have no idea what the syntax would be.

 

Right now my code is as follows:

 

$sql = "SELECT id as id, title
        FROM   main
LIMIT 0, 15
       ";

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

while ($row = mysql_fetch_assoc($result)) {
    //echo $row["id"], ' ', $row["title"], '<br />';
    echo '<li><strong>','<a href="details.php?id=', $row[id], '">', $row[title], '</a></strong></li>';

}
echo '</ul>';

 

[akitchin]

try this:

 

SELECT stuff, IF(date_post >= DATE_DUB(NOW(), INTERVAL 2 WEEK), 'NEW', 'OLD') AS new_or_old FROM ...

 

'new_or_old' will contain either 'NEW' if it's 2 weeks old or newer, and 'OLD' otherwise.