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

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

Have you ever ticked a ‘Use Default Value’ checkbox for Price (in store view level) and noticed the changes in 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 default value for all the products in that store just remove the ‘AND entity_id = {entity_id}’ filter.

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

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

How to find the size / rows of Magento database & tables?

February 17, 2013  |  1 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

Introduction

Magento is a giant e-commerce application having more than 300 tables. It uses an eav model concept and provides different complex features which makes the database huge.

Sometimes you may wonder what’s the database size of your Magento database or individual tables so that you can work on some optimization task or freeing some of your server space.
Here we are going to discuss some SQL queries which seems to be helpful.

SQL Snippets

1. Find size & rows of Magento database

SELECT 
  TABLE_SCHEMA AS "Database",
  SUM(TABLE_ROWS) AS "Rows #",
  ROUND(
    SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
    2
  ) AS "Size (MB)" 
FROM
  information_schema.TABLES 
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name' 
 GROUP BY TABLE_SCHEMA;

2. Find size & rows of Magento database tables

SELECT 
  TABLE_NAME AS "Table",
  TABLE_ROWS AS "Rows #",
  ROUND(
    (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
    2
  ) AS "Size (MB)" 
FROM
  information_schema.TABLES 
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name' 

3. Find size & rows of Magento log tables

SELECT 
  TABLE_NAME AS "Table",
  TABLE_ROWS AS "Rows #",
  ROUND(
    (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
    2
  ) AS "Size (MB)" 
FROM
  information_schema.TABLES 
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name' 
  AND (
    TABLE_NAME LIKE 'log_%' 
    OR TABLE_NAME LIKE 'report_%' 
    OR TABLE_NAME LIKE 'dataflow_%' 
    OR TABLE_NAME = 'catalog_compare_item'
  ) 
ORDER BY TABLE_ROWS DESC 

Magento log tables info

These queries can be useful for optimizing your database. Suppose say if log tables are huge with large no of data then you can optimize the log tables by truncating them using following SQL:

TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;
TRUNCATE catalog_compare_item;

Caution: Always take a DB backup before performing truncate operation.

Hope you found this article useful.
Thanks for reading!

Getting super attributes info using raw SQL in Magento

April 22, 2012  |  1 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

Introduction

While creating a configurable product, you need to create an attribute(s) which will be used as select option(s) for the customer(For Example: Color, Size etc.). These kinda attributes are called super attributes or configurable attributes.

In this article we will be discussing how to get different info about super attributes using raw SQL.

1. Getting Super Attribute Info

SELECT 
  attribute_id,
  attribute_code,
  frontend_label 
FROM
  eav_attribute 
WHERE attribute_id IN 
  (SELECT 
    attribute_id 
  FROM
    catalog_product_super_attribute 
  WHERE product_id = $productId)

2. Getting Super Attribute Labels

SELECT 
  value 
FROM
  catalog_product_super_attribute_label 
WHERE product_super_attribute_id IN 
  (SELECT 
    product_super_attribute_id 
  FROM
    catalog_product_super_attribute 
  WHERE product_id = $productId)

3. Getting Super Attribute Prices

SELECT 
  is_percent, 
  pricing_value 
FROM
  catalog_product_super_attribute_pricing 
WHERE product_super_attribute_id IN 
  (SELECT 
    product_super_attribute_id 
  FROM
    catalog_product_super_attribute 
  WHERE product_id = $productId)

4. Getting Super Attribute Products (Associated Simple Products)

SELECT 
  product_id 
FROM
  catalog_product_super_link 
WHERE parent_id = $productId

Note: $productId = product id of configurable product

5. Super Attributes Schema

Super Attributes Schema

Hope this article gave at least some info about Super Attributes.
Thanks for reading!

Cheers!!

Magento EAV structure & role of eav_attribute’s backend_type field

April 8, 2012  |  3 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

Introduction:

Magento database is based on EAV(Entity Attribute Value) architecture. And it uses this architecture especially for categories, products, customers & customer addresses.
Before Magento version 1.4.x it used EAV structure for orders as well but thereafter order’s eav structure has been converted to flat structure.

From the defination of EAV:

Entity(E):

Entity actually refers to data item.
In Magento:

catalog_category_entity
catalog_product_entity
customer_entity
customer_address_entity

are the entity tables for categories, products, customers & customer addresses respectively.

Attribute(A):

Attribute refers to the different attributes of an Entity.
In Magento, attributes related to an entity are stored in a single table: eav_attribute but differentiated by field: entity_type_id.

We can easily find all the attributes related to an entity using the following SQL query(For Example: product):

SELECT 
  attribute_code,
  attribute_id,
  backend_type 
FROM
  eav_attribute 
WHERE entity_type_id = 
  (SELECT 
    entity_type_id 
  FROM
    eav_entity_type 
  WHERE entity_type_code = 'catalog_product')

Which gives the following results:

+----------------------------+--------------+--------------+
| attribute_code             | attribute_id | backend_type |
+----------------------------+--------------+--------------+
| activation_information     |          496 | text         |
| color                      |          272 | int          |
| color_code                 |          950 | varchar      |
| computer_manufacturers     |          510 | int          |
| contrast_ratio             |          875 | int          |
| cost                       |          100 | decimal      |
| country_orgin              |          507 | text         |
| cpu_speed                  |          877 | int          |
| created_at                 |          930 | static       |
| custom_design              |          571 | varchar      |
| custom_design_from         |          572 | datetime     |
| custom_design_to           |          573 | datetime     |
| custom_layout_update       |          531 | text         |
| description                |           97 | text         |
| dimension                  |          494 | text         |
| enable_googlecheckout      |          903 | int          |
| finish                     |          509 | text         |
| gallery                    |          271 | varchar      |
| gender                     |          501 | int          |
| gift_message_available     |          562 | varchar      |
| harddrive_speed            |          878 | varchar      |
| hardrive                   |          499 | text         |
| has_options                |          838 | static       |
| image                      |          106 | varchar      |
| image_label                |          879 | varchar      |
| in_depth                   |          492 | text         |
| is_imported                |          949 | int          |
| is_recurring               |          933 | int          |
| links_exist                |          947 | int          |
| links_purchased_separately |          904 | int          |
| links_title                |          906 | varchar      |
| manufacturer               |          102 | int          |
| max_resolution             |          873 | varchar      |
| media_gallery              |          703 | varchar      |
| megapixels                 |          513 | int          |
| memory                     |          498 | text         |
| meta_description           |          105 | varchar      |
| meta_keyword               |          104 | text         |
| meta_title                 |          103 | varchar      |
| minimal_price              |          503 | decimal      |
| model                      |          495 | text         |
| name                       |           96 | varchar      |
| news_from_date             |          704 | datetime     |
| news_to_date               |          705 | datetime     |
| old_id                     |          110 | int          |
| options_container          |          836 | varchar      |
| package_id                 |          951 | int          |
| page_layout                |          929 | varchar      |
| price                      |           99 | decimal      |
| price_type                 |          859 | int          |
| price_view                 |          862 | int          |
| processor                  |          497 | text         |
| ram_size                   |          874 | varchar      |
| recurring_profile          |          934 | text         |
| required_options           |          837 | static       |
| response_time              |          876 | varchar      |
| room                       |          508 | int          |
| samples_title              |          905 | varchar      |
| screensize                 |          500 | text         |
| shape                      |          476 | text         |
| shipment_type              |          863 | int          |
| shipping_qty               |          952 | int          |
| shirt_size                 |          525 | int          |
| shoe_size                  |          502 | int          |
| shoe_type                  |          107 | int          |
| short_description          |          506 | text         |
| sku                        |           98 | static       |
| sku_type                   |          860 | int          |
| small_image                |          109 | varchar      |
| small_image_label          |          880 | varchar      |
| special_from_date          |          568 | datetime     |
| special_price              |          567 | decimal      |
| special_to_date            |          569 | datetime     |
| status                     |          273 | int          |
| tax_class_id               |          274 | int          |
| thumbnail                  |          493 | varchar      |
| thumbnail_label            |          881 | varchar      |
| tier_price                 |          270 | decimal      |
| updated_at                 |          931 | static       |
| url_key                    |          481 | varchar      |
| url_path                   |          570 | varchar      |
| visibility                 |          526 | int          |
| weight                     |          101 | decimal      |
| weight_type                |          861 | int          |
+----------------------------+--------------+--------------+

Similarly you can find attributes for other entities just by using following values for entity_type_code:

catalog_category
customer
customer_address

for categories, customers & customer address respectively.

Note: You can get the related values for entity_type_code from table: eav_entity_type.

Value(V):

Value refers to the actual value of the attribute of the entity.
In Magento attribute values of an entity (for example: product) are stored in catalog_product_entity_[backend_type] tables.
Where [backend_type] refers to the value of field: backend_type (except value ‘static’) of table: eav_attribute.

Following SQL is used to find all the backend types related to a product entity:

SELECT DISTINCT 
  backend_type 
FROM
  eav_attribute 
WHERE entity_type_id = 
  (SELECT 
    entity_type_id 
  FROM
    eav_entity_type 
  WHERE entity_type_code = 'catalog_product')

Which results:

+--------------+
| backend_type |
+--------------+
| text         |
| int          |
| varchar      |
| decimal      |
| static       |
| datetime     |
+--------------+

So tables:

catalog_product_entity_text 
catalog_product_entity_int 
catalog_product_entity_varchar 
catalog_product_entity_decimal
catalog_product_entity_datetime

are used for storing values for an attributes of related backend_type.

Note: You must be wondering about backend_type = static. For an attribute with the static backend_type, values are stored as a column directly in the entity table. For example: sku is an attribute of backend_type = static and it’s values are stored in the entity table itself: catalog_product_entity under field: sku.

That’s all about the definition of EAV relating to Magento.
Hopefully now you are able to differentiate which attribute values goes where (in which table) based on the backend_type.

Thanks for reading & Happy E-Commerce!!

Updating product qty in Magento in an easier & faster way

December 2, 2011  |  31 Comments  |  by Raj (MagePsycho)  |  Latest, Magento, Mysql

Introduction

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

Steps:

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

2> Create a file: update_stocks.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 _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 _getIdFromSku($sku){
	$connection = _getConnection('core_read');
	$sql		= "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?";
	return $connection->fetchOne($sql, array($sku));
}

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

	$sql			= "UPDATE " . _getTableName('cataloginventory_stock_item') . " csi,
					   " . _getTableName('cataloginventory_stock_status') . " css
	                   SET
					   csi.qty = ?,
					   csi.is_in_stock = ?,
	                   css.qty = ?,
					   css.stock_status = ?
					   WHERE
					   csi.product_id = ?
			           AND csi.product_id = css.product_id";
	$isInStock		= $newQty > 0 ? 1 : 0;
	$stockStatus	= $newQty > 0 ? 1 : 0;
	$connection->query($sql, array($newQty, $isInStock, $newQty, $stockStatus, $productId));
}
/***************** UTILITY FUNCTIONS ********************/

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

$message = '';
$count   = 1;
foreach($data as $_data){
	if(_checkIfSkuExists($_data[0])){
		try{
			_updateStocks($_data);
			$message .= $count . '> Success:: Qty (' . $_data[1] . ') of Sku (' . $_data[0] . ') has been updated. <br />';

		}catch(Exception $e){
			$message .=  $count .'> Error:: while Upating  Qty (' . $_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_stocks.php

You will see how fast the qty of corresponding skus are updated.

Thanks for reading guys!!

Happy Importing!!