Get the count of all “In stock” products in WooCom

Question

I have a site where products are considered trade/deal. Therefore, when someone take a trade (buy a product), it become out of stock.

What would be the PHP snippet to display the remaining numbers of product currently available (basically In Stock) ?

ex: Hurry Up! Only 10 trades (woocommerce -> products) available!

Thanks in advance!

I tried the code provided :

function fp2() {
    global $wpdb; 

    $stock = get_post_meta( $post->ID, '_stock', true ); 

    echo '<span style="color:#fff;text-align:center;font-size:12px">Remaining Trade:' . $stock;
}
add_shortcode('fp7', 'fp2');

Answer 1

Here is a custom function with a SQL query that will return the products "instock" count:

function get_instock_products_count(){
    global $wpdb;

    // The SQL query
    $result = $wpdb->get_col( "
        SELECT COUNT(p.ID)
        FROM {$wpdb->prefix}posts as p
        INNER JOIN {$wpdb->prefix}postmeta as pm ON p.ID = pm.post_id
        WHERE p.post_type LIKE '%product%'
        AND p.post_status LIKE 'publish'
        AND pm.meta_key LIKE '_stock_status'
        AND pm.meta_value LIKE 'instock'
    " );

    return reset($result);
}

Code goes in function.php file of your active child theme (or theme) or also in any plugin file.

Tested and working

---

Usage example (in any php file):

$count = get_instock_products_count();
$message = sprintf( __( 'Hurry Up! Only %s remaining trades' ), $count );
echo '<div class="woocommerce-message">'.$message.'</div>';

will display something like: