php - Get the sales count of a WooCommerce product for a period of time - Stack Overflow

admin管理员组

文章数量:1300023

I am developing a function that gives me the results of sales on a product in a certain period of time, in order to correctly fill in the data to send to Google Merchant. So I created a mysql query that works from phpmyadmin and I transformed it into php. But while in mysql it works perfectly in php it always gives me a result equal to an empty string.

I need to convert this mysql to php

SELECT COUNT(product_id) 
FROM wp_wc_order_product_lookup 
WHERE product_id = 4098995 
  AND date_created < '2025-02-11' 
  AND date_created > '2017-02-11' 
limit 50

this is my php

    $sql = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(product_id) FROM $wpdb->wp_wc_order_product_lookup WHERE product_id = 4098995 AND date_created < '2025-02-11' AND date_created > '2017-02-11' limit 50"),$product_id,$date_from,$date_to );

but I don't know why it doesn't give me the results, while if I use phpmyadmin I have 28 as a count

this is the complete function

global $product;
global $wpdb;

$sku = $sku.$sex;

$product_id = $wpdb->get_var( $wpdb->prepare( "SELECT post_id FROM $wpdb->postmeta WHERE meta_key='_sku' AND meta_value='%s' LIMIT 1", $sku ) );

$date_to = date("Y-m-d", strtotime('-96 months'));
$date_from = date("Y-m-d");


$product_id = 4098995;

$tot = get_post_meta($product_id,'total_sales',true);

echo "vendite totali ". $tot . " id prodotto ". $product_id. " dalla data ". $date_from . " fino alla data ".$date_to;

$sql = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(product_id) FROM $wpdb->wp_wc_order_product_lookup WHERE product_id = 4098995 AND date_created < '2025-02-11' AND date_created > '2017-02-11' limit 50"),$product_id,$date_from,$date_to );

echo " TEST SQL " . $sql;

I am developing a function that gives me the results of sales on a product in a certain period of time, in order to correctly fill in the data to send to Google Merchant. So I created a mysql query that works from phpmyadmin and I transformed it into php. But while in mysql it works perfectly in php it always gives me a result equal to an empty string.

I need to convert this mysql to php

SELECT COUNT(product_id) 
FROM wp_wc_order_product_lookup 
WHERE product_id = 4098995 
  AND date_created < '2025-02-11' 
  AND date_created > '2017-02-11' 
limit 50

this is my php

    $sql = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(product_id) FROM $wpdb->wp_wc_order_product_lookup WHERE product_id = 4098995 AND date_created < '2025-02-11' AND date_created > '2017-02-11' limit 50"),$product_id,$date_from,$date_to );

but I don't know why it doesn't give me the results, while if I use phpmyadmin I have 28 as a count

this is the complete function

global $product;
global $wpdb;

$sku = $sku.$sex;

$product_id = $wpdb->get_var( $wpdb->prepare( "SELECT post_id FROM $wpdb->postmeta WHERE meta_key='_sku' AND meta_value='%s' LIMIT 1", $sku ) );

$date_to = date("Y-m-d", strtotime('-96 months'));
$date_from = date("Y-m-d");


$product_id = 4098995;

$tot = get_post_meta($product_id,'total_sales',true);

echo "vendite totali ". $tot . " id prodotto ". $product_id. " dalla data ". $date_from . " fino alla data ".$date_to;

$sql = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(product_id) FROM $wpdb->wp_wc_order_product_lookup WHERE product_id = 4098995 AND date_created < '2025-02-11' AND date_created > '2017-02-11' limit 50"),$product_id,$date_from,$date_to );

echo " TEST SQL " . $sql;

• php
• mysql
• wordpress
• woocommerce
• product

Share Improve this question Follow edited Feb 11 at 15:24 LoicTheAztec 254k2424 gold badges396396 silver badges443443 bronze badges asked Feb 11 at 14:43 Vito SantimoneVito Santimone 5588 bronze badges 5

• 2 What's the point in preparing a SQL statement without any parameters? – Nico Haase Commented Feb 11 at 14:46
• Use {$wpdb->prefix}wc_order_product_lookup instead of $wpdb->wp_wc_order_product_lookup ensuring that you declared before global $wpdb;. – LoicTheAztec Commented Feb 11 at 14:47
• LoicTheAztec nothing changes – Vito Santimone Commented Feb 11 at 14:50
• Great!!!!! This is the solution {$wpdb->prefix}wc_order_product_lookup – Vito Santimone Commented Feb 11 at 14:51
• There are multiple mistakes in your code attempt… So I have answered below. – LoicTheAztec Commented Feb 11 at 15:13

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

There are some mistakes in your code attempt, to get the sales count for a product from a specific date:

• You need to replace "wp_" prefix from the table string name with {$wpdb->prefix},
• The WPDB prepare() method, requires placeholders, for each variable,
• With WPDB prepare() method, each comma separated variable is inside, not outside,
• The "date to" is not needed in your query,
• The limit argument is not needed as you are trying to get the count.

See WPDB() WordPress Class Documentation.

Try the following instead (with a "from" date):

global $wpdb;

$product_id = 4098995;

$count = $wpdb->get_var( $wpdb->prepare( "
    SELECT COUNT(product_id) 
    FROM {$wpdb->prefix}wc_order_product_lookup 
    WHERE product_id = %d 
    AND date_created > '%s';
", $product_id, date("Y-m-d", strtotime('-8 years') ) ) );

It should work.

---

Using a date range

With a date range you can use the BETWEEN operator instead like:

global $wpdb;

$product_id = 4098995;
$from_date  = date("Y-m-d", strtotime('-8 years') );
$to_date    = date("Y-m-d", strtotime('-1 years') );

$count = $wpdb->get_var( $wpdb->prepare( "
    SELECT COUNT(product_id) 
    FROM {$wpdb->prefix}wc_order_product_lookup 
    WHERE product_id = %d 
    AND date_created BETWEEN '%s' AND '%s';
", $product_id, $from_date, $to_date ) );

本文标签： phpGet the sales count of a WooCommerce product for a period of timeStack Overflow