Programatically update Custom Attributes WooCommerce

Question

First of all, thank you for being part of an impressive community that has helped me out a thousand times before.

In my Wordpress environment (Enfold theme, Woocommerce plugin) I created a system to use a survey as input for instantly to-be-created posts in a front-end website. Answers in the survey are included in automatically generated posts (type 'product') including WooCommerce Custom Attributes.

I wrote custom queries responding to triggers in mySQL - inserting & updating wp_posts, wp_terms, wp_postmeta, wp_term_relationships.

All works fine, except for one thing: WooCommerce custom product attributes are not displayed instantly in dropdown/select filters on the website. However, 'manually' pressing Update on the Wordpress-page does the job.

I'm looking for a way to exactly replicate the 'manually' pressing Update in the Wordpress CMS. So far, the post itself succesfully loads in the website, but its WooCommerce product attributes are not instantiated.

So far, I've tried many workarounds:

• Programmatically setting the status of a post (draft) and changing it instantly to a published post (publish);
• Using the in-built post Scheduling of Wordpress by setting a time on which it should be published;
• Calling wp_update_post;
• Using the Tao Scheduled Updates plugin (https://nl.wordpress.org/plugins/tao-schedule-update/);
• Using the WordPress PostController plugin (https://github.com/HarriBellThomas/Wordpress_PostController)

Still, all the attempts only update the posts in wp_posts, apparently not the underlying relations/custom product attributes.

My only request would be to programatically replicate the clicking on 'Update' in the Wordpress CMS environment. This would help me in automating the process instead of intervening myself manually.

Could you please suggest me a way on how to do that?

Many thanks for your time!

UPDATE:

Hi Tom,

After diving into your answer, still I don´t understand.

My queries update / insert all of the necessary tables. They practically follow all of the steps from your referred php scripts.

When I check a product in the CMS, I see all of the data - and especially the attributes - correctly loaded. When I press Update manually in the CMS and compare the data in the mySQL tables before and after update, I don't see significant differences. The only thing I see is the value for _product_attributes in wp_postmeta which is slightly different (serialized string).

However, when I experiment with this value (emptying / changing it for the specific post in wp_postmeta), nothing changes in the attributes - and all still works when filtering.

Which brings me to the questions:

• What exactly happens when pressing 'Update' manually?
• Is there another storage except for the mySQL tables in which parts are somehow connected?
• Would it be possible for me to 1) take the 'create product' procedure you referred to for granted and skip it (as I ran my own queries) and then 2) only call the exact procedure that runs when pressing Update - to let it 'tie' everything together without re-creating all of the database entries?
• If so, where would I find this specific procedure?

Your help is much appreciated. Thanks! Thom

Answer 1

OK so I had a variation of this same problem myself and I'll walk you through it.

All works fine, except for one thing: WooCommerce custom product attributes are not displayed instantly in dropdown/select filters on the website. However, 'manually' pressing Update on the Wordpress-page does the job.

When displaying attributes woocommerce does this function: wc_get_attribute_taxonomies() (http://woocommerce.wp-a2z.org/oik_api/wc_get_attribute_taxonomies/)

You'll notice in that function that it is getting the data from a 'transient' (think cached data, see http://codex.wordpress.org/Transients_API).

If you are manually uploading data into the database, chances are, you've missed uploading the transient (which goes into wp_options by the way). By hitting update in the Wordpress admin panel you run the function which updates the transient which is why they work/show then and not before.

To make this work you need to make sure that your manual updating of the MySQL also updates the transient. For me, manually adding product attributes in woocommerce, I had to add a row in wp_options with options_name _transient_wc_attribute_taxonomies and my attributes in the serialized array in the option_value column (plus an ID autoload set to yes). But there are other transients so it depends on which function is causing the issue and what you are trying to display. You can see which one you are missing by doing a MySQL search for %transient% in wp_options once before you click update in the wordpress admin panel and once after then comparing the data.

Answer 2

Are you also updating the terms inside the Woocommerce product? Wp_term_taxonomy and wp_woocommerce_attribute_taxonomies both need to be updated with your taxonomy terms to allow the Woocommerce product to display "Color" or "Size" variation dropdowns.

When creating a Woocommerce product programmatically, Product Variations need to be inserted and tied into existing attributes (taxonomies created within Woocommerce).

Look at the post meta key "_product_attributes" which holds the attribute data. You will still need to associate the terms (attributes) with your product (inside wp_term_taxonomy) but hopefully this gives you a direction to move in.

Also take a look at the functions create_product (https://github.com/woothemes/woocommerce/blob/master/includes/api/class-wc-api-products.php#L237) and save_variations (https://github.com/woothemes/woocommerce/blob/master/includes/api/class-wc-api-products.php#L1692) which both outline how you perform the creation of the product and its variations.

Answer 3

This is one option with PHP code:

function change_price_by_type( $product_id, $price, $price_type) {
   update_post_meta( $product_id, '_' . $price_type, $price );
}

function change_price_all_types( $product_id, $price ) {
    change_price_by_type( $product_id, $price, 'price' );
    change_price_by_type( $product_id, $price, 'sale_price' );
    change_price_by_type( $product_id, $price, 'regular_price');
}


function change_product_price( $product_id, $price ) {
    change_price_all_types( $product_id, $price ); 
    $product = wc_get_product( $product_id ); // Handling variable products

    if ( $product->is_type( 'variable' ) ) {
        // RM: Get variations which are also out of stock
        $args = array(
        'post_type'     => 'product_variation',
        'post_status'   => array( 'private', 'publish' ),
        'numberposts'   => -1,
        'orderby'       => 'menu_order',
        'order'         => 'asc',
        'post_parent'   => $product_id // $post->ID 
        );
        $variations = get_posts( $args ); 
        foreach ( $variations as $variation ) {
                change_price_all_types( $variation->ID, $price );
        }
    }
}

$args     = array( 'post_type' => 'product', 'posts_per_page' => 3000);
$products = get_posts( $args ); 

foreach($products as $product) {
    change_product_price($product->ID, 9.99);
    echo $product->ID;
}

exit;

then go to phpMyAdmin and run following SQL queries (you might need to change your table name instead of wp_).

UPDATE `wp_postmeta`
SET `meta_value` = ''
WHERE `meta_key` IN ('_sale_price', '_sale_price_dates_from', '_sale_price_dates_to')
  AND `post_id` IN
    (SELECT `ID`
     FROM `wp_posts`
     WHERE `post_type` = 'product'
       AND `post_status` = 'publish' );

Clear cache:

DELETE
FROM `wp_options`
WHERE (`option_name` LIKE '_transient_wc_var_prices_%'
    OR `option_name` LIKE '_transient_timeout_wc_var_prices_%')

SQL code was taken from: http://www.technoedu.com/forums/topic/wordpress-solved-copy-woocommerce-products-sale-prices-to-regular-prices-and-reset-sale-prices/