Reminder Email Funtion based on the last login

[sammry]

lastlogin is getting saved as time() in my mysql table and it is varchar mode.

querying the database to send out emails to remind my users that they have not logged in from past 30 days

SELECT * FROM members WHERE lastlogin = DATE_SUB(CURDATE(), INTERVAL 30 DAY)

i tried with several options, but not able to query exactly as i need. for example,

SELECT * FROM members WHERE lastlogin < DATE_SUB(CURDATE(), INTERVAL 30 DAY)

SELECT * FROM members WHERE lastlogin < DATE_ADD(CURDATE(), INTERVAL 30 DAY)

can some one help me on this to achieve this function please.

[diafol]

In which format are you storing your dates?

[sammry]

update the table with .time().

[diafol]

so unix timestamp in that case? date_sub/add use the Y-m-d H:i:s format. You can't compare apples and oranges.

BTW - if storing unix timestamp - why are you using varchar? Should be INT

[sammry]

this script is bought from the market place, hard to understand their coding, hence seeking help out here. Can you state me an example if you dont mind

[diafol]

SELECT * FROM members WHERE DATE_ADD(FROM_UNIXTIME(lastlogin), INTERVAL 30 DAY) < CURDATE()

not tested

[sammry]

thanks for your great help, its working perfectly fine. thanks so much.

just need to understand the query,

DATE_ADD(FROM_UNIXTIME(lastlogin), INTERVAL 30 DAY)

: this adds +30 day interval and then it finds the member who is less than (CURDATE()) current date login.

please correct me if am wrong.

Or is it had to be

DATE_SUB if i have to send the mail who has not been logged in since 30 days.

[diafol]

date_add or date_sub doesn't matter - can use either with > / < or change places of lastlogin and CURDATE()

if solved, mark it so with the link below

[sammry]

sure, i will play around with it and a great thanks to you for helping me to solve this