CODEWITHSUNDEEP
phpmysqltimestamp
2by
2by

Reputation: 1093

Select rows from MySQL table where PHP timestamp is older than X

I have a user table, where users need to be approved, i want to show users who are not approved and is registered more than 7 days ago.

My user_regdate is a timestamp created with php time() function.

This is what i try, it does not works:

mysql_query("select * from users WHERE user_regdate < now() - interval 7 day AND approved='0' order by id;");

Thanks

Upvotes: 6

Views: 14615

Answers (4)

KenL
KenL

Reputation: 875

Try this;

select * 
from users 
WHERE DATE_SUB(user_regdate,INTERVAL 7 DAY) 
AND approved='0' 
order by id;

Upvotes: 0

gdub
gdub

Reputation: 301

php's time() function outputs a unix timestamp (the number of seconds since January 1 1970). MySQL's now() function outputs a formatted date (like 2011-6-9 12:45:34)... so I don't think you can compare them like that.

Try using the unix timestamp, minus 7 days, instead of now() in your query:

 $7_days_ago = time() - (7 * 24 * 60 * 60);
 mysql_query("select * from users WHERE user_regdate <" . $7_days_ago . " AND approved='0' order by id;");

Upvotes: 1

Marc B
Marc B

Reputation: 360562

PHP's timstamps are a simple integer, whereas MySQL's now() returns a datetime value. Most likely this will fix up the query:

SELECT ... WHERE user_regdate < unix_timestamp(now() - interval 7 day)) ...

Basically, without the unix_timstamp() call, you're comparing apples and oranges.

Upvotes: 16

Mick Hansen
Mick Hansen

Reputation: 2694

Primitive solution at best, but im not the best at MySQL time calculation

$timestamp = strtotime("-7 days");
mysql_query("SELECT * FROM users WHERE user_regdate < $timestamp AND approved = 0 ORDER BY id");

Upvotes: 3

Related Questions