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

Question

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.

Answer 1

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.

Answer 2

This is an expanded version of Chad's answer that does them in batches. It also adds a current timestamp, how long the script took, and how many records were deleted.

<?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;

$i = 0; /*counter starts at 0 not 1*/

/*log start time*/
$start_time = microtime(true);

/*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);
            /*stops at 1000th record - change this if you want*/
            if ($i++ == 999){
                /*log the time it took*/
                $end_time = microtime(true);
                break;
            } 
        } 

    }

}
if(!$end_time){
    $end_time = microtime(true); 
}
$execution_time = ($end_time - $start_time);

echo "DONE! Deleted " .$i. " users.<br>" ;
echo "The time in " . date_default_timezone_get() . " is " . date("H:i:s");
echo " Execution time of script = ".$execution_time." sec";

Answer 3

Welcome!

Short Answer about SQL queries:

Your query in the first image is not correct. It should be like SELECT * FROM wp_tom2users and it will select everything in the table. If it is not working, you may need to add your database name to it like SELECT * FROM myDbName.wp_tom2users.

The mentioned query looks fine, but try it by adding database name like above.

Detailed answer to visualize the process:

As you want to delete some users based on their orders, it is better to use existing functions in WordPress and WooCommerce instead of doing it using direct SQL code.

In WooCommerce, there is a function to count user orders. wc_get_customer_order_count Based on documentation you need to pass the user ID to this function and it will return the count of orders.

$oreder_count = wc_get_customer_order_count( $user_id );

So, if you run a loop for users and count their orders, you can find wich users should be deleted.

The following code can display order count for each user in admin-> users as a sortable column.

add_filter( 'manage_users_columns', 'prefix5487_modify_user_columns' );

function prefix5487_modify_user_columns( $column ) {
    $column['orders'] = __( 'Order count' );
    return $column;
}


add_filter( 'manage_users_custom_column', 'prefix5487_user_order_column_value', 10, 3 );

function prefix5487_user_order_column_value( $val, $column_name, $user_id ) {
    switch ($column_name) {
        case 'orders' :
            return wc_get_customer_order_count( $user_id );
        default:
    }
    return $val;
}


add_filter( 'manage_users_sortable_columns', 'prefix5487_make_registered_column_sortable' );

function prefix5487_make_registered_column_sortable( $columns ) {
    return wp_parse_args( array( 'orders' => 'orders' ), $columns );
}

Now, you can sort your users based on order count and use bulk action to delete them and make sure you are not deleting admin users.