Allow administrator to reset WooCommerce product stock in the frontend

I am adding a button on products in shop page only when admin is connected. This button when pressed will reset product stock to zero (0). Sometimes it can be easier to clean out of stock products from website using products images than searching them with their sku. I made this code based on @LoicTheAztec & @Víctor González SO answer to update database tables in that sense and it works like a charm. Below is the working code

function createStockZeroButton(){
    global $product;
    $theId = $product-> get_id();
    if(current_user_can( 'administrator' )){
        echo "<form action='' method='POST'>
                    <button type="submit" class="stockZeroButtonClass" name="."name"."$theId".">set Stock To 0 for id".$theId."</button>
             </form>";
        if(isset($_POST["name".$theId])){
            $con = mysqli_connect($server, $username, $password, $database);
            $database_request = "UPDATE wp_postmeta pm
            INNER JOIN wp_wc_product_meta_lookup pml
                ON pm.post_id =  pml.product_id
            SET pm.meta_value="0", pml.stock_quantity = '0'
            WHERE pm.meta_key = '_stock' AND pm.post_id = '$theId'";
            $database_query = mysqli_query($con, $database_request);
            $database_request ="UPDATE wp_postmeta pm
                INNER JOIN wp_wc_product_meta_lookup pml
                    ON pm.post_id =  pml.product_id
                SET pm.meta_value="outofstock", pml.stock_status="outofstock"
                WHERE pm.meta_key = '_stock_status' AND pm.post_id = '$theId'";
            $database_query = mysqli_query($con, $database_request);
            $database_query = "INSERT IGNORE INTO wp_term_relationships (object_id, term_taxonomy_id)
            SELECT pml.product_id, (SELECT term_id FROM wp_terms WHERE slug = 'outofstock')
            FROM wp_wc_product_meta_lookup pml
            WHERE pml.stock_status="outofstock" AND pm.post_id = '$theId'";
            $database_query = mysqli_query($con, $database_request);
        }
    }
}



add_action('woocommerce_shop_loop_item_title','createStockZeroButton');

I would like now to use the $wpdb class to do it. I have tried this code below ‘according’ to this SO answer but could not get it to work.

function createStockZeroButton(){
    global $product;
    $theId = $product-> get_id();
    if(current_user_can( 'administrator' )){
        echo "<form action='' method='POST'>
                    <button type="submit" class="stockZeroButtonClass" name="."name"."$theId".">set Stock To 0 for id".$theId."</button>
             </form>";
        if(isset($_POST["name".$theId])){
$update1 = $wpdb->query($wpdb->prepare("UPDATE wp_postmeta pm
            INNER JOIN wp_wc_product_meta_lookup pml
                ON pm.post_id =  pml.product_id
            SET pm.meta_value="0", pml.stock_quantity = '0'
            WHERE pm.meta_key = '_stock' AND pm.post_id = '$theId'"));

$update2 = $wpdb->query($wpdb->prepare("UPDATE wp_postmeta pm
                INNER JOIN wp_wc_product_meta_lookup pml
                    ON pm.post_id =  pml.product_id
                SET pm.meta_value="outofstock", pml.stock_status="outofstock"
                WHERE pm.meta_key = '_stock_status' AND pm.post_id = '$theId'"));

$update3 = $wpdb->query($wpdb->prepare("INSERT IGNORE INTO wp_term_relationships (object_id, term_taxonomy_id)
            SELECT pml.product_id, (SELECT term_id FROM wp_terms WHERE slug = 'outofstock')
            FROM wp_wc_product_meta_lookup pml
            WHERE pml.stock_status="outofstock" AND pm.post_id = '$theId'"));
        }
    }
}

add_action('woocommerce_shop_loop_item_title','createStockZeroButton');

How can we achieve correct query using INNER JOIN and $wpdb as in this case?

Looked as well at the official doc link but could not get it clear in my head.

You need to sign in to view this answers

Related Post