List out all the product names in magento via sql code

October 12, 2011  |  4 Comments  |  by Raj (MagePsycho)  |  Magento, Mysql

You know we can easily fetch the product names using Model object.
But what if you want to list out all the product names via sql code?
Don’t worry here is the sql code for you:

SELECT `value` AS product_name
FROM catalog_product_entity_varchar
WHERE entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product') 
AND attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))


Export / Import CMS pages / Static Blocks Via SSH

October 10, 2011  |  4 Comments  |  by Raj (MagePsycho)  |  Linux, Magento, Mysql

It’s really a tedious task when you have to copy all the cms pages / static blocks from your development server to the live server. If you are importing the whole database then you are lucky enough but if you are updating in already installed magento server then :(, luckily you can use the following commands via SSH:

1> Export from your development server:

mysqldump -u [username] -p[password] [database] cms_page cms_page_store cms_block cms_block_store | grep INSERT | sed 's/INSERT INTO/REPLACE INTO/' > cms-export.sql

Note: Don’t forget to add prefix to cms_* tables if you have prefixed your tables.

2> Import to your live/destination server:

mysql -u [username] -p[password] [database] < cms-export.sql

Hope this saves your time while copying cms pages & static blocks.

Let’s share if anyone has better idea.

Happy E-Commerce!!

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.