limit also purchased query by date

[knifeman]

Forgive me if this is considered a double post. It is kind of a continuation although knid of a spin off as well.

 

Anyway, I have determined that the also purchased query is taking 5 seconds or more for my older products. They have been purchased many times and are now linked to numerous others. Using Chemos outpug queries debug, I have this query:

select p.products_id, p.products_image from orders_products opa, orders_products opb, orders o, products p where opa.products_id = '31' and opa.orders_id = opb.orders_id and opb.products_id != '31' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit 6

 

Changing the six to a three makes no real difference, but my idea is to limit the query to orders placed within the last 12 months. Less data to search through should equal quicker results.

I have no clue how to accomplish this code.

So I guess I have 3 questions.

Is it possible?

Will it speed up the query as I have imagined?

And here is the big one, can somebody show me how limit the query to a certain number of past days or months?

 

I would rather have the stock also purchased module that is automated and based upon actual customer purchases rather than installing a cross sell mod. However, if I cannot do the above, I will go with a contribution. Currently I am leaving the also purchased set to zero until I implement a solution.

 

Thanks,

 

Tim

[Jan Zonjee]

Forgive me if this is considered a double post. It is kind of a continuation although knid of a spin off as well.

Hmm.

 

Changing the six to a three makes no real difference, but my idea is to limit the query to orders placed within the last 12 months. Less data to search through should equal quicker results.

I have no clue how to accomplish this code.

So I guess I have 3 questions.

Is it possible?

Will it speed up the query as I have imagined?

And here is the big one, can somebody show me how limit the query to a certain number of past days or months?

It is possible.

I don't know if it will speed up the query because I can't test that. On the one hand it first trims down the table to join to orders_products down to the ones with the products_id you are looking for and the date interval you are looking for but if it really makes much of a difference with such a subselect?

 

select p.products_id, p.products_image from (select op.orders_id, op.products_id, o.date_purchased from
orders_products op, orders o where products_id = '31' and op.orders_id = o.orders_id 
and DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= o.date_purchased) as 
opa, orders_products opb, products p where opa.orders_id = opb.orders_id and opb.products_id != '31' 
and opb.products_id = p.products_id and p.products_status = '1' group by p.products_id 
order by opa.date_purchased desc limit 6

 

Alternatively/additionally, you could leave the table products out of the query and select opb.products_id. Then do the query, fetch the results of the query (basically only the products_id) and store the products_id's in an array (list_of_prdct_ids). Then query the table products for products_id and products_image using the products_id's you fetched (where products_id in (" . implode(',', $list_of_prdct_ids) . ") ).

[multimixer]

Alternatively/additionally, you could leave the table products out of the query and select opb.products_id. Then do the query, fetch the results of the query (basically only the products_id) and store the products_id's in an array (list_of_prdct_ids). Then query the table products for products_id and products_image using the products_id's you fetched (where products_id in (" . implode(',', $list_of_prdct_ids) . ") ).

 

You mean in a similar way it is done for product attributes on product_info.php in contribution SPPC for QPBPP?

[Jan Zonjee]

You mean in a similar way it is done for product attributes on product_info.php in contribution SPPC for QPBPP?

