Skip to:
Content
Pages
Categories
Search
Top
Bottom

rebuilding topics DB


  • _ck_
    Participant

    @_ck_

    I had to use phpmyadmin to delete a few dozen posts by a spammer.

    How can I rebuild the topics DB so the front page (and sub-forums) are accurate?

    Or is the answer no such tool exists and I’ll have to write one?

Viewing 5 replies - 1 through 5 (of 5 total)
  • Why did you have to use phpMyAdmin to delete posts? That makes no sense. Are you unable to log in?

    Have you tried the ‘Recount’ feature? bb-Admin > Site Management > Recount, that is, if you can log in, but I should think you can since that would be the more serious problem but I haven’t seen you post about it.

    I suggest that you mark spam posts as spam in future. At least in Kakumei there should be a link to do so at the bottom of the post, by the post age, if the user is logged in and of high enough authority.


    _ck_
    Participant

    @_ck_

    BBpress has no mass search/delete feature, unlike several wordpress plugins that are available. Even the upcoming search feature that was posting on the blog doesn’t seem to have an “action” ability, just search, which doesn’t make sense, so maybe there will be more to it.

    Trying searching and deleting 50+ posts by hand, not worth it.

    Recount will only update counts, not pointers.

    I am just about finished with a new function to do this however.

    Right now it only updates the last poster id and name, the trickiest part was figuring out the proper mysql expression to grab the last item while grouping by topic_id. Took an hour of research.

    But in fact my routine could be extended further to rebuild the entire topics database from scratch if need be since all the data is in the posts and it’s just kept in the topics table for speed.

    I just find it surprising there isn’t such a routine already and I am hoping I am not duplicating efforts already done.


    _ck_
    Participant

    @_ck_

    tested working – at least for my setup:

    function rebuild_topics() {
    global $bbdb;
    // currently only rebuilds last post pointers, last poster name, last poster id, last topic post date
    // todo: rebuild entire topics table
    // warnings: hard coded for WP user table - backup your DB before using
    // run the built-in post/topic recount first

    echo "t<br>n";
    if ( $topics = (array) $bbdb->get_results("SELECT topic_id,post_id,poster_id,post_time FROM <code>bb_posts</code> a WHERE post_position=(SELECT MAX(post_position) FROM <code>bb_posts</code> WHERE topic_id=a.topic_id) AND post_status = '0' GROUP BY topic_id ") ) :
    echo "tt" . __('Calculating last posters for each topic...') . "n";
    foreach ($topics as $t) {
    echo $t->topic_id."... ";
    $user_login=$bbdb->get_var("SELECT user_login FROM wp_users WHERE ID='$t->poster_id'");
    $bbdb->query("UPDATE $bbdb->topics SET topic_time='$t->post_time',topic_last_poster= '$t->poster_id', topic_last_poster_name='$user_login', topic_last_post_id='$t->post_id' WHERE topic_id = '$t->topic_id'");
    }
    endif;
    echo "<br> tt" . __('Done calculating last posters.');
    }

    If you use $bbdb->users you should get the appropriate table name instead.


    _ck_
    Participant

    @_ck_

    Yeah I was just being quick and lazy. I guess I could fetch the proper table prefix and make it work for anyone.

    Just shared it in the hope someone more skilled would make a more professional function.

    Without much more work, it could be made to rebuild the entire topics table from scratch using all the posts table data (well except for the topic title which apparently is not stored anywhere else? Would it have been too much to have varchar post_title in there for eventual ability to allow post replies to change the title without a plugin/extra table?)

    It also would allow the “real delete” ability several people have been asking for.

Viewing 5 replies - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.