How to set tier prices programmatically in Magento

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();
    • COBAY on March 7, 2012 at 3:16 am

    Reply

    AWESOME !
    Thanks.

  1. Reply

    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 on April 17, 2012 at 6:10 am
      • Author

      Reply

      Thanks Josh!! I will change this guide then 🙂

        • sunel on August 2, 2012 at 12:37 pm

        Reply

        hey there is error can u help me

    • Nico on May 6, 2012 at 7:23 pm

    Reply

    Really helpfull solution. Thanks!

    • sunel on August 2, 2012 at 12:33 pm

    Reply

    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 on May 11, 2014 at 4:52 pm

    Reply

    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

Your email address will not be published.