Help with SQL Query for New Contribution

[royalfunk]

I am currently almost done with a very simple mod/contribution that will enhance the stats_products_purchased.php file in Admin. For those of us using the Order Editor Contribution, or those who manually edit orders after they have been placed, this is a big help. By default this page displays the best products purchased based on the information stored in the products table. If this data is to be precise and always up-to-date the total quantity sold should be grabbed from the orders_products table. I have coded this all the way except for being able to have a column that will show the product description. Not everyone may want this, but I would like to include it. I am not the best at SQL queries so if someone could help me fix the code below so the page will show the product description, I would GREATLY appreciate it. Also, credit will be given in the install file of the Contribution. THANKS!!

 

<?php
 if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;
 $products_query_raw = "select p.products_id, p.products_quantity, pd.products_name from " . TABLE_ORDERS_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where pd.products_id = p.products_id and pd.language_id = '" . $languages_id. "' and p.products_quantity > 0 group by pd.products_id order by p.products_quantity DESC, pd.products_name";
 $products_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $products_query_raw, $products_query_numrows);

 $rows = 0;
 $products_query = tep_db_query($products_query_raw);
 while ($products = tep_db_fetch_array($products_query)) {
   $rows++;

   if (strlen($rows) < 2) {
     $rows = '0' . $rows;
   }
?>

[Mary B.]

The only thing missing from the query was selecting the pd.products_description along with all the rest. I ran this query in phpMyAdmin, and it does grab the description from the products_description table.

 

 

<?php
if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;
$products_query_raw = "select p.products_id, p.products_quantity, pd.products_name, pd.products_description  from " . TABLE_ORDERS_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where pd.products_id = p.products_id and pd.language_id = '" . $languages_id. "' and p.products_quantity > 0 group by pd.products_id order by p.products_quantity DESC, pd.products_name";
$products_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $products_query_raw, $products_query_numrows);

$rows = 0;
$products_query = tep_db_query($products_query_raw);
while ($products = tep_db_fetch_array($products_query)) {
  $rows++;

  if (strlen($rows) < 2) {
    $rows = '0' . $rows;
  }
?>

[royalfunk]

Thanks Mary! Unfortunately I just noticed I might not be as close to done as I originally thought. I ran a test of looking up a couple of products and seeing exactly how many of those units have been sold total. They do not match the number I get on the stats page in Admin so I must be missing something else with this query. Any ideas?

[royalfunk]

Finally figured out the code and will be submitting a Contribution very soon. In case you are someone viewing this post for an answer to this question/issue, here is the fix. This should work even if you have no other Contributions installed. In my opinion it is just a much more "fail proof" way of precisely reporting the total quantity of each product ordered:

 

Change:

<?php
 if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;
 $products_query_raw = "select p.products_id, p.products_description, p.products_ordered, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where pd.products_id = p.products_id and pd.language_id = '" . $languages_id. "' and p.products_ordered > 0 group by pd.products_id order by p.products_ordered DESC, pd.products_name";
 $products_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $products_query_raw, $products_query_numrows);

 $rows = 0;
 $products_query = tep_db_query($products_query_raw);
 while ($products = tep_db_fetch_array($products_query)) {
   $rows++;

   if (strlen($rows) < 2) {
     $rows = '0' . $rows;
   }
?>

 

 

 

 

To:

<?php
 if (isset($HTTP_GET_VARS['page']) && ($HTTP_GET_VARS['page'] > 1)) $rows = $HTTP_GET_VARS['page'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;
 $products_query_raw = "select p.products_id, sum(p.products_quantity) as quantitysum, pd.products_name, pd.products_description from " . TABLE_ORDERS_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where pd.products_id = p.products_id and pd.language_id = '" . $languages_id. "' group by pd.products_id order by quantitysum DESC, pd.products_name";
 $products_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $products_query_raw, $products_query_numrows);

 $rows = 0;
 $products_query = tep_db_query($products_query_raw);
 while ($products = tep_db_fetch_array($products_query)) {
   $rows++;

   if (strlen($rows) < 2) {
     $rows = '0' . $rows;
   }
?>

 

 

And then change:

<?php echo $products['products_ordered']; ?>

 

 

To:

<?php echo $products['quantitysum']; ?>