Mass Update Product Variations Plugin

I am attempting to develop a WP plug-in that allows my clients to add a variation at the click of a button to all products on their website.

I am close, but for some reason, the dropdown in the product page won’t show the new variations. The variation adds to the global attribute, it even adds to the product with the price. The new price will even show up on the item (this can be confirmed when I look at the price display showing 20-125 which 125 is the price I was attempting at the time).

I’ve attempted through plugins and direct sql. Here is the code for both. Can anybody tell me where I’m going wrong?

SQL: -- Step 1: Get the parent product ID SET @parent_product_id = ( SELECT ID FROM wp_posts WHERE post_type = 'product' AND post_status = 'publish' AND post_title = 'Fingers Crossed' LIMIT 1 );

-- Step 2: Ensure the _product_attributes metadata is set for the parent product -- Check if _product_attributes metadata already exists SET @existing_attributes = ( SELECT meta_value FROM wp_postmeta WHERE post_id = @parent_product_id AND meta_key = '_product_attributes' LIMIT 1 );

-- Update or insert _product_attributes metadata IF @existing_attributes IS NOT NULL THEN UPDATE wp_postmeta SET meta_value = JSON_SET( @existing_attributes, '$.pa_style', '{"name":"pa_style","value":"|from-sql","position":"0","is_visible":"1","is_variation":"1","is_taxonomy":"1"}' ) WHERE post_id = @parent_product_id AND meta_key = '_product_attributes'; ELSE INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES ( @parent_product_id, '_product_attributes', '{"pa_style":{"name":"pa_style","value":"|from-sql","position":"0","is_visible":"1","is_variation":"1","is_taxonomy":"1"}}' ); END IF;

-- Step 3: Ensure the term and taxonomy exist -- Insert the term into wp_terms if it doesn't already exist INSERT INTO wp_terms (name, slug, term_group) SELECT 'From SQL', 'from-sql', 0 WHERE NOT EXISTS ( SELECT 1 FROM wp_terms WHERE slug = 'from-sql' );

-- Get the term_id for the 'from-sql' term SET @term_id = ( SELECT term_id FROM wp_terms WHERE slug = 'from-sql' LIMIT 1 );

-- Insert the taxonomy into wp_term_taxonomy if it doesn't already exist INSERT INTO wp_term_taxonomy (term_id, taxonomy, description, parent, count) SELECT@term_id, 'pa_style', '', 0, 0 WHERE NOT EXISTS ( SELECT 1 FROM wp_term_taxonomy WHERE term_id = @term_id AND taxonomy = 'pa_style' );

-- Get the term_taxonomy_id for the 'from-sql' term SET @term_taxonomy_id = ( SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id = @term_id AND taxonomy = 'pa_style' LIMIT 1 );

-- Step 4: Insert the new variation into wp_posts INSERT INTO wp_posts (post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count) VALUES ( 1, -- post_author (typically admin) NOW(), -- post_date NOW(), -- post_date_gmt '', -- post_content 'Fingers Crossed - From SQL', -- post_title 'Style: From SQL', -- post_excerpt 'publish', -- post_status 'closed', -- comment_status 'closed', -- ping_status '', -- post_password 'from-sql', -- post_name (unique slug) '', -- to_ping '', -- pinged NOW(), -- post_modified NOW(), -- post_modified_gmt '', -- post_content_filtered @parent_product_id, -- post_parent (link to parent product) '', -- guid (can update later) 0, -- menu_order 'product_variation', -- post_type '', -- post_mime_type 0 -- comment_count );

-- Capture the ID of the new variation SET @new_variation_id = LAST_INSERT_ID();

-- Update the GUID for the new variation UPDATE wp_posts SET guid = CONCAT('http://localhost:8000/?post_type=product_variation&p=', @new_variation_id) WHERE ID = @new_variation_id;

