如何按库存直接更新WooCommerce产品库存量 [英] How to directly update WooCommerce product stock quantity by its sku

问题描述

A有一吨多变的产品。我使用cron每X分钟运行一次脚本，以便从外部来源获取库存水平。如果我使用wc_get_product_id_by_sku和update_post_meta，它花费的时间太长，所以我试图找到使用定制准备的查询的方法。 如果我知道product_id查询应该是这样：

$conn->prepare( "UPDATE wp_postmeta SET meta_value=? WHERE post_id=? AND meta_key=`_stock`" );

但我只知道产品SKU。那么，如何修改此查询以基于产品SKU更新_stock？

post_id	元密钥	Meta_Value
10	_sku	shu ju
10	_STOCK	25

推荐答案

$wpdb和JOIN运行速度太慢，因此最快的方法是使用四个单独的准备好的查询：

$conn = mysqli_init();
mysqli_real_connect( $conn, DB_HOST, DB_USER, DB_PASSWORD, DB_NAME );

// Get product ID by SKU
$stmt1 = $conn->prepare( "SELECT post_id FROM {$wpdb->prefix}postmeta WHERE meta_key = '_sku' AND meta_value = ?");

// Update stock level on postmeta table
$stmt2 = $conn->prepare( "UPDATE {$wpdb->prefix}postmeta SET meta_value = ? WHERE meta_key = '_stock' AND post_id = ?");

// Update stock data on wc_product_meta_lookup table
$stmt3 = $conn->prepare( "UPDATE {$wpdb->prefix}wc_product_meta_lookup SET stock_quantity = ?, stock_status = ? WHERE product_id = ?");

// Update stock status on postmeta table
$stmt4 = $conn->prepare( "UPDATE {$wpdb->prefix}postmeta SET meta_value = ? WHERE meta_key = '_stock_status' AND post_id = ?");

foreach ( $products as $product ) {
    $qty        = $product['ON_HAND'];
    $sku        = $product['PRODUCT_SKU'];
    $status     = $qty ? 'instock' : 'onbackorder';

    // Get product ID by SKU
    $stmt1->bind_param( "s", $sku );
    $stmt1->execute();

    $res = $stmt1->get_result();
            
    while ( $row = $res->fetch_assoc() ) {

        $id = $row['post_id'];

        // Update stock level on postmeta table
        $stmt2->bind_param( "dd", $qty, $id );
        $stmt2->execute();

        // Update stock data on wc_product_meta_lookup table
        $stmt3->bind_param( "dsd", $qty, $status, $id );
        $stmt3->execute();

        // Update stock status on postmeta table
        $stmt4->bind_param( "sd", $status, $id );
        $stmt4->execute();
   }
}

这篇关于如何按库存直接更新WooCommerce产品库存量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！