Hi, on the sale_followup module i've changed the xsell query to this:
$xsell_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name, p.products_tax_class_id, products_price, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, specials_new_products_price
from " . TABLE_PRODUCTS_XSELL . " xp left join " . TABLE_PRODUCTS . " p on xp.xsell_id = p.products_id
left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "'
left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id
where xp.products_id in (select p.products_id
from " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_PRODUCTS . " p
where op.products_id = p.products_id
and p.products_status = '1'
and op.orders_id = '" . $mail['orders_id'] . "')
and p.products_status = '1'
and p.products_id not in (select p.products_id
from " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_PRODUCTS . " p
where op.products_id = p.products_id
and p.products_status = '1'
and op.orders_id in (
select orders_id from " . TABLE_ORDERS . "
where customers_id = '" . $mail['customers_id'] . "'))
order by sort_order asc limit " . $limit_xsell_products."");
The reason for this change is that i've put the while loop of the xsell out of the while loop of the ordered products so first i have to look for the xsell products of all the products purchased.
Then, while i was checking it, i thought that i should recommend products that the customer have not purchased yet and it's ridiculous to recommend products that he/she already bought so that is the final code.
the first susbelect shows the products_id of the products purchased on the order so that we can get the xsell products of that items.
the third subselect show the orders_id from the orders made by a customer and is passed to the second subselect which looks for products_id that where bought in that orders. So in that way we exclude the items bought in each order from that customer and show items that have not bought yet.
I hope you understand me, and sorry for my English.