How to set product attribute values to take values from default store?

February 7, 2014  |  No Comments  |  by Raj (MagePsycho)  |  Magento, Mysql

Have you ever ticked a ‘Use Default Value’ checkbox for Price (in store view level) and noticed the changes in the database?

If you tick the ‘Use Default Value’ checkbox for any product attribute (say Price) in store view level and save it. This will delete the related row in the database for that attribute, for the specific product, for that store id. And attribute will now take the default value.

Lets explain this scenario in detail.

1st Approach

Use Default Value - Before Tick

Fig: Before Checkbox Tick

Use Default Value Checkbox

Fig: After Checkbox Tick

So if you want to set the default value for any attribute, you can just tick the checkbox and save the product.
This process can be overhead in case of hundreds of product.

2nd Approach

Alternatively, you can use SQL approach which is actually the background operation.
1 Find entity_type_id

SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product';

2 Find attribute_id

SELECT attribute_id FROM eav_attribute WHERE entity_type_id = 10 AND attribute_code = 'price';

Suppose attribute_code = price and entity_type_id = 10
3 Check if row for that attribute exists

SELECT * FROM catalog_product_entity_decimal WHERE entity_type_id = 10 AND attribute_id = 99 AND store_id = 3 AND entity_id = 123;

4 Delete that particular row

DELETE FROM catalog_product_entity_decimal WHERE entity_type_id = 10 AND attribute_id = 99 AND store_id = 3 AND entity_id = 123;

Now try to run SQL #3, will now give you the empty result.

So in general, You can use the following SQL

DELETE FROM {table_name}
WHERE entity_type_id = {entity_type_id}
AND attribute_id = {attribute_id}
AND store_id = {store_id}
AND entity_id = {entity_id}

In case you want to set the default value for all the products in that store just remove the ‘AND entity_id = {entity_id}’ filter.

Caution:
In order to save you from worst-case scenario, just take a backup of your DB.

Last but not least, must thank @MariusStrajeru for his valuable suggestions and willing to help nature.

How to fix the issue: Product images missing in backend but not in frontend

June 22, 2012  |  48 Comments  |  by Raj (MagePsycho)  |  Latest, Magento

Introduction

Recently i have to fix a strange issue for one of my client. Issue was that product images were missing from ‘Images’ tab of product edit form (as depicted below), though they were displaying fine in the frontend.

Missing Product Images In Backend

After going through the table relationship for catalog product images (as shown below), it was found that required rows were missing in table ‘catalog_product_entity_media_gallery’. Note that table ‘catalog_product_entity_media_gallery_value’ is not necessary unless you want to sort or label the product images.

Schema For Product Images

By inspecting the html code of Images tab of product edit form via firebug, it was seen that ‘value’ attribute had empty json:

<input type="hidden" value="[]" name="product[media_gallery][images]" id="media_gallery_content_save">

Further Debugging:
Using template path hints extension for admin: Easy Template Path Hints, it was found that the responsible template and block class were: ‘app/design/adminhtml/default/default/template/catalog/product/helper/gallery.phtml’ and ‘Mage_Adminhtml_Block_Catalog_Product_Helper_Form_Gallery_Content’ respectively.

Looking at the block class and .phtml code it was found that Input element has empty json because of Mage_Adminhtml_Block_Catalog_Product_Helper_Form_Gallery_Content::getImagesJson() method.

When populated the images from table ‘catalog_product_entity_varchar’ to ‘catalog_product_entity_media_gallery’ for those products, products images were seen at the backend(refer to the code below for more info).
Here goes the steps for fixing:

Steps

1> Prepare CSV file(update_missing_images.csv) with only one field: sku and upload to the root of Magento installation.
Note: This will contain the sku of those products whose images are missing at the backend.

2> Create a file: update_missing_images.php and upload to the root of magento installation and paste the following code:

<?php
/**
 * @author		MagePsycho <info@magepsycho.com>
 * @website		http://www.magepsycho.com
 * @category	Export / Import
 */
$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app('admin');
Mage::register('isSecureArea', 1);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

set_time_limit(0);
ini_set('memory_limit','1024M');

