This time around we’ll take a look at some SQL. As you know, WooCommerce orders (same as WooCommerce products) are stored in the WordPress database.
Instead of using complex PHP loops and conditionals, sometimes knowing a bit of database “reading” can help. I took some inspiration (because I don’t know everything by heart) from the “wc_customer_bought_product()” WooCommerce function, which contains some SQL to check if a user has purchased a given product.
I’ve played a little with the same SQL SELECT call, and managed to return the list of user email addresses who have purchased a specific product ID. If you’re ever going to need this, enjoy!
PHP Snippet: Get List of WooCommerce Customer Emails Who Purchased a Specific Product
Usage: simply call the bbloomer_echo_product_customers( $product_id ) function somewhere in your code, and you will get the list of customers who bought a specific product!
/**
* @snippet Get Customers Who Purchased Product ID
* @how-to Get CustomizeWoo.com FREE
* @author Rodolfo Melogli
* @compatible WooCommerce 6
* @donate $9 https://businessbloomer.com/bloomer-armada/
*/
function bbloomer_echo_product_customers( $product_id ) {
global $wpdb;
$statuses = array_map( 'esc_sql', wc_get_is_paid_statuses() );
$customer_emails = $wpdb->get_col( "
SELECT DISTINCT pm.meta_value FROM {$wpdb->posts} AS p
INNER JOIN {$wpdb->postmeta} AS pm ON p.ID = pm.post_id
INNER JOIN {$wpdb->prefix}woocommerce_order_items AS i ON p.ID = i.order_id
INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS im ON i.order_item_id = im.order_item_id
WHERE p.post_status IN ( 'wc-" . implode( "','wc-", $statuses ) . "' )
AND pm.meta_key IN ( '_billing_email' )
AND im.meta_key IN ( '_product_id', '_variation_id' )
AND im.meta_value = $product_id
" );
if ( $customer_emails ) echo implode( ', ', $customer_emails );
}
This is very useful. But i wonder how i can call this dynamically via shortcode?
Hello Frode, thanks so much for your comment! Yes, this is definitely possible, but I’m afraid it’s custom work. If you’d like to get a quote, feel free to contact me here. Thanks a lot for your understanding!
Good morning, how do I specify which specific product I want to sort?
Hi Bartosz, you can use the function anywhere you need, simply specify the product ID:
Hello! How would I go about to find users IDs instead of emails?
Hi Rafa, thanks so much for your comment! Yes, this is definitely possible, but I’m afraid it’s custom work. If you’d like to get a quote, feel free to contact me here. Thanks a lot for your understanding!
Great – thank you. Still working with v. 4.9, Jan 2021. Super helpful.
Awesome!
Still works in WooCommerce 4.01
Ha meant to say 4.0! not 4.01 🙂
Nice!
Using the Code Snippets plugin. Once the code snippet is activated, where should I look to see the results?
Hi Mark! It depends where you need to display them. For example, this is how to add a new WP admin subpage: https://businessbloomer.com/woocommerce-add-new-subpage-wordpress-admin-dashboard/
This is a very handy one, thanks Rodolfo
Great!