If statment...based on date?

[sgtpepper]

When I use the following query...

select mod_date_time from residential where Id =457129;

I get the results....

+---------------------+
| mod_date_time |
+---------------------+
| 2003-11-24 10:50:00

How can I now....do the following:

Setup the code so that if mod_date_time is 30 days or less from today than mod_date_time = new

Basically, I am trying to find all results that are 30 days or newer....

[sgtpepper]

I am thinking I should use something like this:

SELECT mod_date_time FROM residential WHERE TO_DAYS(NOW()) - TO_DAYS(mod_date_time) <= 30

But I am not sure how I can incorporate this into my original query without loosing data. That is why I was looking at an If-statement.

[kevinsequeira]

try this

SELECT mod_date_time FROM residential WHERE TO_DAYS(NOW()) - TO_DAYS(mod_date_time) < 31

to update

UPDATE residential SET mod_date_time ='whatever' WHERE TO_DAYS(NOW()) - TO_DAYS(mod_date_time) < 31

reg
kevin

[sgtpepper]

Actaully, I am trying to do something similar to this:

<?
$displaystatus = "$row[status]";

if(($displaystatus) == 'C') {
$displaystatusvar = "";
}
if(($displaystatus) == 'P') {
$displaystatusvar = "
<table border=0 cellspacing=1 width=100%>
<tr>
<td width=100% bgcolor=#FFFF00>
<p align=center><font size=2><b>Sale Pending</b></font></td>
</tr>
</table>
";
}
echo $displaystatusvar;
?>

See, this script looks at $row[status] and if It is 'C' does nothing...if it is 'P' is displays HTML.

I want to do EXACTLY the same thing with mod_date_time....only I want to display HTML code only if the date is between 30 days ago and today.

[kevinsequeira]

well instead of getting the all the rows and then doing an IF-ELSE in PHP, just get the rows that u need through the DB-query directly, by doing this

SELECT mod_date_time FROM residential WHERE TO_DAYS(NOW()) - TO_DAYS(mod_date_time) < 31

and display the resultset. its the same thing right ....

does that help ??

reg
kevin

[sgtpepper]

I want to display all the results...but highlight the results that are 30 days or newer.

[kevinsequeira]

well use the Date() function along with mktime() to find out if the mod_date_time is less than 31.

there is an example here

http://ca3.php.net/manual/en/function.date.php

reg
kevin