/***************** UTILITY FUNCTIONS ********************/
function _log($message, $file = 'update_missing_images.log'){
	Mage::log($message, null, $file);
}

function _getIndex($field) {
	global $fields;
	$result = array_search($field, $fields);
	if($result === false){
		$result = -1;
	}
	return $result;
}

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

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

function _getAttributeId($attribute_code = 'price'){
	$connection = _getConnection('core_read');
	$sql = "SELECT attribute_id
				FROM " . _getTableName('eav_attribute') . "
			WHERE
				entity_type_id = ?
				AND attribute_code = ?";
	$entity_type_id = _getEntityTypeId();
	return $connection->fetchOne($sql, array($entity_type_id, $attribute_code));
}

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

function _getIdFromSku($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
	return $connection->fetchOne($sql, array($sku));
}

function _checkIfSkuExists($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity') . "	WHERE sku = ?";
	$count		= $connection->fetchOne($sql, array($sku));
	if($count > 0){
		return true;
	}else{
		return false;
	}
}

function _checkIfRowExists($productId, $attributeId, $value){
	$tableName  = _getTableName('catalog_product_entity_media_gallery');
	$connection = _getConnection('core_read');
	$sql		= "SELECT COUNT(*) AS count_no FROM " . _getTableName($tableName) . " WHERE entity_id = ? AND attribute_id = ?  AND value = ?";
	$count		= $connection->fetchOne($sql, array($productId, $attributeId, $value));
	if($count > 0){
		return true;
	}else{
		return false;
	}
}

function _insertRow($productId, $attributeId, $value){
	$connection				= _getConnection('core_write');
	$tableName				= _getTableName('catalog_product_entity_media_gallery');

	$sql = "INSERT INTO " . $tableName . " (attribute_id, entity_id, value) VALUES (?, ?, ?)";
	$connection->query($sql, array($attributeId, $productId, $value));
}

function _updateMissingImages($count, $productId, $data){
	$connection				= _getConnection('core_read');
	$smallImageId			= _getAttributeId('small_image');
	$imageId				= _getAttributeId('image');
	$thumbnailId			= _getAttributeId('thumbnail');
	$mediaGalleryId			= _getAttributeId('media_gallery');

	//getting small, base, thumbnail images from catalog_product_entity_varchar for a product
	$sql	= "SELECT * FROM " . _getTableName('catalog_product_entity_varchar') . " WHERE attribute_id IN (?, ?, ?) AND entity_id = ? AND `value` != 'no_selection'";
	$rows	= $connection->fetchAll($sql, array($imageId, $smallImageId, $thumbnailId, $productId));
	if(!empty($rows)){
		foreach($rows as $_image){
			//check if that images exist in catalog_product_entity_media_gallery table or not
			if(!_checkIfRowExists($productId, $mediaGalleryId, $_image['value'])){
				//insert that image in catalog_product_entity_media_gallery if it doesn't exist
				_insertRow($productId, $mediaGalleryId, $_image['value']);
				/* Output / Logs */
				$missingImageUpdates = $count . '> Updated:: $productId=' . $productId . ', $image=' . $_image['value'];
				echo $missingImageUpdates.'<br />';
				_log($missingImageUpdates);
			}
		}
		$separator = str_repeat('=', 100);
		_log($separator);
		echo $separator . '<br />';
	}
}
/***************** UTILITY FUNCTIONS ********************/

$messages			= array();
$csv				= new Varien_File_Csv();
$data				= $csv->getData('update_missing_images.csv'); //path to csv
$fields				= array_shift($data);
#print_r($fields); print_r($data); exit;

$message = '<hr />';
$count   = 1;
foreach($data as $_data){
	$sku									= isset($_data[_getIndex('sku')]) ? trim($_data[_getIndex('sku')]) : '';
	if(_checkIfSkuExists($sku)){
		try{
			$productId = _getIdFromSku($sku);
			_updateMissingImages($count, $productId, $_data);
			$message .= $count . '> Success:: While Updating Images of Sku (' . $sku . '). <br />';

		}catch(Exception $e){
			$message .=  $count .'> Error:: While Upating Images of Sku (' . $sku . ') => '.$e->getMessage().'<br />';
		}
	}else{
		$message .=  $count .'> Error:: Product with Sku (' . $sku . ') does\'t exist.<br />';
	}
	$count++;
}
echo $message;

