A have a ton of variable products. I’m using cron to run the script every X minutes for fetching stock level from an external source. If I use wc_get_product_id_by_sku
and update_post_meta
it takes too long so I’m trying to find the way to use a custom prepared query.
If I know product_id
query would be like that:
$conn->prepare( "UPDATE wp_postmeta SET meta_value=? WHERE post_id=? AND meta_key=`_stock`" );
But I only know a product SKU. So how to modify this query to update _stock
based on a product SKU?
post_id | meta_key | meta_value |
---|---|---|
10 | _sku | ABCD |
10 | _stock | 25 |
Advertisement
Answer
$wpdb
and JOIN
works too slow, so the fastest way is to use four separate prepared queries:
$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(); } }