Snitz Forums 2000

Snitz Forums 2000

All Forums

Snitz Forums 2000 MOD-Group

MOD Add-On Forum (W/Code)

Member and member posts deletion and cleanup

Note: You must be registered in order to post a reply.
To register, click here. Registration is FREE!
Before posting, make sure you have read this topic!

Screensize:

UserName:

Password:

Format Mode:

Format:

Message:

* HTML is OFF
* Forum Code is ON

Smilies

[quote][i]Originally posted by nathankeller[/i]
[br]If you build a a webpage for admins to delete a unwanted (spam) members member record and associated posts, you can use this code in an sp or elsewhere to clean up afterwards.

This cleanup is necessary because snitz tables are not well normalized for example, whcih member last posted on a certain topic is stored in the topic record rather than just looking to see which member was actually the last to post. This is compramise is usually made to save time...which makes sense because otherwise, if you have a zillion posts, you might get time outs.

After running this cleanup code, then you should probably run the numbers cleanup in the admin panel after that.

=================================================

CREATE PROCEDURE [dbo].[usp_ADMForumMemberDELETE] 
@memberid int
AS

--RUN DELETES ON MEMBER
DELETE  FROM         tblForum_REPLY
WHERE     (R_AUTHOR = @memberid)

DELETE FROM tblForum_TOPICS
WHERE     (T_AUTHOR = @memberid)

DELETE FROM tblForum_A_REPLY
WHERE     (R_AUTHOR = @memberid)

DELETE FROM tblForum_A_TOPICS
WHERE     (T_AUTHOR = @memberid)

DELETE  FROM         tblForum_MEMBERS
WHERE     (MEMBER_ID = @memberid)

--RUN NON NORMALIZED CLEANUP

DECLARE @cid int
DECLARE @fid int
DECLARE @tid int

DECLARE @author int
DECLARE @date varchar(25)
DECLARE @topicid int
DECLARE @replyid int

DECLARE forum_cursor CURSOR FOR
SELECT     CAT_ID, FORUM_ID
FROM         tblForum_FORUM
OPEN forum_cursor

FETCH NEXT FROM forum_cursor
INTO @cid, @fid

SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
 BEGIN

SET @author  = NULL
  SET @date  = NULL
  SET @topicid  = NULL
  SET @replyid  = NULL

DECLARE fadtr_cursor CURSOR FOR
  SELECT TOP 1 TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
  FROM (
  SELECT     TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
  FROM         (SELECT     TOP 1 tblForum_REPLY.R_AUTHOR AS TR_AUTHOR, tblForum_REPLY.R_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID,0) AS TOPIC_ID, 
                        ISNULL(tblForum_REPLY.REPLY_ID,0) AS REPLY_ID
    FROM         tblForum_FORUM LEFT OUTER JOIN
                          tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND 
                          tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID INNER JOIN
                          tblForum_REPLY ON tblForum_TOPICS.CAT_ID = tblForum_REPLY.CAT_ID AND tblForum_TOPICS.FORUM_ID = tblForum_REPLY.FORUM_ID AND 
                          tblForum_TOPICS.TOPIC_ID = tblForum_REPLY.TOPIC_ID
    WHERE     (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid)
    ORDER BY tblForum_REPLY.R_DATE DESC) AS MRFReply
  UNION
  SELECT     TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
  FROM         (SELECT     TOP 1 tblForum_TOPICS.T_AUTHOR AS TR_AUTHOR, tblForum_TOPICS.T_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID,0) AS TOPIC_ID, 0 AS REPLY_ID
    FROM         tblForum_FORUM LEFT OUTER JOIN
                          tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID
    WHERE     (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid)
    ORDER BY tblForum_TOPICS.T_DATE DESC) AS MRFTopic
  ) AS X
  ORDER BY TR_DATE DESC

OPEN fadtr_cursor
  
  FETCH NEXT FROM fadtr_cursor
  INTO @author, @date, @topicid, @replyid
  
  UPDATE    tblForum_FORUM
  SET              F_LAST_POST_AUTHOR = @author, F_LAST_POST = @date, F_LAST_POST_TOPIC_ID = @topicid, F_LAST_POST_REPLY_ID = @replyid
  WHERE     (CAT_ID = @cid) AND (FORUM_ID = @fid)

CLOSE fadtr_cursor
  DEALLOCATE fadtr_cursor

FETCH NEXT FROM forum_cursor
  INTO @cid, @fid

END
SET NOCOUNT OFF
CLOSE forum_cursor
DEALLOCATE forum_cursor

SET @cid = NULL
SET @fid = NULL
SET @tid = NULL

DECLARE topic_cursor CURSOR FOR
SELECT     CAT_ID, FORUM_ID, TOPIC_ID
FROM         tblForum_TOPICS
OPEN topic_cursor

FETCH NEXT FROM topic_cursor
INTO @cid, @fid, @tid

SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
 BEGIN

SET @author  = NULL
  SET @replyid  = NULL

DECLARE tadtr_cursor CURSOR FOR
  SELECT TOP 1 TR_AUTHOR, REPLY_ID
  FROM (
  SELECT     TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
  FROM         (SELECT     TOP 1 tblForum_REPLY.R_AUTHOR AS TR_AUTHOR, tblForum_REPLY.R_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID, 0) AS TOPIC_ID, 
                          ISNULL(tblForum_REPLY.REPLY_ID, 0) AS REPLY_ID, tblForum_TOPICS.TOPIC_ID AS Expr1
    FROM         tblForum_FORUM LEFT OUTER JOIN
                          tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND 
                          tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID INNER JOIN
                          tblForum_REPLY ON tblForum_TOPICS.CAT_ID = tblForum_REPLY.CAT_ID AND tblForum_TOPICS.FORUM_ID = tblForum_REPLY.FORUM_ID AND 
                          tblForum_TOPICS.TOPIC_ID = tblForum_REPLY.TOPIC_ID
    WHERE     (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid) AND (tblForum_TOPICS.TOPIC_ID = @tid)
    ORDER BY tblForum_REPLY.R_DATE DESC) AS MRFReply
  UNION
  SELECT     TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
  FROM         (SELECT     TOP 1 tblForum_TOPICS.T_AUTHOR AS TR_AUTHOR, tblForum_TOPICS.T_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID, 0) AS TOPIC_ID, 
                        0 AS REPLY_ID
  FROM         tblForum_FORUM LEFT OUTER JOIN
                        tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID
  WHERE     (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid) AND (tblForum_TOPICS.TOPIC_ID = @tid)
  ORDER BY tblForum_TOPICS.T_DATE DESC) AS MRFTopic
  ) AS X
  ORDER BY TR_DATE DESC

OPEN tadtr_cursor
  
  FETCH NEXT FROM tadtr_cursor
  INTO @author, @replyid
  
  UPDATE    tblForum_TOPICS
  SET       T_LAST_POST_AUTHOR  = @author, T_LAST_POST_REPLY_ID = @replyid
  WHERE     (CAT_ID = @cid) AND (FORUM_ID = @fid) AND (TOPIC_ID = @tid)

CLOSE tadtr_cursor
  DEALLOCATE tadtr_cursor

FETCH NEXT FROM topic_cursor
  INTO @cid, @fid, @tid

END
SET NOCOUNT OFF
CLOSE topic_cursor
DEALLOCATE topic_cursor
GO

[/quote]

Check here to include your profile signature.
Check here to subscribe to this topic.

T O P I C R E V I E W

nathankeller

Posted - 12 October 2007 : 15:23:32

If you build a a webpage for admins to delete a unwanted (spam) members member record and associated posts, you can use this code in an sp or elsewhere to clean up afterwards.

This cleanup is necessary because snitz tables are not well normalized for example, whcih member last posted on a certain topic is stored in the topic record rather than just looking to see which member was actually the last to post. This is compramise is usually made to save time...which makes sense because otherwise, if you have a zillion posts, you might get time outs.

After running this cleanup code, then you should probably run the numbers cleanup in the admin panel after that.

=================================================

CREATE PROCEDURE [dbo].[usp_ADMForumMemberDELETE]
@memberid int
AS

--RUN DELETES ON MEMBER
DELETE FROM tblForum_REPLY
WHERE (R_AUTHOR = @memberid)

DELETE FROM tblForum_TOPICS
WHERE (T_AUTHOR = @memberid)

DELETE FROM tblForum_A_REPLY
WHERE (R_AUTHOR = @memberid)

DELETE FROM tblForum_A_TOPICS
WHERE (T_AUTHOR = @memberid)

DELETE FROM tblForum_MEMBERS
WHERE (MEMBER_ID = @memberid)

--RUN NON NORMALIZED CLEANUP

DECLARE @cid int
DECLARE @fid int
DECLARE @tid int

DECLARE @author int
DECLARE @date varchar(25)
DECLARE @topicid int
DECLARE @replyid int

DECLARE forum_cursor CURSOR FOR
SELECT CAT_ID, FORUM_ID
FROM tblForum_FORUM
OPEN forum_cursor

FETCH NEXT FROM forum_cursor
INTO @cid, @fid

SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN

SET @author = NULL
SET @date = NULL
SET @topicid = NULL
SET @replyid = NULL

