Sending to one after checking the date

I am thinking about setting a number (days) in my admin panel which is then used to check if a user haven’t been logged in for a while.
So if a user haven’t been logged in after more than 90 days I will send them a reminder telling them it’s been a while since they were logged in.
When a user on my website is logging out it will run the script and look at my user table and search for just one user that haven’t been logged in since that many days. I have stored the last date the users logged in as a DATE.

Is there a way to check this easy? I guess it should be something calculating now() and the number of dates back. But not sure how to do it the easiest way. Or the correct way.

So when a user is logging out it will look in my user table and pick one that haven’t been logged in since a date > 90 days from today.

Maybe it would be better to run it as a cron job that runs daily with a query for last log-outs dated 90 days ago.

1 Like

Yes. That is a great thing, but on the web server I have the web site I can’t have cron jobs. That’s why I came up with this little clever idea. :wink:

You probably want to add a column that stores the date that the user was last sent a reminder. So you can select all users that haven’t been logged in in over 90 days AND haven’t been sent a reminder already. Otherwise you’ll probably end up just sending the reminder to the same user over and over again.

You could have your query something like

where (date(now()) - date(lastloggedin))>90 and warningsent = NULL

or set a date of last warning if you want 120 days, etc.

Yes. I thought about having a column with the reminder as well, so they won’t be picked twice. I will make that column using a name as well so I know what reminder they got.

Thank you for that query. Looks just like what I am looking for. Will try it next time I’m working on that one.

No that one didn’t work. It doesn’t seem to subtract the days. And I’m not sure how to check what result that code would give me to find the problem.

I have one field named lastloggedin which stores the date like this 0000-00-00

So I tried this:

"SELECT * FROM guest_table WHERE (date(now()) - date(lastloggedin)) > 90"

But when I try this it will not give me the correct ones. Should the result really be 90 when having the date like this?

Another page I looked at suggests you might want to try something like this:

SELECT * FROM guest_table WHERE lastloggedin <= NOW() - INTERVAL 90 DAY

or there also seems to be

SELECT * FROM guest_table WHERE lastloggedin <= DATE_SUB(NOW(), INTERVAL 90 DAY)

I haven’t got a local database with suitable data in it to test this unfortunately.

Actually I solved now after taking a little break and fresh air. This is how I did it.

$thelimit = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d")-90,   date("Y")));
$sql = "SELECT * FROM guest_table WHERE lastloggedin < '$thelimit'";
$result = mysqli_query($GLOBALS["___mysqli_ston"], $sql); 
$myrow = mysqli_fetch_array($result); ```

Good to hear. I have an idea the first line can become

$thelimit = date("Y-m-d", strtotime("-90 days"));

for simplicity.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.