0

I'm trying to add an extra column to the comments database which will store the user ID of the post/page author. This is part of a plugin which will display new comments in reply to either a post or another comment. As far as I can see Wordpress doesn't have this functionality built in but rather places comments in one large group. I want users to quickly see only comments relevant to themselves.

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
global $wpdb;
$wpdb->show_errors();       
$query = "ALTER TABLE $wpdb->comments ADD replyto_who bigint(20)";
if ( maybe_add_column($wpdb->comments,'replyto_who',$query) ) {
    $query = "SELECT comment_post_ID,comment_ID FROM $wpdb->comments WHERE replyto_who IS NULL";
    $results = $wpdb->get_results($query);
    foreach ( $results as $result ) {
        $user_id = $wpdb->get_var( "SELECT post_author FROM $wpdb->posts WHERE ID = $result->comment_post_ID" );
        if ( isset($user_id) )
            $wpdb->query("UPDATE $wpdb->comments SET replyto_who='$user_id' WHERE comment_ID=$result->comment_ID");
    }
}

Please note above code is only for testing.

The problem I'm having is that the new entry is always being given a value of '1' after a new comment is entered even though the above code hasn't run. Looking at the table structure using phpMyAdmin I see the column has Null=yes and default=NULL. Being new to SQL I'm not quite sure what I'm doing.

What have I done wrong?

  • I would not recommend this. See: wordpress.stackexchange.com/a/111835/21376 – s_ha_dum Sep 7 '13 at 16:02
  • @s_ha_dum Is there an alternative you could recommend? I honestly believe this column should be part of the core. As it stands, to find the post author requires first fetching the comment_post_ID then an additional lookup to find the post_author. Thinking about it, I suppose it could be done with INNER MERGE. – Twifty Sep 7 '13 at 23:18
  • If you think it needs to be in the Core, then submit a ticket preferably with a patch and hope for the best, but don't modify a Core table on your own. – s_ha_dum Sep 7 '13 at 23:33
0

I'm not sure why you're getting a 1. Your code says that you should not. However, it seems that what you're doing is unnecessary and generally ill-advised. Modifying existing tables is usually not needed.

The wp_comments table contains a comment_parent and user_id column.

So if I reply to a comment, it's immediate parent ID is in comment_parent and that comments author ID is in user_id if they are logged in (0 otherwise).

You could also use the wp_commentmeta to store this information if I've misunderstood.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.