{"id":4552,"date":"2021-03-24T10:00:35","date_gmt":"2021-03-24T16:00:35","guid":{"rendered":"https:\/\/www.whitefirdesign.com\/blog\/?p=4552"},"modified":"2021-03-24T08:56:57","modified_gmt":"2021-03-24T14:56:57","slug":"dealing-with-incorrect-results-in-zen-carts-best-product-purchased-report-after-upgrade","status":"publish","type":"post","link":"https:\/\/www.whitefirdesign.com\/blog\/2021\/03\/24\/dealing-with-incorrect-results-in-zen-carts-best-product-purchased-report-after-upgrade\/","title":{"rendered":"Dealing With Incorrect Results in Zen Cart’s Best Products Purchased Report After Upgrade"},"content":{"rendered":"

While the Zen Cart team usually resolves bug introduced in to new versions quickly, an issue with the Best Products Purchased report has existed for over two years. The issue produces obviously incorrect data in the report. Here was the result it was producing on a website we just upgraded to the latest version of Zen Cart<\/a>, 1.5.7c:<\/p>\n

\"\"<\/a>While there are only 7 products shown, the stats at the bottom of the table say there are 20 shown out of 27,899.<\/p>\n

Previously the report showed three products:<\/p>\n

\"\"<\/a>The different result is due to a change made to the query used to get the relevant data from the database. The comment included with the changed code that caused this is “new query uses real order info from the orders_products table, and is theoretically more accurate”. The results are clearly not always more accurate, though.<\/p>\n

A quick fix for this is to restore the SQL query used to generate the results as of the last working version, 1.5.5f. In the file \/[admin directory]\/stats_products_purchased.php, the relevant line to change looks like this in version 1.5.7c:<\/p>\n

          $products_query_raw = \"SELECT SUM(products_quantity) AS products_ordered, products_name, products_id\r\n                                 FROM \" . TABLE_ORDERS_PRODUCTS . \"\r\n                                 GROUP BY products_id, products_name\r\n                                 ORDER BY products_ordered DESC, products_name\";<\/pre>\n
The previous version looks like this:<\/p>\n
  $products_query_raw = \"SELECT p.products_id, sum(p.products_ordered) as products_ordered, pd.products_name\r\n                         FROM \" . TABLE_PRODUCTS . \" p, \" . TABLE_PRODUCTS_DESCRIPTION . \" pd\r\n                         WHERE pd.products_id = p.products_id\r\n                         AND pd.language_id = '\" . $_SESSION['languages_id']. \"'\r\n                         AND p.products_ordered > 0\r\n                         GROUP BY p.products_id, pd.products_name\r\n                         ORDER BY p.products_ordered DESC, pd.products_name\";<\/pre>\n","protected":false},"excerpt":{"rendered":"
While the Zen Cart team usually resolves bug introduced in to new versions quickly, an issue with the Best Products Purchased report has existed for over two years. The issue produces obviously incorrect data in the report. Here was the result it was producing on a website we just upgraded to the latest version of … Continue reading “Dealing With Incorrect Results in Zen Cart’s Best Products Purchased Report After Upgrade”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[220],"tags":[],"class_list":["post-4552","post","type-post","status-publish","format-standard","hentry","category-zen-cart-issues"],"_links":{"self":[{"href":"https:\/\/www.whitefirdesign.com\/blog\/wp-json\/wp\/v2\/posts\/4552","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.whitefirdesign.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.whitefirdesign.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.whitefirdesign.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.whitefirdesign.com\/blog\/wp-json\/wp\/v2\/comments?post=4552"}],"version-history":[{"count":10,"href":"https:\/\/www.whitefirdesign.com\/blog\/wp-json\/wp\/v2\/posts\/4552\/revisions"}],"predecessor-version":[{"id":4567,"href":"https:\/\/www.whitefirdesign.com\/blog\/wp-json\/wp\/v2\/posts\/4552\/revisions\/4567"}],"wp:attachment":[{"href":"https:\/\/www.whitefirdesign.com\/blog\/wp-json\/wp\/v2\/media?parent=4552"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.whitefirdesign.com\/blog\/wp-json\/wp\/v2\/categories?post=4552"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.whitefirdesign.com\/blog\/wp-json\/wp\/v2\/tags?post=4552"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}