CREATE PROC GetChildren 
(@ParentID int, @forumID int, @PostLevel int, @ParentPostKey varchar(200), @forumRequestedPage int, @forumMessagesPerPage int) AS

SET NOCOUNT ON

DECLARE @NextLevel int, @Counter int, @PostKey varchar(200), @jumpRows int

SET @Counter = 1

-- Build a cursor to loop through all the kids of this post based on the fact if it's a root post or not
IF @ParentID = 0 BEGIN
	DECLARE c1 CURSOR LOCAL SCROLL READ_ONLY FOR
	SELECT messageID 
	FROM messages
	WHERE messageParent = @ParentID and messageForum = @forumID and deleted=0 and authorized=1
	ORDER BY messageTime DESC	
	
	-- since this is a root posts query, gets only the rows that are relevant to the requested page
	OPEN c1
	IF @forumRequestedPage > 1 
	BEGIN
		SET @jumpRows = ((@forumRequestedPage - 1) * @forumMessagesPerPage) + 1
		FETCH ABSOLUTE @jumpRows FROM c1
		into @ParentID
	END
	ELSE BEGIN
		FETCH NEXT FROM c1
		INTO @ParentID
	END

	-- this loop runs on all parents until there are no more on this page or at all
	WHILE @@FETCH_STATUS = 0 AND @Counter <= @forumMessagesPerPage
	BEGIN
	
	  -- Put this record in the temp table
	  INSERT #NestedPosts (PostID, PostKey, messageLevel) VALUES (@ParentID, @PostKey, @PostLevel)
	
	  SET @NextLevel = @PostLevel + 1
	  
	  -- Process all the children for this post
	  EXEC GetChildren @ParentID, @forumID, @NextLevel, @PostKey, @forumRequestedPage, @forumMessagesPerPage
	 
	  SET @Counter = @Counter + 1
	 
	  -- And get the next record at this level
	  FETCH NEXT FROM c1
	  INTO @ParentID 
	END
END
ELSE BEGIN
	DECLARE c1 CURSOR LOCAL SCROLL READ_ONLY FOR
	SELECT messageID 
	FROM messages
	WHERE messageParent = @ParentID and messageForum = @forumID and deleted=0 and authorized=1
	-- Here we can put ASC so that the inner thread will be ordered by the order of insertion and not new comes first
	ORDER BY messageTime DESC
	
	-- gets all the kids for this post since it's not a root one, no need for jumping
	OPEN c1
	FETCH NEXT FROM c1
	INTO @ParentID

	WHILE @@FETCH_STATUS = 0
	BEGIN
	
	  -- Put this record in the temp table
	  INSERT #NestedPosts (PostID, PostKey, messageLevel) VALUES (@ParentID, @PostKey, @PostLevel)
	
	  SET @NextLevel = @PostLevel + 1
	  
	  -- Process all the children for this post
	  EXEC GetChildren @ParentID, @forumID, @NextLevel, @PostKey, @forumRequestedPage, @forumMessagesPerPage
	 
	  SET @Counter = @Counter + 1
	 
	  -- And get the next record at this level
	  FETCH NEXT FROM c1
	  INTO @ParentID 
	END
END	

CLOSE c1
DEALLOCATE c1

SET NOCOUNT OFF
GO

quote:

---

Originally posted by famdylan

Okay on the same type of question say I wanted to move one of the lower replyies up to a upper level or move it to another thread how would I update all the children? Can I do this in the database or do I need to do some code in my application?

---

quote:

---

Originally posted by minou30

Having difficult time translating to MYSQL. Can anyone help?

---