I'm trying to query the database and pass the results to the update_post_meta function. But not sure if I'm building this correctly or if there's something wrong with my usage of $order_id?
Once an order is placed, I need to update the post meta with the currently logged in user and the query results for the current order, so thought the woocommerce_thankyou hook would make sense, but the post meta is not written after the order is completed. < /p>
add_filter( 'woocommerce_thankyou', 'my_function', 10, 2);
function my_function( $result, $order_id ) {
// Load the global $post
global $woocommerce, $post;
// Get the post ID
$order_id = $post->ID;
// Then you can get the order object
$order = wc_get_order( $order_id );
$user_ID = get_current_user_id();
//SQL
global $wpdb;
return $wpdb->get_var("SELECT SUM(b03_woocommerce_order_itemmeta.meta_value)
FROM b03_woocommerce_order_itemmeta
JOIN b03_woocommerce_order_items ON b03_woocommerce_order_itemmeta.order_item_id = b03_woocommerce_order_items.order_item_id
JOIN b03_posts ON b03_woocommerce_order_items.order_id = b03_posts.ID
JOIN b03_postmeta ON b03_posts.ID = b03_postmeta.post_id
WHERE b03_posts.post_type = 'shop_order'
AND b03_woocommerce_order_itemmeta.meta_key = 'trees_planted'
AND b03_postmeta.meta_value = $user_ID
AND b03_postmeta.meta_key = '_customer_user'
AND b03_posts.ID = $order_id");
update_post_meta( $order_id, 'trees',$wpdb);
}
Any suggestions on how best to handle this issue?
Your code attempt contains multiple bugs and errors:
woocommerce_thankyouis an action hook, not a filter hook$order_idis passed to the callback function,$resultis not applicable$wpdb->prefixwithb03_, this can make it dynamic$wpdbis an objectglobal $woocommerce, $post;is redundantSo you get:
function action_woocommerce_thankyou( $order_id ) { // Get $order object $order = wc_get_order( $order_id ); // Is a WC_Order if ( is_a( $order, 'WC_Order' ) ) { // Get user id $user_id = $order->get_user_id(); // Not a guest if ( $user_id > 0 ) { //SQL global $wpdb; // The SQL query $result = $wpdb->get_var( " SELECT SUM( oim.meta_value ) FROM {$wpdb->prefix}woocommerce_order_itemmeta as oim JOIN {$wpdb->prefix}woocommerce_order_items as oi ON oim.order_item_id = oi.order_item_id JOIN {$wpdb->prefix}posts as p ON oi.order_id = p.ID JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id WHERE p.post_type = 'shop_order' AND oim.meta_key = 'trees_planted' AND pm.meta_value = '$user_id' AND pm.meta_key = '_customer_user' AND p.ID = '$order_id' " ); // REMOVE THIS, only for testing purposes $result = 10; // Add the meta data $order->update_meta_data( 'trees', $result ); $order->save(); } } } add_action( 'woocommerce_thankyou', 'action_woocommerce_thankyou', 10, 1 );Note: Since you are using a custom SQL query where the data/results don't exist generally/by default in WooCommerce, but just for you, I have replaced it with mine The answer has a fixed value of 10. Adjust as needed!