Remove single product from customers also purchased

[sublok]

I need to remove just one product " product_id ='43' " from the customers also bought.

 

ive tried

     $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'] . "' or opb.products_id != '43' 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);

 

but that doesnt seem to work anyone have any idea?

 

cheers and thanks

[Ben Nevis]

I suspect your 'or' should be an 'and'

[sublok]

I've tried that and Ive also tried to remove the entire category with

 

$orders_query = tep_db_query("select p.products_id, p.products_model, 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' and p.products_id NOT IN (SELECT products_id FROM products_to_categories WHERE categories_id = '24') group by p.products_id order by o.date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);

 

I suspect your 'or' should be an 'and'

[♥joli1811]

I need to remove just one product " product_id ='43' " from the customers also bought.

 

ive tried

     $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'] . "' or opb.products_id != '43' 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);

 

but that doesnt seem to work anyone have any idea?

 

cheers and thanks

 

Hi,

 

$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);

 

John

[sublok]

John thank you - but the product is still showing up in this query,

 

Hi,

 

$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);

 

John

[Ben Nevis]

I've tried that and Ive also tried to remove the entire category

Well, I'm sorry but I don't see how it could not work with 'and'. I just tried it myself. The following sql query in /catalog/includes/modules/also_purchased_products.php removed the product with id 53 from my also purchased list :

 

$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 != '53' 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);

 

Maybe your browser or osc has cached the page and is serving the old results back to you?

 

Edit:

Ok, in product_info.php you'll find the lines:

   if ((USE_CACHE == 'true') && empty($SID)) {
   echo tep_cache_also_purchased(3600);  
   } else {
   include(DIR_WS_MODULES . FILENAME_ALSO_PURCHASED_PRODUCTS);

So most probably it is just echoing the cache back to you. Just try commenting out the

echo tep_cache_also_purchased(3600); 

line and then try it with the revised sql.

[sublok]

Ben, thank you for helping, but its not working. I've turned off the cache and commented out, deleted all cache files in the /cache dir. Double check the products_id, tried with other products_id numbers, check it on safari, chrome, ie, firefox and opera. So its not browser issues. At this point im really stumped. and half tempted to just install the x_sell contrib and be done with the entire business.

 

Well, I'm sorry but I don't see how it could not work with 'and'. I just tried it myself. The following sql query in /catalog/includes/modules/also_purchased_products.php removed the product with id 53 from my also purchased list :

 

$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 != '53' 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);

 

Maybe your browser or osc has cached the page and is serving the old results back to you?

 

Edit:

Ok, in product_info.php you'll find the lines:

   if ((USE_CACHE == 'true') && empty($SID)) {
   echo tep_cache_also_purchased(3600);  
   } else {
   include(DIR_WS_MODULES . FILENAME_ALSO_PURCHASED_PRODUCTS);

So most probably it is just echoing the cache back to you. Just try commenting out the

echo tep_cache_also_purchased(3600); 

line and then try it with the revised sql.

[Ben Nevis]

Ben, thank you for helping, but its not working. I've turned off the cache and commented out, deleted all cache files in the /cache dir. Double check the products_id, tried with other products_id numbers, check it on safari, chrome, ie, firefox and opera. So its not browser issues. At this point im really stumped. and half tempted to just install the x_sell contrib and be done with the entire business.

Hmm, well at this point I'd be stumped too. I can't see how a product that is excluded from a syntactically correct sql query can nevertheless be retrieved by it. I'd be tempted to make more radical changes to the query, for example to make it list only one specific product (regardless of whether it was 'also purchased') to see if that does what I expect. If it doesn't then the also purchased product list must be coming from somewhere else!

[♥FWR Media]

Richards query is correct, no question.

 

to test it run it in phpMyAdmin (or your other chosen method) like ..

 

First without the exclusion: (replace xxx with a products id you know has been sold a bit) -

SELECT p.products_id, p.products_image
FROM `orders_products` opa, `orders_products` opb, `orders` o, `products` p
WHERE opa.products_id = xxx
AND opa.orders_id = opb.orders_id
AND opb.products_id != xxx
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 10

 

When you get the result .. choose a products id to remove ( we'll call it yyy ) then run the following: -

SELECT p.products_id, p.products_image
FROM `orders_products` opa, `orders_products` opb, `orders` o, `products` p
WHERE opa.products_id = xxx
AND opa.orders_id = opb.orders_id
AND opb.products_id != xxx
AND opb.products_id != yyy
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 10

 

The yyy product will disappear.

[satish]

or opb.products_id != '43' should be and opb.products_id != '43'

[[email protected]]

Did you ever resolve this issue? I am having exaclty the same problem. Still showing up. Also, is this line to be added in addition to the existing one or the existing one should be changed? I added three lines like this....each with a diff prod id, since I need to hide 3. I am still in the testing phase so right now this issue is less important but if it is cache I hope it works itself out.

[[email protected]]

I just fiddled around with this. Works great. Must replace the other line. For addition products.........where it specifies the prod id, i just added others.:

 

and opb.products_id != '36' and '32' and '37' and '40'