1

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.

1

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.

| improve this answer | |
  • Thank you Hector for the welcoming and detailed response! I really appreciate you taking the time to help explain this to a newbie like me; I will update this thread if I manage to have success! – fraudjeff Oct 22 '19 at 18:46
  • Hector, I managed to get the query to work; I realized that I should be hitting GO instead of UPDATE :facepalm: I was unsure of how to add your elegant solution, so I first tried to delete the queried results in batches of 500; unfortunately, I am not seeing any changes in the user count on my wordpress dashboard when I navigate to the Users tab after making 2000 deletions from the ~14000 populated results via the query… – fraudjeff Oct 22 '19 at 20:06
  • …I think your solution of deleting from the dashboard is the proper way to go about this, but I am scared and would love some clarification first before I go and make any changes. After some research, I am led to believe that I would copypasta your code into the functions.php file, which I can find by navigating to Appearance>Editor tab in my wordpress dashboard. From there, I can paste your code into the file and save, navigate back to the Users tab, and only the users without admin/orders will show? Once done, I can just remove the code and be left with the users I want? – fraudjeff Oct 22 '19 at 20:06
  • @fraudjeff It will add a new column in admin->users. Like email column and shows count of orders for each user. You can keep the code or remove it. It will not affect users. It just displays information about users. – Hector Oct 22 '19 at 20:12
  • You are the man! Thank you so much sir for your help, your code worked flawlessly; I am going to leave the extra column as well. Unfortunately, my upvotes don't do much as a new user, but you have my utmost gratitude! – fraudjeff Oct 22 '19 at 20:37
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.

| improve this answer | |
  • This script worked well for me, as of today on latest version of WP. I would suggest adding a way to make it delete in batches, as with 12000 users to delete, in my case it timed out. Which was fine, but meant getting an error, and then running again. – omega33 May 11 at 7:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.