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 tutoraspire_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 tutoraspire.com FREE
* @author Tutor Aspire
* @compatible WooCommerce 6
* @donate $9 https://www.tutoraspire.com
*/
function tutoraspire_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 );
}