That's one way of doing it. A bit more elegant is the one for featured_products.php in that package IMHO:

 

   $featured_products_query = tep_db_query($featured_products_split->sql_query);
   if (($no_of_featured_prdcts = tep_db_num_rows($featured_products_query)) > 0) {
    while ($_featured_products = tep_db_fetch_array($featured_products_query)) {
// BOF Separate Pricing Per Customer
      $featured_products[] = $_featured_products;
      $list_of_prdct_ids[] = $_featured_products['products_id'];
    } 
// get all customers_group_prices for products with the particular customer_group_id
// however not necessary for customer_group_id = 0
 if ($customer_group_id != '0') {
   $pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where products_id in (" . implode(',', $list_of_prdct_ids) . ") and pg.customers_group_id = '" . $customer_group_id . "'");
  while ($pg_array = tep_db_fetch_array($pg_query)) {
  $new_prices[] = array ('products_id' => $pg_array['products_id'], 'products_price' => $pg_array['price'], 'specials_new_products_price' => '');
}

   for ($x = 0; $x < $no_of_featured_prdcts; $x++) {
// replace products prices with those from customers_group table
     if (!empty($new_prices)) {
      for ($i = 0; $i < count($new_prices); $i++) {
	      if ($featured_products[$x]['products_id'] == $new_prices[$i]['products_id'] ) {
		    $featured_products[$x]['products_price'] = $new_prices[$i]['products_price'];
	      }
      }
    } // end if(!empty($new_prices)
   } // end for ($x = 0; $x < $no_of_featured_prdcts; $x++)
 } // end if ($customer_group_id != '0')

[knifeman]

Hmm.

 

 

It is possible.

I don't know if it will speed up the query because I can't test that. On the one hand it first trims down the table to join to orders_products down to the ones with the products_id you are looking for and the date interval you are looking for but if it really makes much of a difference with such a subselect?

 

select p.products_id, p.products_image from (select op.orders_id, op.products_id, o.date_purchased from
orders_products op, orders o where products_id = '31' and op.orders_id = o.orders_id 
and DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= o.date_purchased) as 
opa, orders_products opb, products p where opa.orders_id = opb.orders_id and opb.products_id != '31' 
and opb.products_id = p.products_id and p.products_status = '1' group by p.products_id 
order by opa.date_purchased desc limit 6

 

Alternatively/additionally, you could leave the table products out of the query and select opb.products_id. Then do the query, fetch the results of the query (basically only the products_id) and store the products_id's in an array (list_of_prdct_ids). Then query the table products for products_id and products_image using the products_id's you fetched (where products_id in (" . implode(',', $list_of_prdct_ids) . ") ).

 

Thanks Jan,

 

I messed up my post. I pasted the output from the debug query instead of the actual file contents. I am going to attempt merging your proposal with the actual file contents:

 if (isset($HTTP_GET_VARS['products_id'])) {
   $orders_query = tep_db_query("select p.products_id, p.products_image from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p where opa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and opa.orders_id = opb.orders_id and opb.products_id != '" . (int)$HTTP_GET_VARS['products_id'] . "' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);
   $num_products_ordered = tep_db_num_rows($orders_query);
   if ($num_products_ordered >= MIN_DISPLAY_ALSO_PURCHASED) {
?>

 

Tim

[knifeman]

I replaced this original:

select p.products_id, p.products_image from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p where opa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and opa.orders_id = opb.orders_id and opb.products_id != '" . (int)$HTTP_GET_VARS['products_id'] . "' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);

 

with this modified:

select p.products_id, p.products_image from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p where opa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= o.date_purchased and opa.orders_id = opb.orders_id and opb.products_id != '" . (int)$HTTP_GET_VARS['products_id'] . "' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);

 

Query times

original = 6.958

mod 1 year = 6.8

mod 1 month = 5.8

Not a significant change. I guess I will go with a crosss sell mod. The only other option I see is to delete orders older than say 3 years, but I don't really want to do that either.

 

I do appreciate all help,

 

Tim

[Jan Zonjee]

Not a significant change.

If you do it like that I would have expected that. The query I suggested uses a subquery:

 

select p.products_id, p.products_image from (select op.orders_id, op.products_id, o.date_purchased from 
" . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' 
and op.orders_id = o.orders_id and DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= o.date_purchased) as 
opa, " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_PRODUCTS . " p where opa.orders_id = opb.orders_id and 
opb.products_id != '" . (int)$HTTP_GET_VARS['products_id'] . "' and opb.products_id = p.products_id and 
p.products_status = '1' group by p.products_id order by opa.date_purchased desc limit 6

 

Would be interesting to know what that does.

[Jan Zonjee]

I haven't tested this, but perhaps this would work faster:

<?php
/*
 $Id: also_purchased_products.php 1739 2007-12-20 00:52:16Z hpdl $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

 if (isset($HTTP_GET_VARS['products_id'])) {
   $sub_orders_query = ("select op.orders_id from " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and op.orders_id = o.orders_id and DATE_SUB(CURDATE(), INTERVAL 720 DAY) <= o.date_purchased order by o.date_purchased desc limit 100"); // the last 100 orders with this product should suffice hopefully
   if (($num_sub_products_ordered = tep_db_num_rows($sub_orders_query)) > 0) {
    while ($sub_orders = tep_db_fetch_array($sub_orders_query)) {
      $list_of_order_ids[] = $sub_orders['orders_id'];
    }
 }
// if there are no orders with this product the next query is pointless too
if (isset($list_of_order_ids)) {
   $orders_query = tep_db_query("select p.products_id, p.products_image from " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p where opb.products_id != '" . (int)$HTTP_GET_VARS['products_id'] . "' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and o.orders_id in (" . implode(',', $list_of_order_ids) . ") and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);
   $num_products_ordered = tep_db_num_rows($orders_query);
   if ($num_products_ordered >= MIN_DISPLAY_ALSO_PURCHASED) {
?>
<!-- also_purchased_products //-->
<?php
     $info_box_contents = array();
     $info_box_contents[] = array('text' => TEXT_ALSO_PURCHASED_PRODUCTS);

     new contentBoxHeading($info_box_contents);

     $row = 0;
     $col = 0;
     $info_box_contents = array();
     while ($orders = tep_db_fetch_array($orders_query)) {
       $orders['products_name'] = tep_get_products_name($orders['products_id']);
       $info_box_contents[$row][$col] = array('align' => 'center',
                                              'params' => 'class="smallText" width="33%" valign="top"',
                                              'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $orders['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $orders['products_image'], $orders['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $orders['products_id']) . '">' . $orders['products_name'] . '</a>');

       $col ++;
       if ($col > 2) {
         $col = 0;
         $row ++;
       }
     }

     new contentBox($info_box_contents);
?>
<!-- also_purchased_products_eof //-->
<?php
   } // end if ($num_products_ordered >= MIN_DISPLAY_ALSO_PURCHASED) {
  } // end if (isset($list_of_order_ids)) 
 } // end if (isset($HTTP_GET_VARS['products_id']))
?>

[knifeman]

If you do it like that I would have expected that. The query I suggested uses a subquery:

 

 

Would be interesting to know what that does.

Sorry Jan,

 

I do not know enough to merge the code you gave. It was my fault for posting the wrong code in the first place. I did manage to get the new offering merged. I uploaded this code:

select p.products_id, p.products_image from (select op.orders_id, op.products_id, o.date_purchased from " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and op.orders_id = o.orders_id and DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= o.date_purchased) as opa, " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_PRODUCTS . " p where opa.orders_id = opb.orders_id and opb.products_id != '" . (int)$HTTP_GET_VARS['products_id'] . "' and opb.products_id = p.products_id and p.products_status = '1' group by p.products_id order by opa.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);

 

The specific query ran in 2.75 and a total page load in 3.02

That is significant.

 

I changed the 1 year to 1 month and reduced it further to

.373 query

.643 page Although 1 month is not enough data to please me, I believe we could get by with one year and it cut the page load time in half.

 

Thank you so much.

 

Tim

[multimixer]

I haven't tested this, but perhaps this would work faster:

 

wow, that was fast, the code that came I mean :) great, thank you, I will try and post also results

[knifeman]

I haven't tested this, but perhaps this would work faster:

I had to format the code. When I copy/paste, it puts it all on one line. So here is what I changed my file to:

<?php
/*  $Id: also_purchased_products.php 1739 2007-12-20 00:52:16Z hpdl $

osCommerce, Open Source E-Commerce Solutions
http://www.oscommerce.com

Copyright (c) 2003 osCommerce  
Released under the GNU General Public License
*/
if (isset($HTTP_GET_VARS['products_id'])) {
$sub_orders_query = ("select op.orders_id from " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and op.orders_id = o.orders_id and DATE_SUB(CURDATE(), INTERVAL 720 DAY) <= o.date_purchased order by o.date_purchased desc limit 100"); // the last 100 orders with this product should suffice hopefully
if (($num_sub_products_ordered = tep_db_num_rows($sub_orders_query)) > 0) {
while ($sub_orders = tep_db_fetch_array($sub_orders_query)) {
$list_of_order_ids[] = $sub_orders['orders_id'];
}
}
// if there are no orders with this product the next query is pointless too
if (isset($list_of_order_ids)) {
$orders_query = tep_db_query("select p.products_id, p.products_image from " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p where opb.products_id != '" . (int)$HTTP_GET_VARS['products_id'] . "' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and o.orders_id in (" . implode(',', $list_of_order_ids) . ") and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);
$num_products_ordered = tep_db_num_rows($orders_query);
if ($num_products_ordered >= MIN_DISPLAY_ALSO_PURCHASED) {
?>
<!-- also_purchased_products //-->
<?php
$info_box_contents = array();
$info_box_contents[] = array('text' => TEXT_ALSO_PURCHASED_PRODUCTS);
new contentBoxHeading($info_box_contents);
$row = 0;
$col = 0;
$info_box_contents = array();
while ($orders = tep_db_fetch_array($orders_query)) {
$orders['products_name'] = tep_get_products_name($orders['products_id']);
$info_box_contents[$row][$col] = array('align' => 'center',
'params' => 'class="smallText" width="33%" valign="top"',
'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $orders['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $orders['products_image'], $orders['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $orders['products_id']) . '">' . $orders['products_name'] . '</a>');
$col ++;
if ($col > 2) {
$col = 0;
$row ++;
}
}
new contentBox($info_box_contents);
?>

<!-- also_purchased_products_eof //-->
<?php
}// end if ($num_products_ordered >= MIN_DISPLAY_ALSO_PURCHASED) {
} // end if (isset($list_of_order_ids))
} // end if (isset($HTTP_GET_VARS['products_id']))
?>

 

But it throws up an error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/xxxxx/public_html/includes/functions/database.php on line 129

 

Tim

[Jan Zonjee]

I had to format the code. When I copy/paste, it puts it all on one line. So here is what I changed my file to:

<?php
/*  $Id: also_purchased_products.php 1739 2007-12-20 00:52:16Z hpdl $

osCommerce, Open Source E-Commerce Solutions
http://www.oscommerce.com

Copyright (c) 2003 osCommerce  
Released under the GNU General Public License
*/
if (isset($HTTP_GET_VARS['products_id'])) {
$sub_orders_query = ("select op.orders_id from " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and op.orders_id = o.orders_id and DATE_SUB(CURDATE(), INTERVAL 720 DAY) <= o.date_purchased order by o.date_purchased desc limit 100"); // the last 100 orders with this product should suffice hopefully

Yeah sorry. That's what you get with not testing...

 

The beginning of the line that starts with $sub_orders_query = ("select op.orders_id from should read:

$sub_orders_query = tep_db_query("select op.orders_id from (so insertion of tep_db_query)

 

Of course there might be more errors :)

[knifeman]

Yeah sorry. That's what you get with not testing...

 

Of course there might be more errors :)

No apologies needed when you code for free. :)

I had to go out for a bit. Came back and tried the new code. BAM! It works and the entire page loads in .364 seconds.

 

I am going to play with the number 100 and see what happens to parse times.

 

Thank you so much. I am curious nobody has had this same problem. On my site it is obviously the older most ordered products that started slowing down.

 

Tim

[Jan Zonjee]

I am curious nobody has had this same problem. On my site it is obviously the older most ordered products that started slowing down.

I have seen it mentioned before, but never seen (as far as I can remember) a solution that didn't use caching.

[Juto]

Hello Jan, I do hope you can help me out with this. I have tried to merge the above code into my also_purchased.

In my version I am trying to get the same look as I have managed to get for product listing, see My Site

that is to say to get the also purchased products in columns to. However I get this error from KISS error handler:

 

Date / Time: 06-02-2011 02:36:57

Error Type: [E_WARNING] fopen(/home/juto1/public_html/pub/cache/categories_box-svenska.cache26) [<a href='function.fopen'>function.fopen</a>]: failed to open stream: No such file or directory

On line 58

File includes/functions/cache.php

 

My also_purchased_products look like this:

<?php
/*  $Id: also_purchased_products.php 1739 2007-12-20 00:52:16Z hpdl $
   osCommerce, Open Source E-Commerce Solutions
   http://www.oscommerce.com 
   Copyright (c) 2003 osCommerce  
   Released under the GNU General Public License
   mod 1, REF: http://www.oscommerce.com/forums/topic/353115-limit-also-purchased-query-by-date/
   mod 2, Products in columns
*/
//Begin mod 2, Products in columns config values
   if (!defined('PRODUCT_LIST_COLUMNS_ALSO_PURCHASED')) {
     tep_db_query(
     "insert into configuration
     (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added)
     VALUES ('Number of products per row on also purchased modules', 'PRODUCT_LIST_COLUMNS_ALSO_PURCHASED', '6', 'Set the number of products per row to display?', '8', '22', now());"
     );
     define('PRODUCT_LIST_COLUMNS_ALSO_PURCHASED', '6');
     }
//End mod 2, Products in columns config values
//Mod 2 Begin build column list
   $define_list = array(
     'PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,
     'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,
     'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,
     'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,
     'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,
     'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,
     'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE,
     'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW,
   );
   asort($define_list);
   $column_list = array();
   reset($define_list);
   while (list($key, $value) = each($define_list)) {
     if ($value > 0) $column_list[] = $key;
   }
   $select_column_list = '';
   for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
   switch ($column_list[$i]) {
       case 'PRODUCT_LIST_MODEL':
         $select_column_list .= 'p.products_model, ';
         break;
       case 'PRODUCT_LIST_NAME':
         $select_column_list .= 'pd.products_name, ';
         break;
       case 'PRODUCT_LIST_MANUFACTURER':
         $select_column_list .= 'm.manufacturers_name, ';
         break;
       case 'PRODUCT_LIST_QUANTITY':
         $select_column_list .= 'p.products_quantity, ';
         break;
       case 'PRODUCT_LIST_IMAGE':
         $select_column_list .= 'p.products_image, ';
         break;
       case 'PRODUCT_LIST_WEIGHT':
         $select_column_list .= 'p.products_weight, ';
         break;
     }
   }
//Mod 2 End build column list
   if (isset($_GET['products_id'])) {
     $sub_orders_query = tep_db_query("select op.orders_id from " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where products_id = '" . (int)$_GET['products_id'] . "' and op.orders_id = o.orders_id and DATE_SUB(CURDATE(), INTERVAL 720 DAY) <= o.date_purchased order by o.date_purchased desc limit 100"); // the last 100 orders with this product should suffice hopefully
     if (($num_sub_products_ordered = tep_db_num_rows($sub_orders_query)) > 0) {
     while ($sub_orders = tep_db_fetch_array($sub_orders_query)) {
     $list_of_order_ids[] = $sub_orders['orders_id'];
     }
   }
// if there are no orders with this product the next query is pointless too
     if (isset($list_of_order_ids)) {
//      $orders_query = tep_db_query("select p.products_id, p.products_image from " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p where opb.products_id != '" . (int)$_GET['products_id'] . "' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and o.orders_id in (" . implode(',', $list_of_order_ids) . ") and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);
   $orders_query = tep_db_query(
     "select p.products_id, p.products_image, p.products_model, p.products_quantity, p.products_weight, p.products_price, p.products_tax_class_id, m.manufacturers_name
     from
       " . TABLE_ORDERS_PRODUCTS . " opa,
       " . TABLE_ORDERS_PRODUCTS . " opb,
       " . TABLE_ORDERS . " o,
       " . TABLE_PRODUCTS . " p,
       " . TABLE_MANUFACTURERS ." m
     where opa.products_id = '" . (int)$_GET['products_id'] . "'
     and opa.orders_id = opb.orders_id
     and opb.products_id != '" . (int)$_GET['products_id'] . "'
     and opb.products_id = p.products_id
     and opb.orders_id = o.orders_id
     and p.products_status = '1'
     and p.manufacturers_id = m.manufacturers_id
     group by p.products_id
     order by o.date_purchased desc
     limit " . MAX_DISPLAY_ALSO_PURCHASED
   );
     $num_products_ordered = tep_db_num_rows($orders_query);
     if ($num_products_ordered >= MIN_DISPLAY_ALSO_PURCHASED) {
?>
<!-- also_purchased_products //-->
<?php

     $info_box_contents = array();
     $info_box_contents[] = array('text' => TEXT_ALSO_PURCHASED_PRODUCTS);
//Fix for heading corners the boolean 'USE_MID_CORNERS' is set in application_top
//Replace new contentBoxHeading($info_box_contents);  new infoBoxHeading($info_box_contents, false, false);
// With
     if (USE_MID_CORNERS == TRUE) {
     new contentBoxHeading($info_box_contents);
     } else {
     new infoBoxHeading($info_box_contents, false, false, false);
     }
// End fix
//Mod 2 Begin output in columns
     $row = 0;
     $col = 0;
     $info_box_contents = array();
     while ($orders = tep_db_fetch_array($orders_query)) {
       $orders['products_name'] = tep_get_products_name($orders['products_id']);        
       $lc_align = 'center';
       $lc_text = '<table cellpadding="2" cellspacing="0" border="0">' . "\r\n";
       foreach ($column_list as $column) {
         switch($column) {
         // Row: Products model
           case 'PRODUCT_LIST_MODEL':
             $lc_text .= '  <tr>' . "\r\n"
                       . '    <td class="main" align="center">'. $orders['products_model'] . '</td>' . "\r\n"
                       . '  </tr>' . "\r\n";
             break;
         // Row: Product's image
           case 'PRODUCT_LIST_IMAGE':
             $lc_text .= '  <tr>' . "\r\n"
                       . '    <td class="main" align="center"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, (isset($_GET['manufacturers_id']) ? 'manufacturers_id=' . $_GET['manufacturers_id'] : ($cPath ? 'cPath=' . $cPath : '')) . '&products_id=' . $orders['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $orders['products_image'], $orders['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a></td>' . "\r\n"
                       . '  </tr>' . "\r\n";
             break;
         // Row: Product's name
           case 'PRODUCT_LIST_NAME':
             $lc_text .= '  <tr>' . "\r\n"
                       . '    <td class="main" align="center"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, (isset($_GET['manufacturers_id']) ? 'manufacturers_id=' . $_GET['manufacturers_id'] : ($cPath ? 'cPath=' . $cPath : '')) . '&products_id=' . $orders['products_id']) . '">' . tep_get_products_name($orders['products_id']) . '</a></td>' . "\r\n"
                       . '  </tr>' . "\r\n";
             break;
         // Row: Manufacturer's name
           case 'PRODUCT_LIST_MANUFACTURER':
             $lc_text .= '  <tr>' . "\r\n"
                       . '    <td class="main" align="center"><span style="color: #666666;">'. $orders['manufacturers_name'] . '</span></td>' . "\r\n"
                       . '  </tr>' . "\r\n";
             break;
         // Row: Price
           case 'PRODUCT_LIST_PRICE':
             $orders['specials_new_products_price'] = tep_get_products_special_price($orders['products_id']);
             if (tep_not_null($orders['specials_new_products_price'])) {
               $lc_text .= '  <tr>' . "\r\n"
                         . '    <td class="main" align="center"><s>' . $currencies->display_price($orders['products_price'], tep_get_tax_rate($orders['products_tax_class_id'])) .'</s><br><span class="productSpecialPrice">'. $currencies->display_price($orders['specials_new_products_price'], tep_get_tax_rate($orders['products_tax_class_id'])) . '</span></td>' . "\r\n"
                         . '  </tr>' . "\r\n";
             } else {
               $lc_text .= '  <tr>' . "\r\n"
                         . '    <td class="main" align="center">' . $currencies->display_price($orders['products_price'], tep_get_tax_rate($orders['products_tax_class_id'])) . '</td>' . "\r\n"
                         . '  </tr>' . "\r\n";
             }
             break;
         // Row: Product's quantity
           case 'PRODUCT_LIST_QUANTITY':
             $lc_text .= '  <tr>' . "\r\n"
                       . '    <td class="main" align="center">'. TABLE_HEADING_QUANTITY .': '. $orders['products_quantity'] . '</td>' . "\r\n"
                       . '  </tr>' . "\r\n";
             break;
         // Row: Product's weight
           case 'PRODUCT_LIST_WEIGHT':
             $lc_text .= '  <tr>' . "\r\n"
                       . '    <td class="main" align="center">'. TABLE_HEADING_WEIGHT .': '. $orders['products_weight'] . '</td>' . "\r\n"
                       . '  </tr>' . "\r\n";
             break;
         // Row: Buy now button
           case 'PRODUCT_LIST_BUY_NOW':
             $lc_text .= '  <tr>' . "\r\n"
                       . '    <td class="main" align="center"><a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $orders['products_id']) . '">' . tep_image_button('button_buy_now.gif', IMAGE_BUTTON_BUY_NOW) . '</a></td>' . "\r\n"
                       . '  </tr>' . "\r\n";
             break;
         }
       }
       $lc_text .= '</table>' . "\r\n";
       $info_box_contents[$row][$col] = array('align' => 'center',
                                              'params' => 'class="smallText" width="'. round(100/PRODUCT_LIST_COLUMNS_ALSO_PURCHASED) .'%" valign="top"',
                                              'text' => $lc_text);
       $col ++;
       if ($col > (PRODUCT_LIST_COLUMNS_ALSO_PURCHASED-1)) {
         $col = 0;
         $row ++;
       }
     }
     while ($col != 0 && sizeof($info_box_contents[$row]) < PRODUCT_LIST_COLUMNS_ALSO_PURCHASED) {
       $info_box_contents[$row][$col] = array('align' => 'center',
                                              'params' => 'class="smallText" width="'. round(100/PRODUCT_LIST_COLUMNS_ALSO_PURCHASED) .'%" valign="top"',
                                              'text' => ' ');
       $col ++;
     }
//Mod 2 End output in columns
     new contentBox($info_box_contents);
?>
<!-- also_purchased_products_eof //-->
<?php
     }// end if ($num_products_ordered >= MIN_DISPLAY_ALSO_PURCHASED) {
   } // end if (isset($list_of_order_ids))
 } // end if (isset($_GET['products_id']))
?>

 

Kind regards

Sara

[Jan Zonjee]

However I get this error from KISS error handler:

 

Date / Time: 06-02-2011 02:36:57

Error Type: [E_WARNING] fopen(/home/juto1/public_html/pub/cache/categories_box-svenska.cache26) [<a href='function.fopen'>function.fopen</a>]: failed to open stream: No such file or directory

On line 58

File includes/functions/cache.php

 

My also_purchased_products look like this:

The warning message is about the cache functions that you use for the categories box. It is just a warning (probably that cache file didn't exist yet and now it does). Besides, the also_purchased_products have absolutely nothing to do with the categories box.

 

Isn't there a way to turn off the warning messages in the KISS error handler? It is been a while since I used that contribution. Innocent warning messages like that are interesting for the owner of the site but shouldn't be shown to customers IMHO.

[Juto]

Thank you Jan :)

yes you are correct. The problem should be with my boxes/categories wrt to the cache. I would love to find a fix, since I wouldn't like to complete the update to v2.3 having any errors. I guess thats a logical error, i.e the logic should be:

 

1) Test if the file exist in the cache

2) If not, write to the cache

3) Then It can be opened.

 

I had the same problem with the also_purchased_products, which seems to have disappeared with the above code.

 

Could you please advice me?

 

Sara

[Cheepnis]

I've had this problem happen recently and have had to disable the Also Purchased box. I tried modifying also_purchased_products.php but I get this error:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /users/xxxxx/htdocs/store/catalog/includes/functions/database.php on line 105

 

Here is what I had:

 

  if (isset($HTTP_GET_VARS['products_id'])) {
   $orders_query = tep_db_query("select p.products_id, p.products_image from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, " . TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p where opa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and opa.orders_id = opb.orders_id and opb.products_id != '" . (int)$HTTP_GET_VARS['products_id'] . "' and opb.products_id = p.products_id and opb.orders_id = o.orders_id and p.products_status = '1' group by p.products_id order by o.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);
   $num_products_ordered = tep_db_num_rows($orders_query);
   if ($num_products_ordered >= MIN_DISPLAY_ALSO_PURCHASED) {

 

I replaced it with:

 

if (isset($HTTP_GET_VARS['products_id'])) {
$sub_orders_query = tep_db_query("select op.orders_id from " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and op.orders_id = o.orders_id and DATE_SUB(CURDATE(), INTERVAL 720 DAY) <= o.date_purchased order by o.date_purchased desc limit 100 "); 

   $num_products_ordered = tep_db_num_rows($orders_query);
   if ($num_products_ordered >= MIN_DISPLAY_ALSO_PURCHASED) {

 

I also tried:

 

if (isset($HTTP_GET_VARS['products_id'])) {
$sub_orders_query = tep_db_query("select op.orders_id from " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS . " o where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and op.orders_id = o.orders_id and DATE_SUB(CURDATE(), INTERVAL 720 DAY) <= o.date_purchased order by o.date_purchased desc limit 100 " . MAX_DISPLAY_ALSO_PURCHASED);
   $num_products_ordered = tep_db_num_rows($orders_query);
   if ($num_products_ordered >= MIN_DISPLAY_ALSO_PURCHASED) {

 

which yielded this error:

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 1

select op.orders_id from orders_products op, orders o where products_id = '32' and op.orders_id = o.orders_id and DATE_SUB(CURDATE(), INTERVAL 720 DAY) <= o.date_purchased order by o.date_purchased desc limit 100 0

 

Any ideas?

Thanks!

[Cheepnis]

I've had this problem happen recently and have had to disable the Also Purchased box. I tried modifying also_purchased_products.php but I get this error:

 

*SNIP*

 

 

 

Opps, I see now that I needed to add the sub-query, not replace the existing one. I'm still learning php and without simple instructions for my simple mind, I do a lot of trial and error testing. I have now basically replaced my file with the one in post 11 (with typo fixed) and everything is GREAT!

 

Jan, thank you SO much for your work and assistance! I can now sleep at night with this excellent fix!

[Cheepnis]

Well, at least mostly great. The speed issue has gone away (THANKS!), but now I get the same products displayed every time - it never changes at all, even with clean cache and even on a different computer. Furthermore, on one product (where I had the original speed issue, no less) only 5 products display in the also-purchased box instead of the normal 6. All other products tested show 6 prods - but they never change either!

 

Any thoughts?

Thanks!

[knifeman]

Well, at least mostly great. The speed issue has gone away (THANKS!), but now I get the same products displayed every time - it never changes at all, even with clean cache and even on a different computer. Furthermore, on one product (where I had the original speed issue, no less) only 5 products display in the also-purchased box instead of the normal 6. All other products tested show 6 prods - but they never change either!

 

Any thoughts?

Thanks!

My site is the same way, same products every time. Although I do not have the php skill to fix it. I took the work supplied by Jan and uploaded as an add on. Maybe some one will be just as genorous with their time and offer a solution. Does it need a RANDOM command somewhere?

 

Tim

[mr_absinthe]

Hi,

not sure if this thread is still being followed, I'm trying to achieve the same for best_sellers while avoiding TABLE_ORDERS - I was reaching the memory limit while fetching orders. So far, the code is as follows, but I'm struggling with the part (o.date_purchased). Could you please help? The major problem is that there is no date in TABLE_PRODUCTS, therefore I don't know if that is something that could be done without using TABLE_ORDERS

 

$best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " .
	TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
	TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES .
	" c where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" .
	(int)$languages_id .
	"' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '" .
	(int)$current_category_id .
	"' in (c.categories_id, c.parent_id) and TO_DAYS(NOW()) - TO_DAYS(o.date_purchased) < '" .
	BESTSELLER_DAYS . "'
 order by p.products_ordered desc, pd.products_name limit " .
	MAX_DISPLAY_BESTSELLERS);
} else
{
$best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " .
	TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION .
	" pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" .
	(int)$languages_id .
	"' and TO_DAYS(NOW()) - TO_DAYS(o.date_purchased) < '" .
	BESTSELLER_DAYS . "' order by p.products_ordered desc, pd.products_name limit " .
	MAX_DISPLAY_BESTSELLERS);

 

The original code (that is giving me error Out of memory (Needed 1537888 bytes)) is this:

$best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " .
    TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
    TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c, " .
    TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS .
    " op where p.products_status = '1' and p.products_ordered > 0 and op.products_quantity < 4 and p.products_id = pd.products_id and pd.language_id = '" .
    (int)$languages_id .
    "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and '" .
    (int)$current_category_id .
    "' in (c.categories_id, c.parent_id) and op.orders_id = o.orders_id and op.products_id = p.products_id and TO_DAYS(NOW()) - TO_DAYS(o.date_purchased) < '" .
    BESTSELLER_DAYS . "'
 group by p.products_id order by sum(op.products_quantity) desc, pd.products_name limit " .
    MAX_DISPLAY_BESTSELLERS);
} else
{
   $best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name from " .
    TABLE_PRODUCTS . " p, " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS .
    " op, " . TABLE_PRODUCTS_DESCRIPTION .
    " pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" .
    (int)$languages_id .
    "' and op.orders_id = o.orders_id and op.products_quantity < 4 and op.products_id = p.products_id and TO_DAYS(NOW()) - TO_DAYS(o.date_purchased) < '" .
    BESTSELLER_DAYS . "' group by p.products_id order by sum(op.products_quantity) desc, pd.products_name limit " .
    MAX_DISPLAY_BESTSELLERS);