Get and display products On Sale count in WooCommerce [on hold]

96
December 26, 2018, at 03:40 AM

By doing an SQL query (does not seem to work properly) which counts how many products that are on sale (it counts how many products that have discounted prices), my goal is to display a message on the main archive (shop) using a hook. The message should contain the count (how many products that are on sale).

Desired example output: "Looking for a great deal? We currently have XX products on sale!"

Any ideas or insight as to why this is not working? Here's my code:

function get_onsale_products_count() {
    global $wpdb;
    // SQL query for counting products that are on sale
    $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 '_on_sale'
    " );
    return reset($result);
}
add_action('woocommerce_before_single_product_summary', 'get_onsale_products_count');
$count = get_onsale_products_count();
$great_deal = sprintf( __( 'Want to make a good deal? We\'ve got %s products on sale!' ), $count );
echo '<span class="great-deal">'.$great_deal.'</span>';
Answer 1

Stock status has nothing to do with "on sale" products and _on_sale meta value doesn't exist, so that's why your SQL query doesn't work.

On sale products have a defined "sale price", So try instead:

function get_onsale_products_count() {
    global $wpdb;
    // SQL query for counting products that are on sale
    return $wpdb->get_var( "
        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 = 'publish'
        AND pm.meta_key = '_sale_price'
        AND pm.meta_value != ''
    " );
}
add_action( 'woocommerce_before_single_product_summary', 'display_onsale_products_count_message', 3 );
function display_onsale_products_count_message(){
    echo '<span class="great-deal">' . sprintf( __( "Want to make a good deal? We've got %s products on sale!" ), 
    get_onsale_products_count() ) . '</span>';
}

Code goes in function.php file of your active child theme (or active theme). Tested and works.

Rent Charter Buses Company
READ ALSO
php contact form, is it possible to check if an automated email has been received?

php contact form, is it possible to check if an automated email has been received?

I have been digging around to look for suggestions on how to ensure an email address that has been inputted in a web form is valid or notIt seems like the best solution is to send an automated email to the email address the user has submitted, if they receive...

138
How to improve and optimize search in php / mysqli

How to improve and optimize search in php / mysqli

I need to make a search like OLX for my website which is a adpost website like OLX

140
Get localized string on the fly

Get localized string on the fly

I am trying to run a seeder for getting a string in different locale (I stored the lang file in lang/esjson)

150