Incorrect database table definition in your PHP script

Top WooCommerce & WordPress Plugins Forums Cost of Goods for WooCommerce Incorrect database table definition in your PHP script

Tagged: , ,

  • This topic has 7 replies, 3 voices, and was last updated 1 year ago by Pablo.
Viewing 8 posts - 1 through 8 (of 8 total)
  • Author
    Posts
  • #95731
    blankmichal.vlcek
    Participant

    Hi, your plugin throws us an SQL query error in the log:

    —————–
    [07-Nov-2022 11:46:58 UTC] WordPress database error Unknown column ‘wp_wc_order_product_lookup.product_net_revenue’ in ‘field list’ for query
    SELECT
    DATE_FORMAT(drs_wc_order_product_lookup.date_created, ‘%Y-%m-%d’) AS time_interval , MAX(drs_wc_order_product_lookup.date_created) AS datetime_anchor , SUM(product_qty) as items_sold , SUM(alg_cog_oimc.meta_value * product_qty) AS costs_total , SUM(wp_wc_order_product_lookup.product_net_revenue – alg_cog_oimc.meta_value * product_qty) AS profit_total
    FROM
    drs_wc_order_product_lookup
    JOIN drs_wc_order_stats ON drs_wc_order_product_lookup.order_id = drs_wc_order_stats.order_id LEFT JOIN drs_woocommerce_order_itemmeta alg_cog_oimc ON drs_wc_order_product_lookup.order_item_id = alg_cog_oimc.order_item_id AND alg_cog_oimc.meta_key = ‘_alg_wc_cog_item_cost’
    WHERE
    1=1
    AND drs_wc_order_product_lookup.product_id IN (795) AND ( drs_wc_order_stats.status NOT IN ( ‘wc-auto-draft’,’wc-trash’,’wc-pending’,’wc-failed’,’wc-cancelled’,’wc-checkout-draft’ ) ) AND drs_wc_order_product_lookup.date_created <= ‘2022-11-07 23:59:59’ AND drs_wc_order_product_lookup.date_created >= ‘2022-11-01 00:00:00’

    GROUP BY
    time_interval
    LIMIT 0,100 made by wp_dashboard, do_meta_boxes, WC_Admin_Dashboard->status_widget, WC_Admin_Dashboard->sales_sparkline, rest_do_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, Automattic\WooCommerce\Admin\API\Reports\Products\Stats\Controller->get_items, Automattic\WooCommerce\Admin\API\Reports\Products\Stats\Query->get_data, WC_Data_Store->__call, Automattic\WooCommerce\Admin\API\Reports\Products\Stats\DataStore->get_data
    —————–

    We found that in the script:
    /cost-of-goods-for-woocommerce-pro/includes/analytics/class-alg-wc-cog-analytics-products.php
    on line 72:
    $clauses[] = “, SUM(wp_wc_order_product_lookup.product_net_revenue – alg_cog_oimc.meta_value * product_qty) AS profit_total”;

    You call the default WordPress database table in the SUM function and do not count on the custom prefix.
    It should be right:
    $clauses[] = “, SUM({$wpdb->prefix}wc_order_product_lookup.product_net_revenue – alg_cog_oimc.meta_value * product_qty) AS profit_total”;

    Thanks for the fix in the next version.

    Best regards
    Michal

    #95749
    blankMoshtafizur
    Moderator
    Plugin Support

    Hello Michal,

    I am really sorry to know you are experiencing problems.

    I couldn’t reproduce the issue on our end. Could you please ensure that you are using the latest version of the Cost of Goods(2.7.3) plugin and the WooCommerce(7.0.1) plugin?

    Kind regards,
    Moshtafizur

    #101682
    blankmichal.vlcek
    Participant

    Hi, yes we have Cost of Goods for WooCommerce Pro 2.7.3 and WooCommerce 7.0.1 and the problem still persists.
    The same error above is still written in the PHP log file.

    I already wrote you the fix above and it is directly in the source of your plugin
    in file: /cost-of-goods-for-woocommerce-pro/includes/analytics/class-alg-wc-cog-analytics-products.php
    on line 72:

    $clauses[] = “, SUM(wp_wc_order_product_lookup.product_net_revenue – alg_cog_oimc.meta_value * product_qty) AS profit_total”;

    change to:

    $clauses[] = “, SUM({$wpdb->prefix}wc_order_product_lookup.product_net_revenue – alg_cog_oimc.meta_value * product_qty) AS profit_total”;

    and it will be good.

    Best regards
    Michal

    #101697
    blankMoshtafizur
    Moderator
    Plugin Support

    Hello Michal,

    I am really sorry to know you are still experiencing the problem.

    I have escalated this with our development team . They will get back to you as soon as they can.

    Kind regards,
    Moshtafizur

    #101737
    blankPablo
    Moderator
    Plugin Support

    Thanks Michal,

    I just released a new version (2.7.5) with the fix. Let us know if you need any more help.

    #102047
    blankmichal.vlcek
    Participant

    Hi Pablo, thank you very much for the fix. The new version 2.7.5 no longer shows errors. Perfect!

    Best regards
    Michal

    #102052
    blankmichal.vlcek
    Participant

    Hi Pablo, I got a little ahead of myself :-).

    It says another error.
    You use a different character for the minus sign in the PHP script in the SQL queries.
    You are using a long minus character and the MySQL database does not understand it.

    —————
    [15-Nov-2022 09:51:33 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘– alg_cog_oimc.meta_value * product_qty) AS profit_total
    FROM
    drs_wc…’ at line 2 for query
    SELECT
    DATE_FORMAT(drs_wc_order_product_lookup.date_created, ‘%Y-%m-%d’) AS time_interval , MAX(drs_wc_order_product_lookup.date_created) AS datetime_anchor , SUM(product_qty) as items_sold , SUM(alg_cog_oimc.meta_value * product_qty) AS costs_total , SUM(drs_wc_order_product_lookup.product_net_revenue – alg_cog_oimc.meta_value * product_qty) AS profit_total
    FROM
    drs_wc_order_product_lookup
    JOIN drs_wc_order_stats ON drs_wc_order_product_lookup.order_id = drs_wc_order_stats.order_id LEFT JOIN drs_woocommerce_order_itemmeta alg_cog_oimc ON drs_wc_order_product_lookup.order_item_id = alg_cog_oimc.order_item_id AND alg_cog_oimc.meta_key = ‘_alg_wc_cog_item_cost’
    WHERE
    1=1
    AND drs_wc_order_product_lookup.product_id IN (770) AND ( drs_wc_order_stats.status NOT IN ( ‘wc-auto-draft’,’wc-trash’,’wc-cancelled’,’wc-failed’,’wc-pending’,’wc-on-hold’,’wc-checkout-draft’ ) ) AND drs_wc_order_product_lookup.date_created <= ‘2022-11-15 23:59:59’ AND drs_wc_order_product_lookup.date_created >= ‘2022-11-01 00:00:00’

    GROUP BY
    time_interval
    LIMIT 0,100 made by wp_dashboard, do_meta_boxes, WC_Admin_Dashboard->status_widget, WC_Admin_Dashboard->sales_sparkline, rest_do_request, WP_REST_Server->dispatch, WP_REST_Server->respond_to_request, Automattic\WooCommerce\Admin\API\Reports\Products\Stats\Controller->get_items, Automattic\WooCommerce\Admin\API\Reports\Products\Stats\Query->get_data, WC_Data_Store->__call, Automattic\WooCommerce\Admin\API\Reports\Products\Stats\DataStore->get_data
    ———————-

    You have a typo here:
    /wp-content/plugins/cost-of-goods-for-woocommerce-pro/includes/analytics/class-alg-wc-cog-analytics-products.php
    … line 73:
    $clauses[] = “, SUM({$wpdb->prefix}wc_order_product_lookup.product_net_revenue – alg_cog_oimc.meta_value * product_qty) AS profit_total”;

    Best regards
    Michal

    #102080
    blankPablo
    Moderator
    Plugin Support

    Hi Michal,

    How did that character end up there?

    Anyway, thanks for the tip. I just fixed it on version 2.7.6 I just released. Please, let me know if it’s ok now.

Viewing 8 posts - 1 through 8 (of 8 total)
  • You must be logged in to reply to this topic.