Dealing With Incorrect Results in Zen Cart’s Best Products Purchased Report After Upgrade

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, 1.5.7c:

While there are only 7 products shown, the stats at the bottom of the table say there are 20 shown out of 27,899.

Previously the report showed three products:

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.

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:

161
162
163
164

          $products_query_raw = "SELECT SUM(products_quantity) AS products_ordered, products_name, products_id
                                 FROM " . TABLE_ORDERS_PRODUCTS . "
                                 GROUP BY products_id, products_name
                                 ORDER BY products_ordered DESC, products_name";

The previous version looks like this:

193
194
195
196
197
198
199

  $products_query_raw = "SELECT p.products_id, sum(p.products_ordered) as products_ordered, pd.products_name
                         FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                         WHERE pd.products_id = p.products_id
                         AND pd.language_id = '" . $_SESSION['languages_id']. "'
                         AND p.products_ordered > 0
                         GROUP BY p.products_id, pd.products_name
                         ORDER BY p.products_ordered DESC, pd.products_name";