Four last purchased items instead of the four bestselling

[Fredrik.r]

Hi,

 

I am at this moment using three rows at the store frontpage; 4 featured products, 4 bestselling items and the 4 newest items.

 

I would want to change the 4 bestsellers to the "4 just bought" but I have no clue how to change the code. Of course the just bought items can be from four separate orders if the last orders just contains one item, would that make it even more complicated?

 

I have had a look at the infobox module named "justbought" but it's an infobox and not a module for the frontpage. I have changed other infobox features to modules before but in this case I have no idea how to do it.

 

Any help is very appreciated!

 

The code for bestselling items I am using right now is;

 

<?php
/*
 Id: best_sellers.php,v 1.0 2002/11/22

 Written by Tony Alanis [email protected]

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

 Copyright (c) 2002 osCommerce

 Released under the GNU General Public License
*/
?>
<!-- best_sellers //-->
<?php
 $info_box_contents = array();
 $info_box_contents[] = array('align' => 'left', 'text' => sprintf(TABLE_HEADING_BEST_SELLERS, strftime('%B')));
 new contentBoxHeading($info_box_contents);

 if ($cPath) {
$best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name, p.products_image, pd.products_description, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . 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) order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_FEATURED_PRODUCTS);
 } else {
$best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name, p.products_image, pd.products_description, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . 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 . "' order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_FEATURED_PRODUCTS);
 }

 $info_box_contents = array();
 $row = 0;
 $nextrow = 1;
 $col = 0;
 while ($best_sellers = tep_db_fetch_array($best_sellers_query)) {
$best_sellers['products_name'] = tep_get_products_name($best_sellers['products_id']);
$info_box_contents[$row][$col] = array('align' => 'center',
									   'params' => 'class="smallText" width="25%"',
									   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $best_sellers['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $best_sellers['products_image'], $best_sellers['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>');
$info_box_contents[$nextrow][$col] = array('align' => 'center',
									   'params' => 'class="smallText" width="25%" valign="top"',
									   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $best_sellers['products_id']) . '">' . $best_sellers['products_name'] . '</a><br>' . $currencies->display_price($best_sellers['products_price'], tep_get_tax_rate($best_sellers['products_tax_class_id'])));

$col ++;
if ($col > 3) {
  $col = 0;
  $row = ++ $nextrow;
  $nextrow ++;
}
 }
 new contentBox($info_box_contents);
?>
<!-- best_sellers_eof //-->

[Fredrik.r]

Hi! Anyone that can point me in the right direction?

[boxtel]

Hi! Anyone that can point me in the right direction?

 

You could use a query like :

 

select distinct op.products_id

from orders o,

orders_products op

where o.orders_status = 3

and o.orders_id = op.orders_id

order by op.orders_products_id desc

limit 4;

[Fredrik.r]

Thanks Amanda!

 

Is this correct?

 

$just_bought_query = tep_db_query("select p.products_id, pd.products_name, p.products_image, p.products_tax_class_id, p.products_price from " . TABLE_PRODUCTS . " op.products_id from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op where o.orders_status = 3 and o.orders_id = op.orders_id, order by op.orders_products_id desc, limit 4;

[boxtel]

Thanks Amanda!

 

Is this correct?

 

$just_bought_query = tep_db_query("select p.products_id, pd.products_name, p.products_image, p.products_tax_class_id, p.products_price from " . TABLE_PRODUCTS . " op.products_id from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op where o.orders_status = 3 and o.orders_id = op.orders_id, order by op.orders_products_id desc, limit 4;

 

 

no, it is a little more complicated than that, thy this:

 

$query_string = "

select distinct

p.products_id,

pd.products_name,

p.products_image,

p.products_tax_class_id,

p.products_price

from " . TABLE_PRODUCTS . " p,

" . TABLE_PRODUCTS_DESCRIPTION . " pd,

" . TABLE_ORDERS . " o,

" . TABLE_ORDERS_PRODUCTS . " op

where o.orders_status = 3

and o.orders_id = op.orders_id

and p.products_id = op.products_id

and p.products_id = pd.products_id

and pd.language_id = ".$languages_id."

order by op.orders_products_id desc

limit 4

";

 

$just_bought_query = tep_db_query ($query_string);

[Fredrik.r]

Thank you. I tried the code above and removed "where o.orders_status = 3" since I want to show the four last purchased products regardless of order status. It still doesnt show the four last bought products though, it shows some other products. I am not sure why. What can be wrong?

[Fredrik.r]

Just found out I cannot just remove "where o.orders_status = 3". How can I modify that code to show last purchased regardless of order status?