Greetings.

I am a happy user of Mollom: it doesn't let much comments in the approval queue.

However, I still have dozens of pages of unapproved comments since before I used Mollom and now I need to delete them.

So how can one delete all the unapproved comments?

- I searched Google for projects and other pages about deleting unapproved comments or clearing the approval queue
- I clicked-through dozens of modules in the Content section for 6.x and searched for comments in the modules
- I also used the site search

But I didn't find anything, except a single unanswered post with the same question.

Thus, the question remains: how does one delete all the unapproved comments (and trackbacks, while we are at it)?

Thank you :)

Comments

roper.’s picture

Quick and dirty approach:

Make a new page node, set it to php, put the following:


db_query("DELETE FROM {comments} WHERE status = 1");

I'm not an expert with SQL so maybe make a backup of that table before you do it.

Yura Filimonov’s picture

That didn't do a thing, unfortunately ;)

Maybe some kind soul would do a plugin for this? ;)

likewhoa’s picture

that will probably work on a D6 site but for a D7 site it's db_query('DELETE FROM comment WHERE status = 0'); best to run the above from a custom module since phpfilter is frown upon on most circles.

Bending technology to fit business

scor’s picture

While this solution may work well for Drupal 6, it is a bad idea for Drupal 7: it will make the comments disappear from the Drupal UI, but given that the comments body field data is stored in 2 separate tables (field_revision_comment_body and field_data_comment_body), it will leave the comment bodies untouched in those field tables, bloating your database size for no reason. Cleaning that up later on will be more work than deleting those comments properly in the first place. You could write some code to first query the comments table for those unapproved comment ids and then use comment_delete_multiple($cids). An easier solution if you don't want to write code is to install https://www.drupal.org/project/views_bulk_operations, create a view of unapproved comments, add a VBO field to your view, and enable the "delete item" VBO operation on that field. Then go to your view page, select the operation from the drop down and select all the comments (make sure your view filters are set up to exclude those comments you want to keep!). There is even an option to select all the comments even if they are not all displayed. Drupal will launch the batch API to delete the comments in batches.

Juc1’s picture

@ scor this works for me thank you - but I need to limit the view display to about 1000 comments otherwise I get memory exhausted errors.

nzpling’s picture

Thanks scor

This is an excellent solution.

VBO is the answer folks.

Chi’s picture

You can use VBO for bulk operations with comments.

________________________
Override, don't change!

JohnSmfifth’s picture

I installed all of these various modules you need to follow until you get VBO enabled, but it made no difference, there are no checkbox options added for selecting/deleting all messages. All I want to do is group select all unapproved messages within my forum topic and then delete them. There are hundreds or more that need deleting as the old Capcha system became ineffective. I've now replaced that but can't remove the messages... seems like a dumbass design not to have the option in the first place.

There is by default check boxes for deleting user accounts, though even this isn't very useful as it will only delete one page at a time, if you want to delete 1000 user accounts then it's hopeless. Seem to have to spend a lot of time searching and installing a vast number of modules to do very simple tasks with Drupal, surely when a module is created it would make more sense if the module package contained all the necessary modules for it to run, it's not like any of them are very big. The whole lot combined is not more than a few megabytes in nearly all case, so wouldn't matter if you already had some installed or not.

Thanks

PalaDolphin’s picture

I installed VBO and Entity, and found no instructions that would even help this situation. How does it work?

chainwork’s picture

1) Went to modules and enabled PHP filtering
2) In Drupal 7 I created a PHP page with the following:

db_query('DELETE FROM comment WHERE status = 0');

3) Viewed that page logged in and logged out not sure that makes a difference.

4) It whiped out 22 thousand unapproved comments.

5) AWESOME (deleted the page after that)

JohnSmfifth’s picture

This worked perfectly.... thanks for that, though does seem somewhat ridiculous you a have to do this, such a simple bit of code that could have been easily placed in the module in the first place surely??

PalaDolphin’s picture

I did the same thing in phpMyAdmin:
DELETE FROM comment WHERE status = 0

However, I really wanted to report these comments to Mollom. And how can I get Mollom to not allow these comments in the first place? I've got text analysis set to max and these spam ads keep populating my unapproved comments.

How can I get Mollom to automatically discard these spam comments with ad links?

scor’s picture

paogarciac’s picture

Hi @chainwork,

I wanted to reply to let the readers know that this worked perfectly for me. One thing I did and some people may make the same mistake is to put the code first, then change the page to PHP code... This may add comments to the code, making the page "not work". So... whoever does this, just make sure you switch the edit area to PHP code first.

Thanks,
Paola G.

clawki’s picture

This was such a simple solution that I have now decided that any new children to grace my tribe will be named "chainwork" !!!! Thanks!!!!!

chunty’s picture

In case this helps anyone who looks at this post - I'd imagine you can do this with the rules module

gMaximus’s picture

I have just put this query together for removing the spam from a site i've started maintaining... It is a Drupal 6 site but it looks like it is only a difference in table names: Thought I'd share:

delete 
from comments 
where subject like "%viagra%"
or subject like "%cialis%"
or subject like "%payday%"
or subject like "%Abercrombie%"
or subject like "%Hollister%"
or subject like "%louis%"
or subject like "%http%"
or subject like "%Jewelry%"
or subject like "%walnut%"
or subject like "%Jewelry%"
or subject like "%nike%"
or subject like "%Ralph%"
or subject like "%ray ban%"
or subject like "%tiffany%"
or subject like "%north face%"
or subject like "%outlet%"
or subject like "%coupon%"
or subject like "%shoes%"
or subject like "%religion%"
or subject like "%bags%"
or comment like "%http%"
or comment like "%viagra%"
or comment like "%cialis%"
or comment like "%payday%"
or comment like "%Abercrombie%"
or comment like "%Hollister%"
or comment like "%louis%"
or comment like "%http%"
or comment like "%Jewelry%"
or comment like "%walnut%"
or comment like "%Jewelry%"
or comment like "%nike%"
or comment like "%Ralph%"
or comment like "%ray ban%"
or comment like "%tiffany%"
or comment like "%north face%"
or comment like "%outlet%"
or comment like "%coupon%"
or comment like "%shoes%"
or comment like "%religion%"
or comment like "%bags%";

You'll need to add " WHERE status = 1" to the end of the query if you're only interested in awaiting approval...

I am always interested in paid work... Contact me through Online Business Builders

acumensw’s picture

This will clear all unapproved comments.

delete from field_revision_comment_body where entity_id in ( select cid from comment where status = 0 ) ;
delete from field_data_comment_body where entity_id in ( select cid from comment where status = 0 ) ;

DELETE FROM `comment` WHERE status = 0 ;

Use captcha to avoid doing it again.

akalam’s picture

VBO is the best option. Also there is a module called admin_views that uses VBO to override the backend admin pages

https://www.drupal.org/project/admin_views

SKAUGHT’s picture

+1 and (better) exposed filters will shorten your stress

GiorgosK’s picture

Another promising module is
https://www.drupal.org/project/admin_frosting
also the module mentioned before
https://www.drupal.org/project/admin_views

or you can use https://www.drupal.org/project/views_bulk_operations
and create YOUR OWN admin tools based on views