Bulk change prices in Magento

First of all, sorry for the misleading title. This article is actually about deducting the tax from prices in Magento via SQL. A few days ago I got an e-mail by a client in which he mentioned that he entered the prices including tax in Magento, instead of the prices excluding tax. After a short investigation on the internet on how to easily edit the prices I came across a plugin which costs around $60. But I thought I could easily manage this without purchasing the plugin. I tried the import/export function but that seemed to leave out certain products and I didn’t want to risk creating duplicate content so I decided to write a small code snippit to change the prices.

The script below is based on the following data:

• the attribute_id for attribute_code ‘price’ in table eav_attribute is 64
• the tax rate used was the Dutch 19% rate, so all prices are divided by 1.19
• this was used for Magento 1.4.1.1

// Connect to your database and provide a databasehandle named $db
$result = mysql_db_query($db, 'SELECT * FROM tbl_catalog_product_entity_decimal WHERE attribute_id=64');
while($row = mysql_fetch_array($result))
{
	// Always round your number, you don't want anything below 1 cent because this could get you in some serious trouble with payment methods like iDEAL..
	$newprice = number_format(round(($row['value']/1.19), 2), 4, '.', '');
	mysql_db_query($db, 'UPDATE tbl_catalog_product_entity_decimal SET value="' . $newprice . '" WHERE value_id=' . $row['value_id']);
}

After you’ve executed this piece of code you should probably rebuild your catalog indexes.

I’m aware of the option to indicate that entered prices are including taxes but again, some payment methods ( iDEAL ) might stop working if you use this setting. You could also use the above snippit to increase all prices, for instance, 10 percent.