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:

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

  • Are you adding products to the database via your "custom triggers" or are the mysql triggers responding when you insert data into the database via WordPress? Or are you manually inserting WordPress data (products?) into the database and it is appearing in the admin? If you can give me a bit of clarity I believe I can help. – Tom Nov 17 '15 at 11:08
  • Thank you for your quick reply, Tom. It works as follows: - User completes survey in wp_fsq_data table - Trigger notifies insert in this survey table and runs query 1: reading a serialized string in a table 'conv1' - Trigger notifies insert in this conv1 table and runs query 2: labeling numeric values to strings in a temp table 'conv2' - Trigger notifies insert in this conv2 table and runs query 3: inserting/updating all data in the WP structure (wp_posts, wp_terms, wp_postmeta, wp_relationships) - Post appears front-end without attributes in dropdowns/selectors. – Thom Rommens Nov 17 '15 at 11:30

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.

  • 1
    Transients is exactly what the problem is, and why it doesn't update on the front-end. Damn transients! – Solomon Closson Sep 9 '16 at 15:28

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.

  • Thanks again, Tom. Yes, forgot to mention wp_term_taxonomy. Yes, I'm also inserting/updating that one. As the set of attributes does not change and no count is registered in wp_woocommerce_attribute_taxonomies, what exactly needs to be updated there? Thank you for those links. I'll dive into it and let you know! Cheers, Thom – Thom Rommens Nov 17 '15 at 13:02
  • Thom, Did this work for you? – Tom Oct 18 '17 at 12:35

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/

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.