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

4
  • 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
    Commented Feb 11, 2013 at 10:01
  • 1
    Yup it was a really bad idea. Thanks for the info though.
    – Binny
    Commented Feb 11, 2013 at 10:21
  • Any idea how to get out of this mess?
    – Binny
    Commented Feb 11, 2013 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
    Commented Feb 11, 2013 at 10:27

3 Answers 3

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!

3
  • 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
    Commented May 7, 2013 at 3:30
  • 4
    I think TRUNCATE TABLE comments will be TRUNCATE TABLE comment
    – trante
    Commented Sep 18, 2013 at 22:59
  • -1: TRUNCATE TABLE comment will delete every comment, not just unapproved spam comments
    – User
    Commented Oct 11, 2016 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 and acknowledge you have read our privacy policy.

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