3

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

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.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Zach Nicodemous
  • 9,097
  • 9
  • 45
  • 68
  • How much are your real vs spam users ? Can you make some count on that ? – Svetoslav Oct 23 '15 at 13:23
  • generally meta `billing_first_name` has almost nothing with `orders`. So you should better think about what is real criteria. In your database should be some table called `orders` show the schema of that table. It must be simple join `users` with `orders` table – Alex Oct 23 '15 at 13:26
  • Based on my orders, I would estimate around 1000 real users, and 28500 spam users. – Zach Nicodemous Oct 23 '15 at 13:26
  • @ Alex the site uses WooCommerce and there is no specific table for orders. billing_first_name and meta_keys like it such as shipping_first_name only get populated when a customer has made an order for the first time, therefore the assumption is that all spam accounts have never made an order which means this would be null. – Zach Nicodemous Oct 23 '15 at 13:27
  • AFAIK the meta_keys are in table post_meta or something like that .You should make it more clear that you are talking about woocomerce – Mihai Oct 23 '15 at 13:28
  • Sorry about that, I have added that info to the main post. – Zach Nicodemous Oct 23 '15 at 13:30

1 Answers1

5

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
Community
  • 1
  • 1
Alex
  • 16,739
  • 1
  • 28
  • 51
  • Hi Alex. I just tried this query but its saying: #1054 - Unknown column 'wv5_users.ID' in 'on clause' – Zach Nicodemous Oct 23 '15 at 14:49
  • @ZachNicodemous oops... sorry my bad... I didn't replace table name with alias I set to the table so it should be `u.ID`. check updated answer – Alex Oct 23 '15 at 15:03
  • OK the query ran this time but returned ALL users except a couple. I had a look through the posts table and all posts with the post type "shop_order" get attributed to user ID 1 which is an admin, hence why its returning almost every single user on that query. – Zach Nicodemous Oct 23 '15 at 15:16
  • It still returns all users. Its because WooCommerce attributes all orders to User ID 1 (admin), therefore that user is the only user that ever has posts in the "shop_order" post type from the perspective of a query like that. – Zach Nicodemous Oct 23 '15 at 15:35
  • @ZachNicodemous check my edit, but I am stiill not sure that it is a good way to go. I believe this list should be get through `posts` table. since that is real `order` table in your case – Alex Oct 23 '15 at 15:59