12 replies to this topic

[virtue]

Finally!
It has been solved - a very big thanks to my super patient husband for checking this out for me.

Do you use a price break contribution and want the cheapest price break to appear in your results page?
(ie Search Pages, Product Listings, etc.)

View an example on the site I am working on right here

Heres How! I use the Simple Price Break contribution,
so thats all we have figured out how to change, anyway here 'tis...


In order to display the cheapest bulk price, rather than the full price when using simple price break,
you need to replace the SQL to fetch the cheapest price rather than the full price.
Because the price breakdown in stored in a single field with the format of [min qty]:[price]
we need to fetch the last price in the list (this assumes the price is always from highest to lowest).
This can be done with the following code:

REVERSE(LEFT(REVERSE(p.products_discount), INSTR(REVERSE(p.products_discount), ':') - 1))

So, in the SQL you simply need to replace p.products_price with REVERSE(LEFT(REVERSE(p.products_discount), INSTR(REVERSE(p.products_discount), ':') - 1))  as products_price

I have included the source code that I needed to modify for my site to work.
Depending on your setup you may need to make other modifications,
however they should all be similar.

         i.e. replace p.products_price with REVERSE(LEFT(REVERSE(p.products_discount), INSTR(REVERSE(p.products_discount), ':') - 1))  as products_price

NOTE: This code REQUIRES you to enter the products_discount field for all products. Even if you don't have multiple price breaks just enter 1:[price]

THE CODE ADJUSTMENTS:



FOR catalog/index.php

REPLACE THIS

