Need help on find row with is 30 days old

[kerent]

Hello PHP experts,

 

please help me with this...

 

I need to pull out data in mysql database for the row which are 30 days old.

I am storing the the date and time as "now()" in the "time" column and is timestamp structure.

 

My table name is "check"

 

Column

 

id  name    time

1  simon  2007-12-25 15:53:18

2  Jen      2007-12-25 15:53:18

3  Kenny  2007-11-14 15:53:18

4  Peter    2007-11-04 15:53:18

5  Ronny  2007-12-07 15:53:18

 

As you can see in this table there are 2 row which are 30 days and they are ID 3 and 4.

 

How can I check and pull out this data?

 

something like this..

 

$query = "SELECT id, name, time FROM check";
$strSQL = mysql_query($query);

while($row = mysql_fetch_array($strSQL)) {

if($row['time'] ???? how to do this part??) {
    echo "$row['name'] is 30 days old<br>";
    } else {
    echo "Nothing to display";
    }
}

 

thanks

[redarrow]

TRY THIS MATE A GUESS

 

<?php
$query = "SELECT id, name, time FROM check WHERE `time
` < ADDDATE(NOW(), INTERVAL -30 DAY_MINUTE";

$strSQL = mysql_query($query);

while($row = mysql_fetch_array($strSQL)) {

echo "".$row["name"]." is 30 days old <br>";
}    
?>

[kerent]

Hi,

 

thanks for replying, seem to have some problem now it displays the result even is less than 30 day old. Any fix?

[redarrow]

try that

<?php
$query = "SELECT id, name, time FROM check WHERE `time
` < ADDDATE(NOW(), INTERVAL -30 DAY)";

$strSQL = mysql_query($query);

while($row = mysql_fetch_array($strSQL)) {

echo "".$row["name"]." is 30 days old <br>";
}    
?>

[kerent]

Thanks alot! It works well.

 

Can this work with =  also?

 

$query = "SELECT id, name, time FROM check WHERE `time` = ADDDATE(NOW(), INTERVAL -30 DAY)";

 

possible?

[redarrow]

GOT IT ALL FROM HERE

 

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_day

 

 

HAVE A REEL GOOD XMAS MATE WELCOME

[redarrow]

ONE STEP AT A TIME READ THE POST OK GOOD LUCK

[Barand]

Thanks alot! It works well.

 

Can this work with =  also?

 

$query = "SELECT id, name, time FROM check WHERE `time` = ADDDATE(NOW(), INTERVAL -30 DAY)";

 

possible?

 

As NOW() contains a time element then using "=" will pull only those records containing exactly the same time value 30 days ago. You need to ignore the time element of the timestamp.

 

SELECT id, name, time FROM check WHERE DATE(`time`) = CURDATE() - INTERVAL 30 DAY