Latest News: (loading..)

Archived

This topic is now archived and is closed to further replies.

virtue

HOW TO: Display the Cheapest Price when using the Price Break Contribution!

13 posts in this topic

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! :)

Share this post


Link to post
Share on other sites

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 :blush: I don't know what this means or how to do it. Can someone please help me?

Share this post


Link to post
Share on other sites
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 :blush: 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` ;

 

:blink:

Share this post


Link to post
Share on other sites
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` ;

 

:blink:

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.

Share this post


Link to post
Share on other sites

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???

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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>';

 

 

 

 

I can not find this code in my product_listing.php file

All I can find is something like this.

case 'PRODUCT_LIST_PRICE':
           $lc_align = 'right';
           if (tep_not_null($listing['specials_new_products_price'])) {
             $lc_text = ' <s>' .  $currencies->display_price($listing['products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '</s>  <span class="productSpecialPrice">' . $currencies->display_price($listing['specials_new_products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '</span> ';
           } else {
             $lc_text = ' ' . $currencies->display_price($listing['products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . ' ';
           }

That is everything with the word pric in it.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

 

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!

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites