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 );
}