3> Open your browser and run the following url:
http://your-magento-url/update_missing_images.php

4> That’s all. Try to browse the Images tab, now you will see those missing images.

Hope this helps somebody!

Playing with Attribute Set in Magento

November 18, 2011  |  5 Comments  |  by Raj (MagePsycho)  |  Latest, Magento

Introduction

An attribute set is a collection of attributes which can be created/edited from Catalog -> Manage Attribute Sets in backend.

Some useful snippets regarding Attribute Set.

1> Getting Attribute Set Details

$attributeSetModel = Mage::getModel('eav/entity_attribute_set');
$attributeSetId	   = 9; // or $_product->getAttributeSetId(); if you are using product model
$attributeSetModel->load($attributeSetId);
print_r($attributeSetModel->getData());

2> Getting Attribute Set Id by Name

$entityTypeId = Mage::getModel('eav/entity')
				->setType('catalog_product')
				->getTypeId();
$attributeSetName	= 'Default';
$attributeSetId		= Mage::getModel('eav/entity_attribute_set')
					->getCollection()
					->setEntityTypeFilter($entityTypeId)
					->addFieldToFilter('attribute_set_name', $attributeSetName)
					->getFirstItem()
					->getAttributeSetId();
echo $attributeSetId;

Note that the following code doesn’t work

$attributeSetId = Mage::getModel('eav/entity_attribute_set')
->load($attributeSetName, 'attribute_set_name')
->getAttributeSetId();

Because if you refer to eav_attribute_set table then you can see there are lots of rows with same attribute set name(for example: Default)

3> Getting all Attribute Sets by Entity Type(catalog_product)

$entityTypeId = Mage::getModel('eav/entity')
				->setType('catalog_product')
				->getTypeId();
$attributeSetCollection = Mage::getModel('eav/entity_attribute_set')
				->getCollection()
				->setEntityTypeFilter($entityTypeId);
foreach($attributeSetCollection as $_attributeSet){
	print_r($_attributeSet->getData());
}

Alternatively, you can also use:

$attributeSets = Mage::getModel('catalog/product_attribute_set_api')->items();
foreach($attributeSets as $_attributeSet){
	print_r($_attributeSet);
}

4> Getting all attributes in an Attribute Set

$attributes = Mage::getModel('catalog/product_attribute_api')->items($attributeSetId);
foreach($attributes as $_attribute){
	print_r($_attribute);
}

5> Filtering Products by Attribute Set

$productCollection = Mage::getModel('catalog/product')
					->getCollection()
					->addAttributeToSelect('*')
					->addFieldToFilter('attribute_set_id', $attributeSetId);
foreach($productCollection as $_product){
	print_r($_product->getData());
}

Hope you will find these snippets useful/helpful.
Please do share if you have any useful code snippets regarding Attribute Set.

Thanks for reading!

Magento utility function: How to easily create select box for drop-down attributes?

November 13, 2011  |  12 Comments  |  by Raj (MagePsycho)  |  Latest, Magento

From now onward i will be sharing custom utility functions on regular basis which will make your programming life easier to some extent at least in case of Magento 🙂

Here i am going to share an utility function which will help you to create select box for drop-down attributes(manufacturer, color, size etc.) of a product.

Utility Function:

function getSelectBox($attributeCode, $label = '', $defaultSelect = null, $extraParams = null){
	$options			= array();
	$product			= Mage::getModel('catalog/product');
	$attribute			= $product->getResource()->getAttribute($attributeCode);
	if($attribute->usesSource()){
		$options = $attribute->getSource()->getAllOptions(false);
		array_unshift($options, array('label' => $label, 'value' => ''));
	}

	$select = Mage::app()->getLayout()->createBlock('core/html_select')
			->setName($attributeCode)
			->setId($attributeCode)
			->setTitle($label)
			->setValue($defaultSelect)
			->setExtraParams($extraParams)
			->setOptions($options);
	return $select->getHtml();
}

