0

Since woo 3 featured product is name and slug of taxonomy 'product_visibility'.

I need to update all products with same meta_value contained in list to 'featured'.

My products have a meta_key = 'product_cip'

My list of cip to compare with 'product_cip' are (1234657,1263709,4563832,837472) etc.

I'm able to select all featured product like this but no figure how to update :

SELECT post_title, ID FROM wp_posts
INNER JOIN wp_postmeta wm ON (wm.post_id = wp_posts.ID)
INNER JOIN wp_term_relationships wtr ON (wp_posts.ID = wtr.object_id)
INNER JOIN wp_term_taxonomy wtt ON (wtr.term_taxonomy_id = wtt.term_taxonomy_id)
INNER JOIN phiz_terms wt ON (wt.term_id = wtt.term_id) AND ((wtt.taxonomy = 'product_visibility' AND  wt.slug = 'featured'))
    WHERE post_type = 'product' AND post_status = 'publish'

Thanks,

EDIT:

global $wpdb;

// Select all products needed to be updated as 'featured'
    $q = 'SELECT wp_posts.ID ';
    $q .= 'FROM wp_posts ';

// Attach wp_postmeta table
    $q .= 'JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) ';
// Attach taxonomy and term tables
    $q .= 'JOIN wp_term_relationships ON ( wp_posts.ID = wp_term_relationships.object_id ) ';
    $q .= 'JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id ) ';
    $q .= 'JOIN wp_terms ON ( wp_term_taxonomy.term_id = wp_terms.term_id ) ';

// WHERE statements
    $q .= 'WHERE ';
// Post should be published
    $q .= 'wp_posts.post_status =  "publish" ';

    $q .= 'AND ';
// Post should have either product as post_type
    $q .= 'wp_posts.post_type =  "product" ';

    $q .= 'AND ';
// Post should have meta_key = product_cip
    $q .= '(';
    $q .= 'wp_postmeta.meta_key =  "product_cip" ';
    $q .= 'AND ';
    $q .= 'wp_postmeta.meta_value IN ("3664592000014","3401097399423","4015630064779","3400941662003","3400941631245","3401321104311","3401051049166","3400936760578","3400938341836","3401021104543","3400939035765","4015630057009","3401097592640","3401060246723","3662042003295","3401096745795","3401097024363","3401099724896","3400935709042","3400935167156","3401021104482","3400936751934","3401096745856","3400936348288","3400934965852","3400922096612","3400933043445","3664490000031","3400932897162","7323190196562","3400936666795","3400939472898") ';
    $q .= ')';

    $wpdb->get_results($q);

With this I get the product needed to be updated, how change this result to featured product ?

EDIT 2:

function update_products_tofeatured_for_home($atts)
{
    global $wpdb;

// Select all products needed to be updated as 'featured'
    $q = 'SELECT wp_posts.ID ';
    $q .= 'FROM wp_posts ';

// Attach wp_postmeta table
    $q .= 'JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) ';
// Attach taxonomy and term tables
    $q .= 'JOIN wp_term_relationships ON ( wp_posts.ID = wp_term_relationships.object_id ) ';
    $q .= 'JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id ) ';
    $q .= 'JOIN wp_terms ON ( wp_term_taxonomy.term_id = wp_terms.term_id ) ';

// WHERE statements
    $q .= 'WHERE ';
// Post should be published
    $q .= 'wp_posts.post_status =  "publish" ';

    $q .= 'AND ';
// Post should have either product as post_type
    $q .= 'wp_posts.post_type =  "product" ';

    $q .= 'AND ';
// Post should have meta_key = product_cip
    $q .= '(';
    $q .= 'wp_postmeta.meta_key =  "product_cip" ';
    $q .= 'AND ';
    $q .= 'wp_postmeta.meta_value IN ("3664592000014","3401097399423","4015630064779","3400941662003","3400941631245","3401321104311","3401051049166","3400936760578","3400938341836","3401021104543","3400939035765","4015630057009","3401097592640","3401060246723","3662042003295","3401096745795","3401097024363","3401099724896","3400935709042","3400935167156","3401021104482","3400936751934","3401096745856","3400936348288","3400934965852","3400922096612","3400933043445","3664490000031","3400932897162","7323190196562","3400936666795","3400939472898") ';
    $q .= ')';

    $products = $wpdb->get_results($q);

    //var_dump($products);

    foreach ($products as $prd)
    {
        $term_taxonomy_ids = wp_set_object_terms( $prd, 'featured', 'product_visibility' );

        if ( is_wp_error( $term_taxonomy_ids ) ) {
            // There was an error somewhere and the terms couldn't be set.
        } else {
            // Success!
        }
    }
}

