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.
Jese Leos
18 hours ago
Verified user
You should better use instead the related WooCommerce setter methods. Try the following instead: function product_zero_stock_admin_button(){ global $product; if( wc_current_user_has_role('administrator') ) : $id = $product->get_id(); echo '<form method="POST" action=""> <button type="submit" class="reset-stock" name="reset-stock-'.$id.'">Set Stock To 0 for id '.$id.'</button> </form>'; if( isset($_POST['reset-stock-'.$id]) ){ $product->set_stock_quantity(0); // Reset stock quantity $product->set_stock_status('outofstock'); // Set stock status "Out of stock" $product->save(); // Sync and save data } endif; } add_action( 'woocommerce_shop_loop_item_title', 'product_zero_stock_admin_button' ); It should better work, in a simple way. Note that this will mostly work on simple products.