how about fixing some code like this?

[cstsai]

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 ,

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

to

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

 

that is,you can modify SQL statement from this

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

to

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

 

Regards,

Karl