How to convert varchar type attribute to price type in magento?

October 10, 2011  |  No Comments  |  by Raj (MagePsycho)  |  Magento, Mysql, PHP

People make mistakes. It’s part of human nature. But some clients don’t understand this πŸ˜‰
Few months ago i made some mistake in creating custom attribute for product. I created attribute type = ‘varchar’ but it was supposed to be of type = ‘price’ since it was going to be used for shipping cost per product.

Client added thousand of products and so did filled up the custom attribute value for shipping cost. He added the shipping cost values in dutch format (, as decimal separator and . as thousands separator). Storing such values in varchar and displaying it as price with currency symbol (magento way) in frontend obviously creates issues. So the only choice i had is to convert the varchar type attribute to price type.

Following are the steps that i followed in order to solve this problem.

      1> First You need to convert your varchar attribute type to decimal. For this go to ‘eav_attribute’ table and edit the following fields for the related attributes:
      -> backend_model = catalog/product_attribute_backend_price
      -> backend_type = decimal
      -> frontend_input = price
      2> After step 1, Edit the attribute codes: $attributeCodes (seein below code), then copy the below file(varcharToPriceConverter.php) to the root of your magento installation
      & simply call it from url as: http://your-magento-base-url/varcharToPriceConverter.php

      3> That’s all πŸ™‚

Here is the code used for the conversion:
File: /varcharToPriceConverter.php

 * @author	MagePsycho <>	
 * @website
$mageFilename = 'app/Mage.php';
require_once $mageFilename;
ini_set('display_errors', 1);
Mage::register('isSecureArea', 1);

# EDIT HERE...Note the single quotes inside the double quotes. This is necessary unless you modify the function yourself
# Note that these attribute codes are those attributes whose type is to be changed.
$_attributeCodes = array("'shipping_cost_with_tax'", "'shipping_cost_without_tax'");

function copyVarcharToDecimal(){
	$connection			= _getConnection('core_write');
	$attributeIds		= (string) implode(',', _getAttributeIds());	
	$entityTypeId		= (int) _getEntityTypeId();
	$sql				= 'SELECT * FROM ' . _getTableName('catalog_product_entity_varchar') . ' WHERE attribute_id IN ('.$attributeIds.') AND entity_type_id = '.$entityTypeId;
	$rows				= $connection->fetchAll($sql);
	$insertCount		= 1;
	$deleteCount		= 1;
	$insertOutput		= '';
	$deleteOutput		= '';
	foreach($rows as $row){
		$checkIfDecimalValueExists = _checkIfDecimalValueExists($row);
			$sql = 'INSERT INTO ' . _getTableName('catalog_product_entity_decimal') . ' (`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`)
					VALUES (?,?,?,?,?)';
			$price = $row['value'];
			$price = trim(str_replace(',', '.', $price));  //in case if values are stored in dutch format
			$connection->query($sql, array($row['entity_type_id'], $row['attribute_id'], $row['store_id'], $row['entity_id'], $price));
			$insertOutput .= $insertCount . '> INSERTED::' . $connection->lastInsertId() . ' :: ' .$row['value'] . ' => ' . $price . '<br />';
		$sql = 'DELETE FROM ' . _getTableName('catalog_product_entity_varchar') . ' WHERE value_id = ?';
		$connection->query($sql, $row['value_id']);
		$deleteOutput .= $deleteCount . '> DELETED::'.$row['value_id'].'<br />';
	echo '=================================================<br />';
	echo '<strong>INSERTED</strong><br />';
	echo $insertOutput;
	echo '=================================================<br />';
	echo '=================================================<br />';
	echo '<strong>DELETED</strong><br />';
	echo $deleteOutput;
	echo '=================================================<br />';

function _getTableName($tableName){
	return Mage::getSingleton('core/resource')->getTableName($tableName);

function _getConnection($type = 'core_read'){
	return Mage::getSingleton('core/resource')->getConnection($type);

function _getAttributeIds(){
	global $_attributeCodes;
	$attributeCodes = (string) implode(',', $_attributeCodes);	
	$connection = _getConnection('core_read');
	$sql = "SELECT attribute_id
			FROM " . _getTableName('eav_attribute') . "
			WHERE attribute_code
			IN (
				" . $attributeCodes . "
	return $connection->fetchCol($sql);

function _getEntityTypeId(){
	$connection = _getConnection('core_read');
	$sql		= "SELECT entity_type_id FROM " . _getTableName('eav_entity_type') . " WHERE entity_type_code = 'catalog_product'";
	return $connection->fetchOne($sql);

function _checkIfDecimalValueExists($row){
	$connection = _getConnection('core_write');
	$sql		= 'SELECT COUNT(*) FROM ' . _getTableName('catalog_product_entity_decimal') . ' WHERE attribute_id = ? AND entity_type_id = ? AND store_id = ? AND entity_id = ?';
	$result		= $connection->fetchOne($sql, array($row['attribute_id'], $row['entity_type_id'], $row['store_id'], $row['entity_id']));
	return $result > 0 ? true : false;

#simply call as:

Note: Don’t forget to backup your database before the conversion operation.

Download File: varcharToPriceConverter.php

Hope the sharing was helpful.

Happy E-Commerce!!

Magento CheatSheet

September 21, 2011  |  No Comments  |  by Raj (MagePsycho)  |  Latest, Magento

I have been using this great cheat-sheet from my initial days of Magento development.
Hope you will find this useful too.

Download CheatSheet
Thanks Vinai for providing this great cheatsheet for Magento Developer.


Usage of ob_start() in fixing onepage checkout issues

September 21, 2011  |  3 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, PHP

Onepage checkout steps are so sensitive that even a single character echo’ed from anywhere (due to some mistakes or php errors) will make your checkout steps not working.

I just recently faced one issue: Onepage checkout was not redirecting to order confirmation page after clicking on Place Order button (Review Page). When i checked the console via firebug i found the following ajax response:
You can note there there is extra character ‘f’ which makes the json response invalid and the opcheckout.js can’t operate properly & hence no further redirection to third party or order confirmation page.

Since the client was in rush, i solved the issue using the concept of ob_start() function of php as:
File: app/code/core/Mage/Checkout/OnepageController.php

public function saveOrderAction()
	$outputBuffer = ob_get_contents();
	Mage::log('outputTrapped::' . $outputBuffer, null, 'onepagecheckout.log');

By using the ob_start() on the start of the function, output are captured in the buffer instead of direct output to the browser.

I know best bet is to find the related place and prevent the direct output to the browser but i just posted this as an example on dealing with ob_start() in case of onepage checkout steps.

Hope this gave some idea.

Hello world!

September 17, 2011  |  No Comments  |  by Raj (MagePsycho)  |  Javascript, jQuery, Latest, Linux, Magento, Mysql, PHP, Web Service

Dear All
I am trying to share my knowledge gained so far in web development fields from now onwards via this blog.
I will be sharing stuffs related to PHP/Mysql, Magento & more.
Well nothing more to say, just stay tuned with my blog πŸ™‚

Lastly, your suggestion/feedback is always valuable and will make my blog alive.

This much for now.