Delete all pending listings

  • Prashanthi
    Prashanthi
    Newbie
     
    21 points

    My site has over 5000 spam users and 90000 pending listings. Any idea about deleting all pending listings?

    Then delete all users with zero listings?


    JamesD
    JamesD
    Titan
     
    671 points

    Hello Prashanthi,

    Note: This isn’t for the faint of heart! ๐Ÿ˜‰ Be sure to replace the “YOUR_TABLE_PREFIX_” with your table prefix (set in wp-config.php)

    1) Make a backup of the whole database first! If you’re not familiar or comfortable doing something like this yourself, then I’d discourage you from trying, but recommend finding someone able to handle it for you.

    If you have access to execute MySQL queries, then you can try the following…
    2) Remove All Pending Listings (includes listing history):

    DELETE a,b,c,d
    	FROM YOUR_TABLE_PREFIX_posts a
    		LEFT JOIN YOUR_TABLE_PREFIX_term_relationships b ON ( a.ID = b.object_id )
    		LEFT JOIN YOUR_TABLE_PREFIX_postmeta c ON ( a.ID = c.post_id )
    		LEFT JOIN YOUR_TABLE_PREFIX_term_taxonomy d ON ( d.term_taxonomy_id = b.term_taxonomy_id )
    		LEFT JOIN YOUR_TABLE_PREFIX_terms e ON ( e.term_id = d.term_id )
    		WHERE a.post_status = "pending" AND a.post_type = "listing_type"

    3) Remove All Users Without Posts:

    DELETE a FROM YOUR_TABLE_PREFIX_users AS a WHERE a.ID NOT IN (SELECT DISTINCT(post_author) FROM YOUR_TABLE_PREFIX_posts)

    4) Remove User Meta Data For Non-existing Users:

    DELETE a FROM YOUR_TABLE_PREFIX_usermeta AS a WHERE a.user_id NOT IN (SELECT ID FROM YOUR_TABLE_PREFIX_users)
    

    There could be other data fragments around that may need removed/adjusting, but these should get the main bulk out for you.


    JamesD
    JamesD
    Titan
     
    671 points

    I’ll do one better…. perhaps this could make it a tad easier. No need to do anything except upload the folder.

    You’ll need to extract this file and upload it to your web root (same location where the ‘wp-config.php’ is).

    Once loaded, go to:
    YOUR_WP_SITE_URL/_qkcln/qkcln.php

    Once complete be sure to delete the folder and file.

    Don’t forget, always make a back up of your DB and test after any changes!

    Attachments:
    You must be logged in to view attached files.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.