Not yet tested.

4
  • I suggest you use wp_query and combine meta and taxonomy query in it to select and update products and wodpress will make sure all tables are updated accordingly.
    – Mohsin
    Jan 26 '20 at 10:36
  • Thanks @Mohsin edited my question with first part of code to get all product needed to be updated. But do not how update to featured
    – ilanb
    Jan 26 '20 at 13:32
  • I see, so WooCommerce since 3.0 version uses taxonomy to keep track of featured products. So once you have got IDs , you need to update taxonomy term for those products i.e. taxonomy name is product_visibility and term to apply is 'featured' .. see this function to assign term to a post developer.wordpress.org/reference/functions/wp_set_object_terms
    – Mohsin
    Jan 26 '20 at 13:38
  • @Mohsin added foreach with function, not yet tested, need to make un admin plugin first... but it seems to be good ?
    – ilanb
    Jan 26 '20 at 15:03
0

Ok I'v created a admin plugin to do this... If you have better idea :-) I'm in

Create a admin button to open a csv file of meta_value to compare with existing products. and update products as featured if exist.

add_action('admin_menu', 'featured_button_menu');
function featured_button_menu()
{
    add_menu_page('Featured Products Page', 'Mise en avant produits', 'manage_options', 'featured-button-slug', 'featured_button_admin_page');
}

function featured_button_admin_page()
{
    // General check for user permissions.
    if (!current_user_can('manage_options'))
    {
        wp_die( __('You do not have sufficient pilchards to access this page.'));
    }

    // Start building the page
    echo '<div class="wrap">';
    echo '<h2>Mettre à jour les produits mise en avant (CSV)</h2>';

    // Check whether the button has been pressed AND also check the nonce
    if (isset($_POST['featured_button']) && check_admin_referer('featured_button_clicked'))
    {
        // the button has been pressed AND we've passed the security check
        featured_button_action();
    }
    echo '<form action="options-general.php?page=featured-button-slug" method="post">';
    // WordPress security feature
    wp_nonce_field('featured_button_clicked');
    echo '<input type="hidden" value="true" name="featured_button" />';
    submit_button('Lancer la procédure');
    echo '</form>';
    echo '</div>';
}

function featured_button_action()
{
    global $wpdb;

    $filepath = $_SERVER['DOCUMENT_ROOT']."/data/featured.csv";

    $cip_array = [];
    $resInt = [];

    $handle = fopen($filepath, "r") or die("Error opening file");
    while(($line = fgetcsv($handle, 1000, ";")) !== FALSE) {
        $cip_array[] = $line;
    }
    fclose($handle);

    foreach($cip_array as $res) {
        $resUTF8 = mb_convert_encoding($res[0], 'UTF-8');
        $resInt[] = intval($resUTF8);
    }
    $List = implode(',', $resInt);

    $products = $wpdb->get_results("SELECT phiz_posts.ID
                            FROM phiz_posts
                                JOIN phiz_postmeta ON ( phiz_posts.ID = phiz_postmeta.post_id )
                                JOIN phiz_term_relationships ON ( phiz_posts.ID = phiz_term_relationships.object_id )
                                JOIN phiz_term_taxonomy ON ( phiz_term_relationships.term_taxonomy_id = phiz_term_taxonomy.term_taxonomy_id )
                                JOIN phiz_terms ON ( phiz_term_taxonomy.term_id = phiz_terms.term_id )
                            WHERE phiz_posts.post_status =  'publish'
                                AND phiz_posts.post_type =  'product'
                                AND phiz_postmeta.meta_key =  'product_cip'
                                AND phiz_postmeta.meta_value IN (.$List.) ");

    foreach ($products as $prd)
    {
        $term_taxonomy_ids = wp_set_object_terms( $prd->ID, 'featured', 'product_visibility' );
        if ( is_wp_error( $term_taxonomy_ids ) ) {
            echo "There was an error somewhere and the terms couldn't be set.<br>";
        }
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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