-- Step 5: Add metadata for the variation INSERT INTO wp_postmeta (post_id, meta_key, meta_value) VALUES (@new_variation_id, '_regular_price', '123'), (@new_variation_id, '_price', '123'), (@new_variation_id, '_stock_status', 'instock'), (@new_variation_id, '_manage_stock', 'no'), (@new_variation_id, '_backorders', 'no'), (@new_variation_id, '_sold_individually', 'no'), (@new_variation_id, '_virtual', 'no'), (@new_variation_id, '_downloadable', 'no'), (@new_variation_id, '_attribute_pa_style', 'from-sql'); -- Link the attribute to the variation

-- Step 6: Link the parent product and variation to the term -- Link the parent product to the term INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) SELECT @parent_product_id, @term_taxonomy_id, 0 WHERE NOT EXISTS ( SELECT 1 FROM wp_term_relationships WHERE object_id = @parent_product_id AND term_taxonomy_id = @term_taxonomy_id );

-- Link the variation to the term INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) SELECT @new_variation_id, @term_taxonomy_id, 0 WHERE NOT EXISTS ( SELECT 1 FROM wp_term_relationships WHERE object_id = @new_variation_id AND term_taxonomy_id = @term_taxonomy_id );







php plugin function: function cpvg_create_product_variation() {     $parent_product_id = 291; // Replace with your product ID     $attribute_name = 'Style';     $attribute_slug = 'style';     $term_name = 'From SQL2';     $term_slug = 'from-sql2';     $variation_price = '125';

    if ( ! class_exists( 'WooCommerce' ) ) {         echo '<div class="error"><p>WooCommerce is not active. Please activate WooCommerce to use this plugin.</p></div>';         return;     }

    $taxonomy = wc_attribute_taxonomy_name( $attribute_slug );

    // Step 1: Create global attribute if it doesn't exist     if ( ! taxonomy_exists( $taxonomy ) ) {         $attribute_id = wc_create_attribute( [             'name' => $attribute_name,             'slug' => $attribute_slug,             'type' => 'select',             'order_by' => 'menu_order',         ] );

        if ( is_wp_error( $attribute_id ) ) {             echo '<div class="error"><p>Failed to create attribute: ' . $attribute_id->get_error_message() . '</p></div>';             return;         }     }

    // Step 2: Add the term if it doesn't exist     if ( ! term_exists( $term_slug, $taxonomy ) ) {         wp_insert_term( $term_name, $taxonomy, [ 'slug' => $term_slug ] );     }

    // Step 3: Retrieve and merge existing attributes     $product = wc_get_product( $parent_product_id );     if ( ! $product ) {         echo '<div class="error"><p>Parent product not found.</p></div>';         return;     }

    $attributes = $product->get_attributes();

    if ( ! isset( $attributes[ $taxonomy ] ) ) {         $attribute = new WC_Product_Attribute();         $attribute->set_id( wc_attribute_taxonomy_id_by_name( $taxonomy ) );         $attribute->set_name( $taxonomy );         $attribute->set_options( [ $term_slug ] );         $attribute->set_visible( true );         $attribute->set_variation( true );         $attributes[ $taxonomy ] = $attribute;     } else {         $existing_options = $attributes[ $taxonomy ]->get_options();         if ( ! in_array( $term_slug, $existing_options, true ) ) {             $existing_options[] = $term_slug;             $attributes[ $taxonomy ]->set_options( $existing_options );         }     }

    $product->set_attributes( $attributes );     $product->save();

    // Step 4: Add the new variation while preserving existing ones     $variation = new WC_Product_Variation();     $variation->set_parent_id( $parent_product_id );     $variation->set_attributes( [ $taxonomy => $term_slug ] );     $variation->set_regular_price( $variation_price );     $variation->set_stock_status( 'instock' );     $variation->save();

    // Refresh lookup tables for variations     do_action( 'woocommerce_admin_process_product_object', $product );

    echo '<div class="updated"><p>Variation added successfully, and existing data preserved.</p></div>'; }



Thanks!

• This topic was modified 1 year, 4 months ago by Steven Stern (sterndata).
• This topic was modified 1 year, 4 months ago by eranner.