// We are asked to show only a specific category
		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
	  } else {
// We show them all
		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
	  }
	} else {
// show the products in a given categorie
	  if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
	  } else {
// We show them all
		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

WITH THIS

// We are asked to show only a specific category
		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, REVERSE(LEFT(REVERSE(p.products_discount), INSTR(REVERSE(p.products_discount), ':') - 1)) as products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
	  } else {
// We show them all
		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, REVERSE(LEFT(REVERSE(p.products_discount), INSTR(REVERSE(p.products_discount), ':') - 1)) as products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
	  }
	} else {
// show the products in a given categorie
	  if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, REVERSE(LEFT(REVERSE(p.products_discount), INSTR(REVERSE(p.products_discount), ':') - 1)) as products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
	  } else {
// We show them all
		$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, REVERSE(LEFT(REVERSE(p.products_discount), INSTR(REVERSE(p.products_discount), ':') - 1)) as products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

FOR catalog/product_info.php

REPLACE THIS

	$product_info_query = tep_db_query("select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, REVERSE(LEFT(REVERSE(p.products_discount), INSTR(REVERSE(p.products_discount), ':') - 1)) as products_price, p.products_discount, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");
	$product_info = tep_db_fetch_array($product_info_query);

	tep_db_query("update " . TABLE_PRODUCTS_DESCRIPTION . " set products_viewed = products_viewed+1 where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and language_id = '" . (int)$languages_id . "'");

	if ($new_price = tep_get_products_special_price($product_info['products_id'])) {
	  $products_price = '<s>' . $currencies->display_price($product_info['products_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) . '</s> <span class="productSpecialPrice">' . $currencies->display_price($new_price, tep_get_tax_rate($product_info['products_tax_class_id'])) . '</span>';
	} else {
	  $products_price = $currencies->display_price($product_info['products_price'], tep_get_tax_rate($product_info['products_tax_class_id']));
	}

WITH THIS

$product_info_query = tep_db_query("select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, REVERSE(LEFT(REVERSE(p.products_discount), INSTR(REVERSE(p.products_discount), ':') - 1)) as cheapest_price, p.products_discount, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");
	$product_info = tep_db_fetch_array($product_info_query);

	tep_db_query("update " . TABLE_PRODUCTS_DESCRIPTION . " set products_viewed = products_viewed+1 where products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and language_id = '" . (int)$languages_id . "'");

	if ($new_price = tep_get_products_special_price($product_info['products_id'])) {
	  $products_price = '<s>' . $currencies->display_price($product_info['product_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) . '</s> <span class="productSpecialPrice">' . $currencies->display_price($new_price, tep_get_tax_rate($product_info['products_tax_class_id'])) . '</span>';
	} else {
	  $products_price = $currencies->display_price($product_info['cheapest_price'], tep_get_tax_rate($product_info['products_tax_class_id']));
	}

FOR catalog/product_info.php

REPLACE THIS

		   f( $i==0) {

WITH THIS

			if( $i==0 && $index > 1 ) {

FOR catalog//includes/modules/product_listing.php

REPLACE THIS

		$show_price = '<br><font size="'.PRODUCT_PRICE_SIZE.'">' . ($products['products_price'] > 0 ? $price : 'P.O.A') . '<br></font>';

WITH THIS

		$show_price = '<br><font size="'.PRODUCT_PRICE_SIZE.'">' . ($products['products_price'] > 0 ? 'from ' . $price : 'P.O.A') . '<br></font>';

FOR catalog/advanced_search_results.php

REPLACE THIS

  $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";

WITH THIS

$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, REVERSE(LEFT(REVERSE(p.products_discount), INSTR(REVERSE(p.products_discount), ':') - 1)) as products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";

FOR catalog/inclues/boxes/whats_new.php

REPLACE THIS

  if ($random_product = tep_random_select("select products_id, products_image, products_tax_class_id, products_price from " . TABLE_PRODUCTS . " where products_status = '1' order by products_date_added desc limit " . MAX_RANDOM_SELECT_NEW)) {

WITH THIS

  if ($random_product = tep_random_select("select products_id, products_image, products_tax_class_id, REVERSE(LEFT(REVERSE(products_discount), INSTR(REVERSE(products_discount), ':') - 1)) as products_price from " . TABLE_PRODUCTS . " where products_status = '1' order by products_date_added desc limit " . MAX_RANDOM_SELECT_NEW)) {

etc, etc, etc  - Good Luck!

Edited by virtue, 14 August 2008 - 03:11 PM.

[spooks]

To avoid any confusion, the product_listing.php code referanced here comes from Product Listing Enhancements, Thumbnails & Manufacturer Headings   http://addons.oscommerce.com/info/6051 and not the original file.

[charinlasvegas]

Is there a support thread for the Simple Price Break contribution, or is this it???  (I searched, couldn't find any other threads but this one). Anyway, I'd like to install but in the instructions Step 2:

SQL code :
ALTER TABLE `products` ADD `products_discount` VARCHAR( 128 ) ;

I don't understand     I don't know what this means or how to do it.  Can someone please help me?

[charinlasvegas]

charinlasvegas, on Oct 16 2008, 09:22 AM, said:

Is there a support thread for the Simple Price Break contribution, or is this it???  (I searched, couldn't find any other threads but this one). Anyway, I'd like to install but in the instructions Step 2:

SQL code :
ALTER TABLE `products` ADD `products_discount` VARCHAR( 128 ) ;

I don't understand     I don't know what this means or how to do it.  Can someone please help me?

Ok, can someone just tell me if I did this right?  This is what I have now in my 'products' query:

ALTER TABLE `products` ADD `products_discount` VARCHAR( 128 ) NOT NULL AFTER `products_quantity` ;

[Coopco]

charinlasvegas, on Oct 17 2008, 02:35 AM, said:

Ok, can someone just tell me if I did this right?  This is what I have now in my 'products' query:

ALTER TABLE `products` ADD `products_discount` VARCHAR( 128 ) NOT NULL AFTER `products_quantity` ;

Most webhosts have a control panel that you log into.

When you have logged into the control panel , you will have to find the databases section.

Click on the phpMyAdmin icon.

Select the user to configure database via phpMyAdmin and click on the Login icon.

A new window should open for phpMyAdmin.

In some cases, you may have to choose which database your OSC tables are located on. If this is the case, please choose the database from the list or the drop down box at the left.

To run a SQL query, click on "SQL" on the navigation bar (on the right side, click on the SQL tab at the top).

Then copy the code from the sql file (ie the code you pasted in your post) and paste the code into the textarea on that page.

Click on the GO button and it is done.

If all goes well, your SQL query will now be succesfully entered.

[charinlasvegas]

Thanks Leslie. I did all of that, but I wasn't sure if the column should be NULL or NOT NULL?  I think NULL is correct???

[merchantweb]

This question is related to simple price break in general and I need to answer it before I can move on to apply this revision. The recalculated totals do not appear discounted in the cart. I have the field entries correct and it displays the price breakdown in my product's info page but the cart reflects the greatest price no matter the quantity. Additionally, I cannot update the cart with a quantity greater than 99. It will default to 99 if I enter for ex. 200. I am looking for reasons and solutions. Thank you ahead of time!

Edited by merchantweb, 11 November 2008 - 06:56 PM.

[Joorka]

[quote name='virtue' date='Aug 14 2008, 04:09 PM' post='1292371']
[size=3]FOR catalog//includes/modules/product_listing.php[/size]

REPLACE THIS



WITH THIS






I can not find this code in my product_listing.php file
All I can find is something like this.

That is everything with the word pric in it.

[pete2007]

Hi,

Would I be able to use this module for a whole catagory, example:

I will give 10% discount for any 4 T-Shirts purchased across the catagory?

Any help would be most appreciated!

[Jan Zonjee]

pete2007, on May 2 2009, 11:03 AM, said:

I will give 10% discount for any 4 T-Shirts purchased across the catagory?

Quantity Price Breaks Per Product can do that.

[pete2007]

Jan Zonjee, on May 2 2009, 10:19 AM, said:

Quantity Price Breaks Per Product can do that.

Hi,

Thanks for the reply.

Do you know if I can do the following with the "Quantity Price Breaks Per Product" module:

1 Product Purchased: No discount
2 or more products purchased: 5% discount

Many thanks!

Edited by pete2007, 02 May 2009 - 09:22 AM.

[Jan Zonjee]

pete2007, on May 2 2009, 11:22 AM, said:

Do you know if I can do the following with the "Quantity Price Breaks Per Product" module:

1 Product Purchased: No discount
2 or more products purchased: 5% discount

Yes, but then you have to add all products to the same "discount category". That is not what you said you wanted to do by the way. For this scenario there are more addons IMO (working with order total module).

[pete2007]

Jan Zonjee, on May 2 2009, 11:51 AM, said:

Yes, but then you have to add all products to the same "discount category". That is not what you said you wanted to do by the way. For this scenario there are more addons IMO (working with order total module).

So what addon would you recommend I install to get this module working the way I want?

Thanks again for your advice!