Question 207033 in Woocommerce Question posted in
The official Woocommerce documentation can be found here.

Updating WooCommerce product custom metadata via a custom sql query issue

I have a WooCommerce shop for a client where I have products and product bundles. I would like to visualize some information from the product bundle (the price and the name) on a product that is part of that bundle (a child product, if you like).

For this I have created two custom fields, using the JetEngine plugin.

Using PHP I’ve written some code that retrieves the bundle ID that is linked to a product ID from the database. When this is found I update the meta value of those two custom fields with the right value, see my code below

function populate_bundle_data($post_id) {
    global $wpdb;

    $product = wc_get_product($post_id);

    // Retrieve the bundle ID
    $bundle_id = $wpdb->get_var($wpdb->prepare("
        SELECT bundle_id
        FROM {$wpdb->prefix}woocommerce_bundled_items
        WHERE product_id = %d
    ", $post_id));

    // Debug statement to check the retrieved bundle ID
    error_log('Bundle ID: ' . $bundle_id);

    if ($bundle_id && $product->is_type('simple')) {
        $bundle_product = wc_get_product($bundle_id);
        $bundle_name = $bundle_product->get_name();
        $bundle_price = $bundle_product->get_price();

        // Debug statements to check the bundle name and price
        error_log('Bundle Name: ' . $bundle_name);
        error_log('Bundle Price: ' . $bundle_price);
        
        error_log('Product id: ' . $post_id);

        // Update the JetEngine custom fields with bundle name and price
        // 
      
        
         $wpdb->query($wpdb->prepare("
            UPDATE {$wpdb->prefix}postmeta
            SET meta_value = %s
            WHERE post_id = %d
            AND meta_key = 'bundle-name'
        ", $bundle_name, $post_id));

        $wpdb->query($wpdb->prepare("
            UPDATE {$wpdb->prefix}postmeta
            SET meta_value = %s
            WHERE post_id = %d
            AND meta_key = 'bundle-price'
        ", $bundle_price, $post_id));
        
        

        // Debug statement to check if custom fields are updated
        error_log('Custom fields updated.');
    }
}

add_action('save_post_product', 'populate_bundle_data');

I have added some error_logs and these return all the correct value. Below is an example of such an error log. Everything is filled as expected

[13-Jul-2023 11:23:46 UTC] Bundle Name: Product bundle
[13-Jul-2023 11:23:46 UTC] Bundle Price: 200
[13-Jul-2023 11:23:46 UTC] Product id: 20
[13-Jul-2023 11:23:46 UTC] Custom fields updated.

The weird thing now is that if I change the meta_key to a native WooCommerce field (let’s say _price) the UPDATE statement does work.

So is it not allowed to write in custom fields via SQL or am I still doing something wrong?

Much appreciated if anyone have some suggestions for me!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I was able to resolve it in the meantime. Instead of working with SQL Update statements to change the value of existing fields I used the update_post_meta function from Wordpress.

    I tossed the JetEngine custom fields in the bin and replace the SQL with this

    update_post_meta($post_id, 'bundle-name', $bundle_name);
update_post_meta($post_id, 'bundle-price', $bundle_price);

    Even if those fields doesn't exist in the database, it just created these fields none the less.


  2. 0

    As WooCommerce uses, since few years, more custom tables and data caching, you should better use the recommended WC_Data methods and WooCommerce dedicated hooks.

    Also for these reasons, it’s not recommended to use WordPress functions and hooks for WooCommerce custom fields (custom metadata).

    Try this revisited code:

    // Utility function to retreive the bundle ID
function get_bundled_items_bundle_id( $product_id ) {
    global $wpdb;
    return $wpdb->get_var( $wpdb->prepare("
        SELECT bundle_id
        FROM {$wpdb->prefix}woocommerce_bundled_items
        WHERE product_id = %d
    ", $product_id) );
}

// Save/Upddate product custom metadata
add_action('woocommerce_admin_process_product_object', 'update_custom_product_bundle_metadata');
function update_custom_product_bundle_metadata($product) {
    // Retrieve the bundle ID
    $bundle_id = get_bundled_items_bundle_id( $product->get_id() );

    // Debug statement to check the retrieved bundle ID
    error_log('Bundle ID: ' . ($bundle_id ? $bundle_id : 'no id') );

    if ( $bundle_id && $product->is_type('simple') ) {
        $parent_product = wc_get_product($bundle_id);
        $parent_name  = $parent_product->get_name();
        $parent_price = $parent_product->get_price();

        // Debug statements to check the bundle name and price
        error_log('Bundle Name: ' . $parent_name);
        error_log('Bundle Price: ' . $parent_price);
        error_log('Product id: ' . $product->get_id());

        // Update the JetEngine custom fields with bundle name and price
        $product->update_meta_data( 'bundle-name', $parent_name );
        $product->update_meta_data( 'bundle-price', $parent_price );

        // Debug statement to check if custom fields are updated
        error_log('Custom fields updated.');
    }
}

    It should better work now.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search