Update a user meta key based on WooCommerce product purchase

I did manage to figure out a solution. It’s not pure MySQL, but rather uses some WP/PHP wizardry. So this is how I solved the problem – but I’m still open to better solutions (particularly a pure MySQL method).

I used the $wpdb->get_results() object method to retrieve the results of the SQL query. That returns the results as an array (specifying ARRAY_A for the returned results).

Then looping through the results, convert the date (which is a string, as all user meta values are) using PHP’s strtotime(), subtracting 1 year and returning that result to the (yes, meh) m/d/Y format required, finally updating the user meta with the new date using update_user_meta() (the user ID being in the SQL result as ‘customer_id’).

Personally, but not important to the answer, I also echoed the results to the screen so I could keep a record of what records were updated. But that could have been stored as a user meta during the processing as well.

I’ve commented all code and the MySQL query below for clarity:

// Use the $wpdb database object class.
global $wpdb;

// MySQL query
$sql = "SELECT
        u1.ID,
        u1.post_date,
        u1.post_type,
        u1.post_status, 
        m2.meta_value AS customer_id,      # WooCommerce customer ID from post meta
        o3.order_item_name AS item_name,   # The WooCommerce Product Item name
        e4.meta_value AS expires           # A custom user meta field called expires formatted as MM/DD/YYYY
    FROM wp_posts u1
    JOIN wp_postmeta m2 ON (m2.post_id = u1.ID AND m2.meta_key = '_customer_user')
    JOIN wp_woocommerce_order_items o3 ON (o3.order_id = u1.ID )
    JOIN wp_usermeta e4 ON (e4.user_id = m2.meta_value AND e4.meta_key = 'expires')

    WHERE u1.post_type="shop_order"             # WooCommerce orders are custom post type shop_order
    AND u1.post_status="wc-completed"           # Only dealing with post_status wc-completed which is a completed WC order
    AND o3.order_item_name="My Fancy Product"   # String value for item name in the WC order item table
    AND STR_TO_DATE( e4.meta_value, '%m/%d/%Y' ) > STR_TO_DATE( '01/01/2020', '%m/%d/%Y' ) # Format custom user meta (string) as a date

    ORDER BY STR_TO_DATE( e4.meta_value, '%m/%d/%Y' );   # Order by custom user meta (string) as a date";

// Get results of $sql query as an array.
$results = $wpdb->get_results( $sql, ARRAY_A );

// Go through results and update user.
foreach( $results as $result ) {

    // Take current "expires" and get a value -1 year.
    $new_expires = date( 'm/d/Y', strtotime( $result['expires'] . ' -1 year' ) );

    // Update the "expires" meta with the new value.
    update_user_meta( $result['customer_id'], 'expires', $new_expires );
}