Recount items in the database.

Allan

Hello, I've been using vanilla for a while, and have never really had any problems with it. But recently I had to convert a phpbb forum over to vanilla. I managed to get all the posts there, but there was alot of left over spams. I ran a " DELETE FROM `LUM_Comment` WHERE `LUM_Comment`.`BODY` LIKE '%<a href%' " on the database and it removed 99% of them, but now the forum counts are all out of wack. There are topics with no comments, that still say 1 - 26 for the number of comments in them, and there are miscounted numbers all over. Is there an addon, or an easy way to recount all the information, and remove topics with no posts. I could write up a php file to do so, but figured I would ask first. This is the first time I've needed to recount so bad, there was over 10000 spams in the database ( converted over from phpbb1 to phpbb2 to vanilla ). If anybody wants to see it, here is the forum: http://forum.basementbar.net/ . I am still working on a some of the html/css to make it up to standards, and not have it look stupid in IE6 ( 60-70% of basementbar's visitors, and im sure the other 30-40% is just me and the owner of the site ). Any help would be great! We have no addons installed as of yet, but the delete comment addon looks good, as the owner of the site is having a hard time adjusting to the way vanilla functions. Thanks! Allan.

WallPhone

Vanilla does have a RecountComments function in either the comment manager or the discussion manager ... I can't remember which it is (code for these is in library/vanilla/)

Vanilla will run that code if you delete any comment through the interface and then undelete it. It might be easier to construct a loop that calls the function for each discussion.

Allan

Thank you for you help, But I ended up doing it 'the hard way'. After sleeping for the night, I awoke with a simple method to fix everything.

I wrote a function, using the php class for askismet to pass all the posts to its filter, then deleted all the posts it knew was spam. But this still left me miscounted. So I wrote up a small function that might be of use to other php developers, I can't post the source to the database layer script I am using, as its really modifyed, but this should convert over really easy to regular php-mysql calls.

Here it is, in all its glory:

$sql = 'SELECT * FROM `LUM_Comment`';
    echo '<pre>' . $sql . '</pre><hr>';
        if ( !($result1 = $db->sql_query($sql)) )
        {
            die('SQL Error' .  __LINE__ . __FILE__ . $sql);
        }
       
        while( $row = $db->sql_fetchrow($result1) )
        {
            $disc[$row['DiscussionID']]++;
        }


$sql = 'SELECT * FROM `LUM_Discussion` ';
    echo '<pre>' . $sql . '</pre><hr>';
        if ( !($result1 = $db->sql_query($sql)) )
        {
            die('SQL Error' .  __LINE__ . __FILE__ . $sql);
        }
       
        while( $row = $db->sql_fetchrow($result1) )
        {
            if ($disc[$row['DiscussionID']] > 0) {
                $sql6 = "UPDATE `LUM_Discussion` SET `CountComments` = '" . $disc[$row['DiscussionID']] . "' WHERE `LUM_Discussion`.`DiscussionID` =" . $row['DiscussionID'] . " LIMIT 1 ;";
                echo '<pre>' . $sql6 . '</pre><hr>';
                $db->sql_query($sql6);
            } else {
                $sql7 = 'DELETE FROM `LUM_Discussion` WHERE `LUM_Discussion`.`DiscussionID` = ' . $row['DiscussionID'] . ' LIMIT 1';
                echo '<pre>' . $sql7 . '</pre><hr>';
                $db->sql_query($sql7);
            }
        }

Sheila

WallphoneVanilla does have a RecountComments function in either the comment manager or the discussion manager ... I can't remember which it is (code for these is in library/vanilla/)

Vanilla will run that code if you delete any comment through the interface and then undelete it. It might be easier to construct a loop that calls the function for each discussion.

I'm having similar problems after converting and importing from Invision with 100.000+ posts. I'd rather let the database "rest" for a while and force recount with Vanillas functions. So what is the best way to do it?

Importing brought up also few hundred empty ghost topics, dated 5 years back and which most likely are result of original site's beta stage when it was launched. Only way to get rid of these was to delete them straight from the database and under these circumstances, I would be more surprized, if there was not any miscalculation problems.

Tested solution mentioned here by Wallphone but that was not a working solution to this case. Hiding and unhiding comment from a thread resulted losing the whole commentcount so a topic for example with 200 comments now prints in a discussion view 0 comments thou all those 200 still exist in the thread. Real deleting of a comment in a thread does not empty comment count but it does not either help. No whispers exist so that can't be a thing to blame.

CurtisOden

I had this problem after I imported a few 100k comments. Run these queries after your imports (using your favorite Query editor, wrapped in PHP, or build and share a plugin):


//UPDATE THE DISCUSSION COMMENT COUNTS
update GDN_Discussion Set GDN_Discussion.CountComments = ((Select Count(GDN_Comment.CommentID) from GDN_Comment
where GDN_Comment.DiscussionID = GDN_Discussion.DiscussionID) + 1)


//UPDATE THE CATEGORY DISCUSSION COUNTS
update GDN_Category Set GDN_Category.CountDiscussions = (Select Count(GDN_Discussion.DiscussionID)
from GDN_Discussion where GDN_Discussion.CategoryID = GDN_Category.CategoryID)

//UPDATE THE CATEGORY COMMENT COUNTS
update GDN_Category Set GDN_Category.CountComments = (Select SUM(GDN_Discussion.CountComments)
from GDN_Discussion where GDN_Discussion.CategoryID = GDN_Category.CategoryID)

luc

The original discussion is abour vanilla1, you're talking about vanilla2.