Any MySQL experts? Help is needed

[TalkVirginia]

Can anyone help me track down a couple issues with my Email Reminders for inactive users mod? It's been reported that it's sending out emails to members when it shouldn't be, and not sending email when it should be which doesn't make sense.

This SQL statement is supposed to grab all members that have been inactive over x number of days based on their last activity date and have never been sent an email, as long as they can receive emails from the administrator and have not opted out. There is a link in the reminder email which the inactive member may click on to optout.

    $sql = "SELECT userid, username, email, usergroupid, ".
           "FROM_UNIXTIME(lastvisit) lastvisit, ". 
           "FROM_UNIXTIME(lastactivity) lastactivity, ". 
           "CASE WHEN lastpost = 0 THEN '' ELSE FROM_UNIXTIME(lastpost) END AS lastpost, ". 
           "CASE WHEN rmEmailDate = 0 THEN '' ELSE FROM_UNIXTIME(rmEmailDate) END AS lastreminder, ".
           "TIMESTAMPDIFF(DAY, FROM_UNIXTIME(lastvisit), FROM_UNIXTIME(UNIX_TIMESTAMP())) days_since_lastvisit, ".
           "TIMESTAMPDIFF(DAY, FROM_UNIXTIME(lastactivity), FROM_UNIXTIME(UNIX_TIMESTAMP())) days_since_lastactivity, ".
           "CASE WHEN lastpost = 0 THEN 0 ELSE TIMESTAMPDIFF(DAY, FROM_UNIXTIME(lastpost), FROM_UNIXTIME(UNIX_TIMESTAMP())) END AS days_since_lastpost, ".
           "CASE WHEN rmEmailDate = 0 THEN 0 ELSE TIMESTAMPDIFF(DAY, FROM_UNIXTIME(rmEmailDate), FROM_UNIXTIME(UNIX_TIMESTAMP())) END AS days_since_lastemail, ".
           "rmoptout ".
           "FROM " . TABLE_PREFIX . "user ".  
           "WHERE TIMESTAMPDIFF(DAY, FROM_UNIXTIME(lastactivity), FROM_UNIXTIME(UNIX_TIMESTAMP())) >= ". $graceperiod . " ". 
           "AND usergroupid IN ('" . $usergroups . "') ".
           "AND rmEmailCount = 0 ".
           "AND options & 16 ". 
           "AND rmoptout = 0 ";

$graceperiod and the $usergroups are set in the mod settings screen.

$usergroups will be a comma-delimited list of integers (ex. 1','2','3','4 )

If anyone can help, I'll gladly give you credit on the mod page here.

Thanks in advance,
Jim

[Farcaster]

Could you be more specific about what users are receiving emails that aren't supposed to and the other way around? Is that based on one of the filters, multiple, or all?

One shortcut that I can think of that might help is to use the unix timestamp natively for your comparisons. Example: UNIX_TIMESTAMP() - user_lastactivity is the difference in seconds between the two dates. Divide that by 86400 and you have your full days.

[kh99]

I'm not a mysql expert, but: at the beginning you have

FROM_UNIXTIME(lastactivity) lastactivity

then in the WHERE you have another FROM_UNIXTIME(lastactivity). Wouldn't that end up doing a FROM_UNIXTIME() on something that's already been converted?

Or maybe not. Edit: no, I guess you can't use column aliases in a WHERE, so it has to refer to the table column. Never mind.

[TalkVirginia]

[QUOTE=Farcaster;2204733]Could you be more specific about what users are receiving emails that aren't supposed to and the other way around? Is that based on one of the filters, multiple, or all?
[/?QUOTE]

The way I understand it is members that are not inactive are getting emails and members that are known to be inactive are not being found, however, I have reason to believe that the people this is happening to have not updated their copy of the addon to the recent changes because the addon has been working on my development site and my live site.

Originally Posted by Farcaster

One shortcut that I can think of that might help is to use the unix timestamp natively for your comparisons. Example: UNIX_TIMESTAMP() - user_lastactivity is the difference in seconds between the two dates. Divide that by 86400 and you have your full days.

That is what this is doing. It's just using the built-in MYSQL functions instead.
TIMESTAMPDIFF(DAY, FROM_UNIXTIME(lastactivity), FROM_UNIXTIME(UNIX_TIMESTAMP())) >= ". $graceperiod

What this is doing is converting the Unix time stamp to a date then subtracting the current date/time from the date the member was last active on site. It then gets the difference in days comparing that with the integer value entered in the addon settings screen (graceperiod).

[Farcaster]

Originally Posted by TalkVirginia

The way I understand it is members that are not inactive are getting emails and members that are known to be inactive are not being found, however, I have reason to believe that the people this is happening to have not updated their copy of the addon to the recent changes because the addon has been working on my development site and my live site.

Looking at the code you pasted, I don't see any reason why that might might get the wrong subset of data based on the last activity date. Do you have the SQL from the previous version that might have had a problem?

Originally Posted by TalkVirginia

What this is doing is converting the Unix time stamp to a date then subtracting the current date/time from the date the member was last active on site. It then gets the difference in days comparing that with the integer value entered in the addon settings screen (graceperiod).

I understand how the function is working, but what I am saying is that you are converting two unixtime values and then comparing them when you could do it manually, which would allow your query to take advantage of any indexing available on that field instead of doing a table scan. It would also be quicker since it doesn't have to run each value through a function. Of course, with most boards and the relatively small amount of rows you are talking about, this probably wouldn't be noticeable.