woocommerce offtopic - Update a list of product to featured if product have meta value (Mysql not wpquery)

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.

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.

• woocommerce-offtopic
• taxonomy
• mysql
• meta-value

Share Improve this question edited Jan 26, 2020 at 15:01 ilanb asked Jan 26, 2020 at 10:04 ilanbilanb 9333 silver badges1212 bronze badges 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. – Muhammad Asad Commented Jan 26, 2020 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 Commented Jan 26, 2020 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 – Muhammad Asad Commented Jan 26, 2020 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 Commented Jan 26, 2020 at 15:03

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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>";
        }
    }
}