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

People make mistakes. It’s part of human nature. But some clients don’t understand this 😉
A few months ago I made some mistake in creating a custom attribute for a 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 fill 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.

• 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
• Edit the attribute codes: $attributeCodes (You can find the code below), then copy the below file(varcharToPriceConverter.php) to the root of your Magento installation
• Run the script from URL as http://your-magento-base-url/varcharToPriceConverter.php

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

<?php
/**
 * @author 	Raj KB <[email protected]> 
 * @website     https://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));
			$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 . '';
			$insertCount++;
		}
		$sql = 'DELETE FROM ' . _getTableName('catalog_product_entity_varchar') . ' WHERE value_id = ?';
		$connection->query($sql, $row['value_id']);
		$deleteOutput .= $deleteCount . '> DELETED::'.$row['value_id'].'';
		$deleteCount++;
	}
	echo '=================================================';
	echo 'INSERTED';
	echo $insertOutput;
	echo '=================================================';
	echo '=================================================';
	echo 'DELETED';
	echo $deleteOutput;
	echo '=================================================';
}

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: How to convert varchar type attribute to decimal/price type in Magento?

Hope the sharing was helpful.

Happy E-Commerce!!