Page 1 of 1

Need help on find row with is 30 days old

Posted: Tue Dec 25, 2007 2:50 am
by 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..

Code: Select all

$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

Posted: Tue Dec 25, 2007 3:07 am
by VladSun

Posted: Tue Dec 25, 2007 1:08 pm
by Jonah Bron
I believe you want to use regex here. Possibly this?

Code: Select all

$query = "SELECT id, name, time FROM check"; 
$strSQL = mysql_query($query); 
  
while($row = mysql_fetch_array($strSQL)) {
    $_row = preg_match(/[^-]{4}-([^-]{1,2})-/, $row['time'], $matches);
    if($matches[0]==date('m')-2) { 
        echo "{$row['name']} is 30 days old<br>"; 
    }else{ 
        echo "Nothing to display"; 
    } 
}

Posted: Tue Dec 25, 2007 4:25 pm
by VladSun
PHPyoungster wrote:I believe you want to use regex here.
No, it would be absolutely wrong in this case - first you do it at code layer, and second, you do it by using very inefficient code.
It should be done at DB layer by using an appropriate function - DATE_SUB() or DATE_ADD() in this case.

PS: And third, I think your code would not give accurate results.

Posted: Tue Dec 25, 2007 5:05 pm
by ddragas
I agree with VladSun. Here is some example

Code: Select all

SELECT * FROM table_name WHERE date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)

Posted: Tue Dec 25, 2007 10:50 pm
by John Cartwright
I don't think it needs repeating, but what the heck.

Vladsun, ddragas++

Posted: Wed Dec 26, 2007 8:09 pm
by Jonah Bron
Ah. Well, it wasn't tested. :oops:

Don't know much about MySQL connections. I do XML. :wink:

Posted: Wed Dec 26, 2007 9:51 pm
by Ambush Commander
I've never been very fond of MySQL's built-in timestamps. Integer Unix timestamps for me!

Posted: Thu Dec 27, 2007 3:54 am
by VladSun
Ambush Commander wrote:I've never been very fond of MySQL's built-in timestamps. Integer Unix timestamps for me!
I used to use UNIX timestamps with MySQL 4, but with MySQL 5 I use the built-in one - the set of date-calculating functions is good and big enough to make my life easier :)