how about fixing some code like this?

This message was sent from: Suggestions and Proposals
http://forums.oscommerce.com/viewtopic.php?p=199603#199603
----------------------------------------------------------------

Hi all:

How about modifying the SQL in "also_purchased_products.php" line 14?
(Add a "max_date_purchased" for sorting and it can always show the newest "also purchased list".)

original code ,
[code]
    $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);
[/code]
to 
[code]
    $orders_query = tep_db_query("select p.products_id, p.products_image, max(o.date_purchased) max_date_purchased 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 max_date_purchased desc limit " . MAX_DISPLAY_ALSO_PURCHASED);
[/code]

that is,you can modify SQL statement from this
[code]
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
[/code]
to 
[code]
select
	p.products_id,
	p.products_image,
	max(o.date_purchased) max_date_purchased
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
	max_date_purchased desc LIMIT 6
[/code]

Regards,
Karl