Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

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


virtue

Recommended Posts

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

Link to comment
Share on other sites

  • 3 weeks later...

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.

Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Link to comment
Share on other sites

  • 1 month later...

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?

Link to comment
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:

Link to comment
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.

Link to comment
Share on other sites

  • 4 weeks later...

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!

Link to comment
Share on other sites

  • 2 months later...

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.

Link to comment
Share on other sites

  • 3 months later...

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!

Link to comment
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).

Link to comment
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!

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...