WordPress: the blogging engine for Ecommerce
Since WordPress was initially built for blogging, everything built on top of it has a little of that flavor infused into it. When working with WooCommerce you’ll find that products, orders, and subscriptions are all just types of posts (seems like everything in WordPress is a post… or at least related to one).
So when it comes to identifying them and cleaning them out of the database it’s helpful to know how they work together.
Everything’s a Post
I need to do some bulk cleanup on a WooCommerce site so I went straight into PHPMyAdmin rather than deleting things through the WordPress admin. Below you’ll see a SELECT and corresponding DELETE statement. I always want to see what I’m about to delete so I’ve included both here.
Users and their metadata
WooCommerce customers are all found in the
wp_users table. Their role is stored in the
wp_usermeta table so we have to join those to get all of the data identified and removed.
SELECT * FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id WHERE meta_key = "wp_capabilities" AND INSTR(meta_value, "customer") AND NOT INSTR(meta_value, "administrator");
DELETE wp_users, wp_usermeta FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id WHERE meta_key = "wp_capabilities" AND INSTR(meta_value, "customer") AND NOT INSTR(meta_value, "administrator");
WooCommerce Reviews and Orders notes
WooCommerce reviews piggyback off of the existing function of WordPress comments. If you’re looking for reviews, you will find them in the
wp_comments table. You’ll also find order notes in there. Again, since orders are just a post type, tying multiple notes in the form of comments to them is functionality built-in to WordPress. It’s great to reuse the existing framework but can make it difficult to find things since they’re not named intuitively.
SELECT * FROM wp_comments WHERE comment_type="review";
DELETE wp_comments FROM wp_comments WHERE comment_type="review";
SELECT * FROM wp_comments WHERE comment_type="order_notes";
DELETE wp_comments FROM wp_comments WHERE comment_type="order_notes";
Clean up ALL comment meta
For my purposes, I wanted to remove all of the comment meta. This may not work in your case if there are other blog post comments that you want to keep. If so you’ll have to make a JOIN statement with the corresponding records in
wp_comments in order to be more specific about what to delete.
DELETE wp_commentmeta FROM wp_commentmeta;
Clean up orphaned postmeta
Since we deleted the orders and subscriptions, we also need to clean up the metadata from
wp_postmeta that got left behind.
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
At this point we have a cleaned up database that is ready to start taking new orders, or in my case, start migrating from another site.