SQL Query for WordPress/WooCommerce in PHPmyAdmin to select spam users

1 Views

I have a WordPress database that has approximatly 28000 spam user`registrations in there which I some how need to isolate and delete without removing any actual customers.

I have noticed that all of the spam user registrations have an empty meta_value for a specific meta_key

Read More

The meta_key in question is billing_first_name

Therefore, I am trying to write a query I can execute in PHPmyAdmin that will return a list of all user IDs that have an empty meta_value for the meta_key billing_first_name so that I can then delete all of these users from the _usermeta table AND the _users table.

I found a user online who had a similar issue: https://wordpress.org/support/topic/deleting-spam-user-accounts-from-site-with-woocommerce

He wrote the following query that I have attempted to run on my database: 

SELECT * FROM wp_usermeta JOIN wp_users ON (wp_usermeta.user_id = wp_users.ID) WHERE user_id NOT IN (SELECT um1.user_id FROM wp_usermeta um1 WHERE um1.meta_key = 'shipping_first_name')

However, its only returning 137 results, and with 28000+ plus registration, but only around 1000 actual orders, I have a heck of a lot more than 137 spam accounts in the database.

I have tried adapting the query to expand upon it and get it to do what I want, but I am not really a “database” person and am not having much luck.

My attempt was: 

SELECT * FROM wv5_usermeta JOIN wv5_users ON (wv5_usermeta.user_id = wv5_users.ID) WHERE user_id NOT IN (SELECT um1.user_id FROM wv5_usermeta um1 WHERE um1.meta_key = 'billing_first_name' AND um1.meta_value IS NOT NULL)

Can someone help me formulate a working query that I can use?

Note that my attempt was based on the query I found online, and I have no idea if thats even the right approach for this.

My end goal is to identify ALL users that have never made any orders. Users that have made no orders do not have a billing address or shipping address set. So therefore, I am trying to build a query that selects users with a null billing address or null shipping address and then delete them.

Many Thanks for any asssitance provided.

EDIT:

Just to clarify, the site users WooCommerce and as such there is no specific orders table in the database. meta_keys such as “billing_first_name”, “shipping_first_name” and such only get populated with a value once a user has made an order for the first time. Therefore, the assumption is that a spam user would have never made an order and thus these meta_keys will be null.

Post Views: 1

Related posts

1 comment

  1. Just a guess according to this post woocommerce stores orders in a posts table with post_type = 'shop_order'.

    So you can try this query :

    SELECT u.* FROM wv5_users u
LEFT JOIN wv5_posts p
ON  u.ID = p.post_author
  AND p.post_type = 'shop_order'
GROUP BY u.ID
HAVING COUNT(p.id)=0

    It should return list of users never had any posts with post_type = 'shop_order'. Probably you should check what exact post_type your woocommerce uses.

    Be careful if you plan to delete all those users. Some of them could be your administrators.

    EDIT Sorry, I have no wordpress and woocommerce installed. Since my query does not help. Let try to go your way but a bit properly:

    SELECT u.* FROM wv5_users u
LEFT JOIN wv5_usermeta m
ON  u.ID = m.user_id
  AND m.meta_key = 'shipping_first_name'
GROUP BY u.ID
HAVING COUNT(m.id)=0

    or according to this

    HAVING COUNT(m.umeta_id)=0

Comments are closed.