How to delete thousands of records with phpmyadmin?

Discussion in 'Databases' started by Jboo, Jul 25, 2013.

  1. #1
    Hi,
    I've been hit with hundreds of thousands of spam comments and need to delete them from my database. Doing this manually is way too time consuming. However, I want to keep the first 30 records as they are legitimate comments. Could anyone explain what I need to do in phpmyadmin when deleting from the 'field_revision_comment_body' table?

    Thanks for any help, it's appreciated.
     
    Jboo, Jul 25, 2013 IP
  2. GORF

    GORF Well-Known Member

    Messages:
    224
    Likes Received:
    21
    Best Answers:
    3
    Trophy Points:
    165
    #2
    There should be a unique ID field, I will call it id.
    Look at the first 30 entries and find the highest id number, let's say it is 47.
    Your SQL statement would be:

    delete from field_revision_comment_body where id > 47
     
    GORF, Jul 25, 2013 IP
  3. Jboo

    Jboo Active Member

    Messages:
    229
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    58
    #3
    Thanks for your reply. I've tried doing as you suggest but get this...

    "#1054 - Unknown column 'id' in 'where clause'"

    Looking at the rows I have a field called entity_id so tried...

    delete from field_revision_comment_body where `entity_id` > 33

    But nothing is getting deleted.

    Is there something I'm missing?

    Thanks again for any help.
     
    Jboo, Jul 26, 2013 IP
  4. t3od0r

    t3od0r Well-Known Member

    Messages:
    334
    Likes Received:
    3
    Best Answers:
    3
    Trophy Points:
    155
    #4
    Can you post a screenshot with the table?

    What CMS you are using? isn't there any plugin for spam comments?
     
    t3od0r, Jul 26, 2013 IP
  5. Jboo

    Jboo Active Member

    Messages:
    229
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    58
    #5
    EDIT: Image is apparently too large to attach, even at 180kb! I'll try after work.
     
    Jboo, Jul 29, 2013 IP
  6. Code Developer

    Code Developer Active Member

    Messages:
    48
    Likes Received:
    2
    Best Answers:
    2
    Trophy Points:
    58
    #6
    Better way would be first backup those 30 records and after that delete the whole table or if you can delete all records would be good to:confused:nce Records/Table deleted create a new table with the same name and import those 30 records again.That's All!

    You're Done.
     
    Code Developer, Jul 29, 2013 IP
  7. Nigel Lew

    Nigel Lew Notable Member

    Messages:
    4,642
    Likes Received:
    406
    Best Answers:
    21
    Trophy Points:
    295
    #7
    Turn on askismet and run it.

    Nigel
     
    Nigel Lew, Jul 29, 2013 IP
  8. Nigel Lew

    Nigel Lew Notable Member

    Messages:
    4,642
    Likes Received:
    406
    Best Answers:
    21
    Trophy Points:
    295
    #8
    Hmm. Sorry, I think that is drupal?
    https://drupal.org/project/antispam

    You should be able to run this against pre-existing comments. I had to do this with wp a few weeks ago. same stuff different platform

    Hope that helps,
    Nigel
     
    Nigel Lew, Jul 29, 2013 IP
  9. woits

    woits Greenhorn

    Messages:
    29
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    6
    #9
    run SQL Query

    Delete * FROM tbl_name WHERE comment_status='draft' etc
     
    woits, Jul 30, 2013 IP