Now you can easily draw select box for any drop-down attribute (for example: manufacturer) by simply calling the above function as:

<?php echo getSelectBox('manufacturer', 'Select Manufacturer'); ?>

Output:

Select Box - Manufacturer

Note: You can also pass default value to be selected, extra parameter like css class, javascript event handlers etc via function arguments. Or you can customize the function easily as per your requirements.

You can define the above function in your Helper class and can call from anywhere within your application in order to create select box for any drop-down attributes of product.

Happy Coding!!

Updating product prices in Magento in an easier & faster way

November 4, 2011  |  66 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

Introduction

Product prices can be updated via default import profile though but this is very slow and requires lots of csv fields(besides sku & price) for updating.
Today i am going to talk about updating product prices just by using csv with two fields: sku & price(new) which is very fast enough even for thousands of products.

Steps:

1> Prepare CSV file(prices.csv) with two fields: sku & price and upload in the root of Magento installation.
You can see the snapshot how it should look like:

prices.csv

prices.csv


Note: Field/Values should be enclosed within double quotes(“) and separated by a comma(,). I would prefer OpenOffice for csv formatting.

2> Create a file: update_prices.php in the root of magento installation and paste the following code:

<?php
/**
 * @author		MagePsycho <info@magepsycho.com>
 * @website		http://www.magepsycho.com
 * @category	Export / Import
 */
$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app('admin');
Mage::register('isSecureArea', 1);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

set_time_limit(0);
ini_set('memory_limit','1024M');

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

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

function _getAttributeId($attribute_code = 'price'){
	$connection = _getConnection('core_read');
	$sql = "SELECT attribute_id
				FROM " . _getTableName('eav_attribute') . "
			WHERE
				entity_type_id = ?
				AND attribute_code = ?";
	$entity_type_id = _getEntityTypeId();
	return $connection->fetchOne($sql, array($entity_type_id, $attribute_code));
}

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

function _getIdFromSku($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
	return $connection->fetchOne($sql, array($sku));

}

function _checkIfSkuExists($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity') . "	WHERE sku = ?";
	$count		= $connection->fetchOne($sql, array($sku));
	if($count > 0){
		return true;
	}else{
		return false;
	}
}

function _updatePrices($data){
	$connection		= _getConnection('core_write');
	$sku			= $data[0];
	$newPrice		= $data[1];
	$productId		= _getIdFromSku($sku);
	$attributeId	= _getAttributeId();

	$sql = "UPDATE " . _getTableName('catalog_product_entity_decimal') . " cped
				SET  cped.value = ?
			WHERE  cped.attribute_id = ?
			AND cped.entity_id = ?";
	$connection->query($sql, array($newPrice, $attributeId, $productId));
}
/***************** UTILITY FUNCTIONS ********************/

$csv				= new Varien_File_Csv();
$data				= $csv->getData('prices.csv'); //path to csv
array_shift($data);

$message = '';
$count   = 1;
foreach($data as $_data){
	if(_checkIfSkuExists($_data[0])){
		try{
			_updatePrices($_data);
			$message .= $count . '> Success:: While Updating Price (' . $_data[1] . ') of Sku (' . $_data[0] . '). <br />';

		}catch(Exception $e){
			$message .=  $count .'> Error:: While Upating  Price (' . $_data[1] . ') of Sku (' . $_data[0] . ') => '.$e->getMessage().'<br />';
		}
	}else{
		$message .=  $count .'> Error:: Product with Sku (' . $_data[0] . ') does\'t exist.<br />';
	}
	$count++;
}
echo $message;

3> Open your browser and run the following url:
http://your-magento-url/update_prices.php
You will see how fast the prices of corresponding skus are updated.
I have tested with 2000 proudcts and update was accomplished within seconds. Isn’t that fast enough?

EDIT
Recently we have developed an extension for importing all types of prices(regular, special, tier & group prices) via web-interface using the same raw SQL concept. Here goes the link for the extension:
Mass Importer Pro: Price Importer (Regular, Sepcial, Tier & Group Prices)

Thanks for reading. Please share your success story with this code.

Happy Importing!!