DECLARE fadtr_cursor CURSOR FOR
SELECT TOP 1 TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
FROM (
SELECT TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
FROM (SELECT TOP 1 tblForum_REPLY.R_AUTHOR AS TR_AUTHOR, tblForum_REPLY.R_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID,0) AS TOPIC_ID,
ISNULL(tblForum_REPLY.REPLY_ID,0) AS REPLY_ID
FROM tblForum_FORUM LEFT OUTER JOIN
tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND
tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID INNER JOIN
tblForum_REPLY ON tblForum_TOPICS.CAT_ID = tblForum_REPLY.CAT_ID AND tblForum_TOPICS.FORUM_ID = tblForum_REPLY.FORUM_ID AND
tblForum_TOPICS.TOPIC_ID = tblForum_REPLY.TOPIC_ID
WHERE (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid)
ORDER BY tblForum_REPLY.R_DATE DESC) AS MRFReply
UNION
SELECT TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
FROM (SELECT TOP 1 tblForum_TOPICS.T_AUTHOR AS TR_AUTHOR, tblForum_TOPICS.T_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID,0) AS TOPIC_ID, 0 AS REPLY_ID
FROM tblForum_FORUM LEFT OUTER JOIN
tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID
WHERE (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid)
ORDER BY tblForum_TOPICS.T_DATE DESC) AS MRFTopic
) AS X
ORDER BY TR_DATE DESC

OPEN fadtr_cursor

FETCH NEXT FROM fadtr_cursor
INTO @author, @date, @topicid, @replyid

UPDATE tblForum_FORUM
SET F_LAST_POST_AUTHOR = @author, F_LAST_POST = @date, F_LAST_POST_TOPIC_ID = @topicid, F_LAST_POST_REPLY_ID = @replyid
WHERE (CAT_ID = @cid) AND (FORUM_ID = @fid)

CLOSE fadtr_cursor
DEALLOCATE fadtr_cursor

FETCH NEXT FROM forum_cursor
INTO @cid, @fid

END
SET NOCOUNT OFF
CLOSE forum_cursor
DEALLOCATE forum_cursor

SET @cid = NULL
SET @fid = NULL
SET @tid = NULL

DECLARE topic_cursor CURSOR FOR
SELECT CAT_ID, FORUM_ID, TOPIC_ID
FROM tblForum_TOPICS
OPEN topic_cursor

FETCH NEXT FROM topic_cursor
INTO @cid, @fid, @tid

SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN

SET @author = NULL
SET @replyid = NULL

DECLARE tadtr_cursor CURSOR FOR
SELECT TOP 1 TR_AUTHOR, REPLY_ID
FROM (
SELECT TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
FROM (SELECT TOP 1 tblForum_REPLY.R_AUTHOR AS TR_AUTHOR, tblForum_REPLY.R_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID, 0) AS TOPIC_ID,
ISNULL(tblForum_REPLY.REPLY_ID, 0) AS REPLY_ID, tblForum_TOPICS.TOPIC_ID AS Expr1
FROM tblForum_FORUM LEFT OUTER JOIN
tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND
tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID INNER JOIN
tblForum_REPLY ON tblForum_TOPICS.CAT_ID = tblForum_REPLY.CAT_ID AND tblForum_TOPICS.FORUM_ID = tblForum_REPLY.FORUM_ID AND
tblForum_TOPICS.TOPIC_ID = tblForum_REPLY.TOPIC_ID
WHERE (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid) AND (tblForum_TOPICS.TOPIC_ID = @tid)
ORDER BY tblForum_REPLY.R_DATE DESC) AS MRFReply
UNION
SELECT TR_AUTHOR,TR_DATE, TOPIC_ID, REPLY_ID
FROM (SELECT TOP 1 tblForum_TOPICS.T_AUTHOR AS TR_AUTHOR, tblForum_TOPICS.T_DATE AS TR_DATE, ISNULL(tblForum_TOPICS.TOPIC_ID, 0) AS TOPIC_ID,
0 AS REPLY_ID
FROM tblForum_FORUM LEFT OUTER JOIN
tblForum_TOPICS ON tblForum_FORUM.CAT_ID = tblForum_TOPICS.CAT_ID AND tblForum_FORUM.FORUM_ID = tblForum_TOPICS.FORUM_ID
WHERE (tblForum_FORUM.CAT_ID = @cid) AND (tblForum_FORUM.FORUM_ID = @fid) AND (tblForum_TOPICS.TOPIC_ID = @tid)
ORDER BY tblForum_TOPICS.T_DATE DESC) AS MRFTopic
) AS X
ORDER BY TR_DATE DESC

OPEN tadtr_cursor

FETCH NEXT FROM tadtr_cursor
INTO @author, @replyid

UPDATE tblForum_TOPICS
SET T_LAST_POST_AUTHOR = @author, T_LAST_POST_REPLY_ID = @replyid
WHERE (CAT_ID = @cid) AND (FORUM_ID = @fid) AND (TOPIC_ID = @tid)

CLOSE tadtr_cursor
DEALLOCATE tadtr_cursor

FETCH NEXT FROM topic_cursor
INTO @cid, @fid, @tid

END
SET NOCOUNT OFF
CLOSE topic_cursor
DEALLOCATE topic_cursor
GO

Snitz Forums 2000

This page was generated in 0.07 seconds.