Need help on find row with is 30 days old

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kerent
Forum Newbie
Posts: 1
Joined: Tue Dec 25, 2007 2:48 am

Need help on find row with is 30 days old

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Post 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"; 
    } 
}
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post 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)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I don't think it needs repeating, but what the heck.

Vladsun, ddragas++
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Post by Jonah Bron »

Ah. Well, it wasn't tested. :oops:

Don't know much about MySQL connections. I do XML. :wink:
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

I've never been very fond of MySQL's built-in timestamps. Integer Unix timestamps for me!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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 :)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply