4

I received a lot of spam comments in one of my websites running on Drupal 7. I googled for a solution to delete them in bulk and came up on

DELETE FROM comment WERE status = 0

The query worked but the database size did not shrink significantly and I realized out that in addition to the 'comment' table there was another table called

 field_data_comment_body

which housed the body of the comment.

I would like to know which all tables are related to comments in Drupal 7

  • 1
    Comments are entities, and the body is a field. Deleting comments directly from the comment table is a very, very bad idea, you should use the API functions (e.g. comment_delete()) – Clive Feb 11 '13 at 10:01
  • 1
    Yup it was a really bad idea. Thanks for the info though. – Binny Feb 11 '13 at 10:21
  • Any idea how to get out of this mess? – Binny Feb 11 '13 at 10:26
  • Restoring your latest db backup would be the first step, then you can write a script to delete the comments 'properly'. If you're struggling with that script just ask another question here, someone will be able to help you out – Clive Feb 11 '13 at 10:27
1

Run these on your PhpMyAdmin:

TRUNCATE TABLE comment;
TRUNCATE TABLE field_data_comment_body;
UPDATE node_comment_statistics SET comment_count = 0;
TRUNCATE TABLE field_revision_comment_body;

Take care: all the comments will be deleted!

  • Yea this seems to be a good solution too for deleting all the comments. However it is not feasible if you want to preserve a few comments. – Binny May 7 '13 at 3:30
  • 4
    I think TRUNCATE TABLE comments will be TRUNCATE TABLE comment – trante Sep 18 '13 at 22:59
  • -1: TRUNCATE TABLE comment will delete every comment, not just unapproved spam comments – User Oct 11 '16 at 12:15
2

I know following tables. 1. comment 2. field_data_comment_body 3. field_revision_comment_body 4. node_comment_statistics

Beside that, I think, comments are also stored in any other tables installed by modules, and linked to comment modules.

0

So, the question is which tables are related to comments in Drupal 7? Not which SQL commands to run ;-)

If you haven't added any fields to the comment entity (for any content type), the correct tables has already been mentioned:

  • comment
  • field_data_comment_body
  • field_revision_comment_body
  • node_comment_statistics

For each field you add to the comment entity you get two more tables.

Example: For a content type "blog", you have added an email field to the comment. In other words you find a field with machine name "field_comment_email" (or similar) on admin/structure/types/manage/blog/comment/fields. Then you have two more tables:

  • field_data_field_comment_email
  • field_revision_field_comment_email

This quickly gets messy. To repeat what @Clive said ;-)

Deleting comments directly from the comment table is a very, very bad idea, you should use the API functions.

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.