How to delete users with no order history through phpmyadmin sql?

by

I have a wordpress ecommerce website which I have begun to update after not touching it for over a year. The first thing I did was implement reCaptcha into all inquiry and contact forms to reduce the amount of spam that is sent to my business’ inbox. I have also got it working for login and user account creation, to hopefully reduce the amount of bot user accounts registering on my website.

I think I am in a good place to now to tackle the users side of this cleanup, which I have roughly 14,000 in my database. Of these users, there is probably only ~100-200 that have actually placed orders on my ecommerce website. I have been searching for a way to delete users with no order history, and I came across this post: https://wordpress.org/support/topic/how-do-i-delete-customers-with-no-orders/

Under phpmyadmin, I have navigated to SQL tab to run a query, but am not having success with the code provided by the above link. Here is a screenshot of what my SQL window looks like when selecting the wp_tom2users database. https://ibb.co/8488Zyk

If I try to hit update, without making any changes, I get this https://ibb.co/LrJcBmF
Same goes for if I try and paste the below code into the textbox and hit update.

SELECT * from wp_tom2users where wp_tom2users.ID not in (
    SELECT meta_value FROM wp_tom2postmeta WHERE meta_key = '_customer_user'
) AND wp_tom2users.ID not in (
    select distinct(post_author) from wp_tom2posts
)

I was hoping someone could help me with this, as I would prefer to leave the accounts with order history rather than bulk delete all users. I also want to mention, I have three users with administrator privileges which I would like to prevent from deleting as well.

Thank you for taking the time to read the essay I typed into my issue.

Edit: After looking at the code from the link provided above again, and looking at the table indexes https://ibb.co/tHxpMCN I don’t think I can sort by ‘_customer_user’ as it’s not an index in wp_tom2postmeta.

2 Answers
2

Hector’s answer works well, but if you have have a large number of users you need to remove (in my case, it was over 13,000 spam registrations) I find using this little utility script works wonders, and doesn’t require you to manually check and delete from the user-list in the admin panel.

The script still might time out, so keep an eye on it if you are clearing out a lot of users.

cleanusers.php

//Load WP functions and DB access
include('wp-load.php');

//required for wp_user_delete
require_once( ABSPATH.'wp-admin/includes/user.php' );

//Let it run forever
set_time_limit(0);

//Get the $wpdb database object
global $wpdb;


//Loop through all users
foreach($wpdb->get_results('SELECT ID from '.$wpdb->prefix.'users ORDER BY ID DESC') as $user) {


    //Get user object
    $user = get_user_by('ID', $user->ID);

    //Check if this user's role (customer, subscriber, author, etc.)
    $roles = $user->roles;
    if ($roles[0] == "customer") {

        //Check the order count and delete if it is 0
        $order_count = wc_get_customer_order_count( $user->ID);
        if ($order_count === 0) wp_delete_user($user->ID);

    }

}

echo "DONE!";

Place the above file in your installation folder, then access via browser or run via command-line.

Cheers,
C.

Leave a Comment