by JBrooks
5. September 2010 14:21
I use YetAnotherForum (YAF) for my Forums
here. Like others, I get hundreds of spam users created each month. I wrote the following SQL to delete any user that hasn’t visited in the last day, never posted or emailed a message, isn’t the last user on a forum and isn’t in the Active list.
-- first delete the UserGroup rows.
DELETE FROM dbo.yaf_UserGroup
WHERE UserID in
(SELECT UserID
FROM dbo.yaf_User
WHERE LastVisit < getdate() -1
AND NumPosts = 0)
AND UserID NOT IN
(SELECT UserID
FROM dbo.yaf_Active)
AND UserID NOT IN
(SELECT LastUserID
FROM dbo.yaf_Forum
WHERE LastUserID is not null)
AND UserID NOT IN
(SELECT FromUserId
FROM dbo.yaf_PMessage)
--
-- now delete the User rows.
--
DELETE FROM dbo.yaf_User
WHERE LastVisit < getdate() -1
AND NumPosts = 0
AND UserID NOT IN
(SELECT UserID
FROM dbo.yaf_Active)
AND UserID NOT IN
(SELECT LastUserID
FROM dbo.yaf_Forum
WHERE LastUserID IS NOT NULL)
AND UserID NOT IN
(SELECT FromUserId
FROM dbo.yaf_PMessage)
Subscribe