How to bulk delete comments?
|
teddink
Joined: 2006-06-22
Posts: 7 |
Posted: Fri, 2006-09-29 23:25
|
|
Gallery version = 2.1.2 core 1.1.0.2 Unfortunately I left the ability for guest to comment on the root album. I eneded up with 350 or so comments on the root album. Is there an easy way to bulk delete all of the comments for a particular album? Needless to say that deleting them one at a time will get very tiresome... Thanks in advance. |
|
Posts: 32509
there's no interface for that yet.
you can file a feature request for it.
if you decide to manually delete the comments in the database, don't forget the g2_Entity and g2_ChildEntity tables which hold a row for each row in g2_Comment.
Posts: 7
Done - thanks for the verification and the suggestion. I may try the direct DB deletion method in the meantime.
Posts: 14
That seems a pretty glaring lack.
Posts: 107
Has anything for this done yet in 2.2? I have looked around and can't find anything.
I see here (http://gallery.menalto.com/node/33894) that a hack existed once for this in Gallery 1, and also there has been lots of requests for some way to bulk delete comments (there was in G1). I may be missing this functionality if it has indeed been added since I last checked.
Eduo
---
www.eduo.info
www.eduo.info/gallery/
www.hamsterspit.com
Posts: 17
haven't found anything for 2.2.
though haven't looked too hard since I saved the magic SQLs that deletes them.
1) browse the g2_comment table to find range of g_id's that you want to delete (in my case there are ~15 spam comments a day from various IPs despite the fact that I have captcha module enabled for anonymous comments). Take a not of min/max values of the IDs - ID_MIN, ID_MAX
2) replace the ID_MIN/ID_MAX with actual values and execute the following SQLs:
delete from g2_Comment where g_id>=ID_MIN and g_id<=ID_MAX
delete from g2_Entity where g_id>=ID_MIN and g_id<=ID_MAX
delete from g2_ChildEntity where g_id>=ID_MIN and g_id<=ID_MAX
for good or for bad, but legal commenters are rather inactive in my galleries, so it's usually just a single range that needs to be deleted to get rid of the spam comments.
cheers,
Austris
Posts: 32509
that's a very dangerous and error-prone way to do it. please don't do it this way.
you could delete new items and other stuff this way by accident.
--------------
Documentation: Support / Troubleshooting | Installation, Upgrade, Configuration and Usage
Posts: 36
okay, so deleted the Comment rows without deleting the entries from the Entity and ChildEntity tables... Any advice? :o)
Posts: 17
that's why valiant asked you to not do it this way ;)
I'm not SQL expert, so it might be not most effective way, but I would fix it as follows:
run the following SQL to get id's of comments you deleted from Comment table and did not delete from Entity and child Entity (this is just informative step so you can get the feeling whether number of id's is ok or not):
select g_id from g2_Entity where g_entityType='GalleryComment' and g_id not in (select g_id from g2_Comment)
delete the id's from both tables. I used following SQL:
delete g2_ChildEntity, g2_Entity from g2_ChildEntity, g2_Entity where g2_ChildEntity.g_id=g2_Entity.g_id and g2_Entity.g_entityType='GalleryComment' and g2_Entity.g_id not in (select g_id from g2_Comment)
Posts: 17
ok, I got the same problem in the different instance of gallery, so I came up with something better.
I hope, valiant will admit this as more secure option
to delete by IP:
delete ce, e, co from g2_ChildEntity ce, g2_Entity e, g2_Comment co where ce.g_id=e.g_id and e.g_id=co.g_id and e.g_entityType='GalleryComment' and (co.g_host='195.225.177.20' or co.g_host='195.225.177.40')
to delete by content:
delete ce, e, co from g2_ChildEntity ce, g2_Entity e, g2_Comment co where ce.g_id=e.g_id and e.g_id=co.g_id and e.g_entityType='GalleryComment' and (co.g_comment like '%[url=http://%')
to delete by author:
delete ce, e, co from g2_ChildEntity ce, g2_Entity e, g2_Comment co where ce.g_id=e.g_id and e.g_id=co.g_id and e.g_entityType='GalleryComment' and (co.g_author like '%ialis%' or co.g_author like '%iagra%')
of course, you can combine IP with content and/or author if you wish.
and this will delete it in a single sql, so yottabit won't have the same problem next time
cheers,
Austris
Posts: 15
I went into MyPHP and found all the records of the few hundred comment spams and deleted them. Now when I attempt to view latest comments of certain folders, I get a SQL error:
I think for some reason, Gallery is still looking for those comments. You can tell by going to my gallery homepage and clicking "Latest Comments." The first page is fine but when you click next, you will see "not found." Admins see the above detail. I have tried to delete the database cache and I got the following results:
When I tried to delete the template cache, I get THIS error:
Anyone have any ideas as to how to fix it?
Posts: 15
Fixed the delete problem by playing with permissions but I still have the not-found issue with viewing latest comments.
Posts: 1
I had quite a success using CommentBlaster, after failing one of the SQL queries above.
Posts: 12
Posts: 94
Thanks for suggesting this script!
I had more than a thousand spam comments!
Posts: 1
Script works great. Thanks!