Code: Select all
SELECT user_email FROM phpbb_users WHERE user_type <>2 AND user_lastpost_time > UNIX_TIMESTAMP() - 31536000
phpbb_
change accordingly.Code: Select all
SELECT DISTINCT u.user_email -- Fold identical address into being listed only once
FROM phpbb_users u
LEFT JOIN phpbb_banlist bu ON bu.ban_userid= u.user_id -- Exclude bans per member ID
LEFT JOIN phpbb_banlist be ON be.ban_email= u.user_email -- Exclude bans per e-mail address
WHERE u.user_type IN( 0, 3 ) -- Only normal and founders, neither deactivated nor ignored
AND u.user_email<> '' -- Empty addresses might occur
AND u.user_allow_massemail<> 0 -- Only members who opted in
AND coalesce( bu.ban_start, unix_timestamp() )<= unix_timestamp()
AND coalesce( bu.ban_end, unix_timestamp()+ 1 )>= unix_timestamp()
AND (bu.ban_userid IS NULL OR bu.ban_exclude<> 0)
AND coalesce( be.ban_start, unix_timestamp() )<= unix_timestamp()
AND coalesce( be.ban_end, unix_timestamp()+ 1 )>= unix_timestamp()
AND (be.ban_email IS NULL OR be.ban_exclude<> 0)
ORDER BY u.user_email
NOW()
from the post above is wrong when used arithmetically and in MySQL/MariaDB. Its intention was to exclude all members which haven't posted for more than 365 days (which doesn't honor those still visiting daily).Banned users are not (necessarily) inactive. TheI've been asked to create a list from the forum database of all active members' email addresses.
<>2
eliminates inactive users.How so?The NOW() from the post above is wrong when used arithmetically and in MySQL/MariaDB.
Which is what the OP asked for. Posters, not visitors:Its intention was to exclude all members which haven't posted for more than 365 days (which doesn't honor those still visiting daily).
I'd like to be able to make sure that only people who have logged in and posted in the last year get the email,
NOW()
in any SQL for phpBB - did you use that before?