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

<?php
/**
 * @author	MagePsycho <info@magepsycho.com>	
 * @website     http://www.magepsycho.com
 * 
 */
$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app('admin');
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);
		if(!$checkIfDecimalValueExists){
			$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 />';
			$insertCount++;
		}
		$sql = 'DELETE FROM ' . _getTableName('catalog_product_entity_varchar') . ' WHERE value_id = ?';
		$connection->query($sql, $row['value_id']);
		$deleteOutput .= $deleteCount . '> DELETED::'.$row['value_id'].'<br />';
		$deleteCount++;
	}
	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:
copyVarcharToDecimal();

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

Download File: varcharToPriceConverter.php

Hope the sharing was helpful.

Happy E-Commerce!!

Posted in Magento, Mysql, PHP and tagged . Bookmark the permalink.

About Raj (MagePsycho)

Raj, the guy behind MagePsycho is a Zend PHP5 Certified Engineer, Magento Certified Developer, Magento Moderator / Freelancer with specialization in web applications (CMS, E-Commerce, ERP etc.). Catch him on: Twitter: @magepsycho Skype: magentopycho