Working with tier prices programmatically in Magento is somewhat tricky. I found a couple of annoying bugs or behaviors that really got me stuck. I’ll speak about that at the end of this post.

Update: Our colleague Josh found how to solve this issue by rewriting a core module. Take a look at that solution in StackOverflow. The solution below is still valid and you can use it anyway!

Setting tier prices programmatically

In order to set product tier prices in Magento we need to create an array of arrays that contain each new tier price rule. The process consists in: load the product , define the tier prices array (… matrix), set it and finally save the product:

/* First we tell magento to use the admin store in order to let
 * us to save products. This is needed for the Enterprise version,
 * but not for the community one
*/
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
$id_product = 80880; //Select your product id
$product = Mage::getModel('catalog/product')->load($id_product);

//First defined tier price...
$tierPrices[] = array(
                  'website_id'  => 0,
                  'cust_group'  => 1,
                  'price_qty'   => 2,
                  'price'       => 5
                 );
//Second defined tier price
$tierPrices[] = array(
                  'website_id'  => 0,
                  'cust_group'  => 2,
                  'price_qty'   => 3,
                  'price'       => 10
                 );

//Now we set the tier price and save the product
$product->setTierPrice($tierPrices);

$product->save();

It seems to be easy, but it’s important to note that:

  • There is an annoying bug that deletes all tier prices when saving a product programatically, if you don’t set a tierprice, then it deletes all tier prices.

This bug can be solved adding a simple tier price array. How is it? Something like this:

$tierPrices = array(
                  'website_id'  => 0,
                  'cust_group'  => 2,
                  'price_qty'   => 3,
                  'price'       => 10
                 );
//Now we set the tier price and save the product
//No tier price will be added or deleted in this case
$product->setTierPrice($tierPrices);

Exactly, a variable without “[ ]”. Magento won’t add this tier price, but at least it will keep the old ones that have been already defined.

  • There is another bug, or not, that: if you try to save a tierprice that already exists, then Magento returns a constraint violation (I love those errors).

I have no a clear idea on how to solve this. In my case, I always receive all tier prices every time I update the product, so what I do first is to delete all product tier prices directly from the database and then add the tier prices again (because in my script I always receive from the xml file all the product tier prices). Then, in my case:

//Workaround to restart tier prices for the product.
//First delete tier prices related
$dbc = Mage::getSingleton('core/resource')->getConnection('core_write');
$resource = Mage::getSingleton('core/resource');
$table = $resource->getTableName('catalog/product').'_tier_price';
$dbc->query("DELETE FROM $table WHERE entity_id = $id_product");

//Now we can set the product tier prices
$tierPrices[] = array(
                  'website_id'  => 0,
                  'cust_group'  => 1,
                  'price_qty'   => 2,
                  'price'       => 5
                 );
//Second defined tier price
$tierPrices[] = array(
                  'website_id'  => 0,
                  'cust_group'  => 2,
                  'price_qty'   => 3,
                  'price'       => 10
                 );

//Now we set the tier price and save the product
$product->setTierPrice($tierPrices);

$product->save();
Categories: Development

7 Comments

COBAY · March 7, 2012 at 3:16 am

AWESOME !
Thanks.

Josh Pennington · April 16, 2012 at 5:51 pm

I was having this same problem all day. I found the problem and created a fix of sorts so that you can save products from a script without getting the error. I detailed it all in a post at StackOverlfow.

http://stackoverflow.com/questions/10176310/odd-behavior-when-saving-a-product-model-that-has-tier-pricing-from-a-script-in/10178922

Hopefully this will help you not have to manually delete from the table and then just reinsert.

    Pau · April 17, 2012 at 6:10 am

    Thanks Josh!! I will change this guide then 🙂

      sunel · August 2, 2012 at 12:37 pm

      hey there is error can u help me

Nico · May 6, 2012 at 7:23 pm

Really helpfull solution. Thanks!

sunel · August 2, 2012 at 12:33 pm

hey i have problem please help me…………………..

a:5:{i:0;s:313:”SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`sve129`.`catalog_product_entity_tier_price`, CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_TIER_PRICE_GROUP` FOREIGN KEY (`customer_group_id`) REFERENCES `customer_group` (`customer_group_id`) ON DEL)”;i:1;s:1772:”#0 D:xampphtdocsSVE129appMage.php(462): Mage_Core_Model_Config->getModelInstance(‘eav/entity_attr…’, ‘SQLSTATE[23000]…’)
#1 D:xampphtdocsSVE129appcodecoreMageEavModelEntityAbstract.php(661): Mage::getModel(‘eav/entity_attr…’, ‘SQLSTATE[23000]…’)
#2 D:xampphtdocsSVE129appcodecoreMageEavModelEntityAbstract.php(1648): Mage_Eav_Model_Entity_Abstract->walkAttributes(‘backend/afterSa…’, Array)
#3 D:xampphtdocsSVE129appcodecoreMageCatalogModelResourceProduct.php(195): Mage_Eav_Model_Entity_Abstract->_afterSave(Object(Mage_Catalog_Model_Product))
#4 D:xampphtdocsSVE129appcodecoreMageEavModelEntityAbstract.php(1124): Mage_Catalog_Model_Resource_Product->_afterSave(Object(Mage_Catalog_Model_Product))
#5 D:xampphtdocsSVE129appcodecoreMageCoreModelAbstract.php(318): Mage_Eav_Model_Entity_Abstract->save(Object(Mage_Catalog_Model_Product))
#6 D:xampphtdocsSVE129appcodelocalAppthaAirbnbclonecontrollersPropertyController.php(86): Mage_Core_Model_Abstract->save()
#7 D:xampphtdocsSVE129appcodecoreMageCoreControllerVarienAction.php(419): Apptha_Airbnbclone_PropertyController->postAction()
#8 D:xampphtdocsSVE129appcodecoreMageCoreControllerVarienRouterStandard.php(250): Mage_Core_Controller_Varien_Action->dispatch(‘post’)
#9 D:xampphtdocsSVE129appcodecoreMageCoreControllerVarienFront.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#10 D:xampphtdocsSVE129appcodecoreMageCoreModelApp.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#11 D:xampphtdocsSVE129appMage.php(683): Mage_Core_Model_App->run(Array)
#12 D:xampphtdocsSVE129index.php(87): Mage::run(”, ‘store’)
#13 {main}”;s:3:”url”;s:34:”/SVE129/airbnbclone/property/post/”;s:11:”script_name”;s:17:”/SVE129/index.php”;s:4:”skin”;s:7:”default”;}

Fabian Schwark · May 11, 2014 at 4:52 pm

Yo Mate, thx for the script it saved a lot of time on the errors i had. Anyone who gets the typical update error like the constraint violation guy above me : just load the product beforehand. the error occures if magento trys to insert while you should be updating. by just doing a simple:

$product->load();

its solved 😉 have a nice day. c0da

Leave a Reply to sunel Cancel reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.