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 “SQL SELECT” 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!

Here’s the array containing the list of customer billing emails who have purchased a given WooCommerce product

PHP Snippet: Get List of WooCommerce Customer Emails Who Purchased a Specific Product

/**
 * @snippet       Get Customers Who Purchased Product ID
 * @how-to        Get CustomizeWoo.com FREE
 * @author        Rodolfo Melogli
 * @compatible    WooCommerce 3.7
 * @donate $9     https://businessbloomer.com/bloomer-armada/
 */

// Access WordPress database
global $wpdb;

// Select Product ID
$product_id = 282;
		
// Find billing emails in the DB order table
$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
");

// Print array on screen
print_r( $customer_emails );
Share:

Leave a Reply