In Magento eCommerce, it is possible to add new attributes to product models and edit the values for these attributes on the Product edit page. This is fine if we only want to add standard data (attributes) to a product, but what if we wanted to do something a little different?
This article explains how to build a simple custom Magento extension that will add a new tab to the product edit page and provide a facility for processing that data when the user hits the Save button.
Creating the Magento Extension
The first step is to create the extension's setup file, which loads the extension into Magento.
app/etc/modules/Fishpig_Customtabs.xml
<?xml version="1.0"?>
<config>
<modules>
<Fishpig_Customtabs>
<active>true</active>
<codePool>local</codePool>
</Fishpig_Customtabs>
</modules>
</config>
This file will register the extension in Magento, telling it to look in the app/code/local/Fishpig/Customtabs/ directory for the extension's code.
app/code/local/Fishpig/Customtabs/etc/config.xml
The next file we make is the extension's config file. This file is a more detailed setup file, containing information about the extension's classes, layout files and everything else we need to make it work.
Notice the events section of this file (below)? To save the product data, we listen for an event that is triggered when ever a product is saved in the Magento Admin. This allows us to access the custom data we have created in the tab and process/save it.
<?xml version="1.0"?>
<config>
<modules>
<Fishpig_CustomTabs>
<version>0.1.0</version>
</Fishpig_CustomTabs>
</modules>
<global>
<blocks>
<customtabs>
<class>Fishpig_Customtabs_Block</class>
</customtabs>
</blocks>
<models>
<customtabs>
<class>Fishpig_Customtabs_Model</class>
</customtabs>
</models>
</global>
<adminhtml>
<layout>
<updates>
<customtabs>
<file>customtabs.xml</file>
</customtabs>
</updates>
</layout>
<events>
<catalog_product_save_after>
<observers>
<fishpig_save_product_data>
<type>singleton</type>
<class>customtabs/observer</class>
<method>saveProductTabData</method>
</fishpig_save_product_data>
</observers>
</catalog_product_save_after>
</events>
</adminhtml>
</config>
app/code/local/Fishpig/Customtabs/Block/Adminhtml/Catalog/Product/Tab.php
<?php
class Fishpig_Customtabs_Block_Adminhtml_Catalog_Product_Tab
extends Mage_Adminhtml_Block_Template
implements Mage_Adminhtml_Block_Widget_Tab_Interface {
/**
* Set the template for the block
*
*/
public function _construct()
{
parent::_construct();
$this->setTemplate('customtabs/catalog/product/tab.phtml');
}
/**
* Retrieve the label used for the tab relating to this block
*
* @return string
*/
public function getTabLabel()
{
return $this->__('My Custom Tab');
}
/**
* Retrieve the title used by this tab
*
* @return string
*/
public function getTabTitle()
{
return $this->__('Click here to view your custom tab content');
}
/**
* Determines whether to display the tab
* Add logic here to decide whether you want the tab to display
*
* @return bool
*/
public function canShowTab()
{
return true;
}
/**
* Stops the tab being hidden
*
* @return bool
*/
public function isHidden()
{
return false;
}
/**
* AJAX TAB's
* If you want to use an AJAX tab, uncomment the following functions
* Please note that you will need to setup a controller to recieve
* the tab content request
*
*/
/**
* Retrieve the class name of the tab
* Return 'ajax' here if you want the tab to be loaded via Ajax
*
* return string
*/
# public function getTabClass()
# {
# return 'my-custom-tab';
# }
/**
* Determine whether to generate content on load or via AJAX
* If true, the tab's content won't be loaded until the tab is clicked
* You will need to setup a controller to handle the tab request
*
* @return bool
*/
# public function getSkipGenerateContent()
# {
# return false;
# }
/**
* Retrieve the URL used to load the tab content
* Return the URL here used to load the content by Ajax
* see self::getSkipGenerateContent & self::getTabClass
*
* @return string
*/
# public function getTabUrl()
# {
# return null;
# }
}
This is a big file and is the main code file for our tab. This file extends Mage_Adminhtml_Block_Template, which is the base Adminhtml template and doesn't do much. Your tab block can extend any block file in Magento, for example, in FishPig's WordPress Integration, we use this method to extend the grid class to display a list of WordPress posts.
On it's own, the above code won't do anything as we haven't included it yet. Let's include it now by creating a layout XML file for our extension.
app/design/adminhtml/default/default/layout/customtabs.xml
This is the layout file for the Adminhtml section of the extension. In here we will include the tab on the Magento Product edit page.
<?xml version="1.0"?>
<layout>
<adminhtml_catalog_product_edit>
<reference name="product_tabs">
<action method="addTab">
<name>my_custom_tab</name>
<block>customtabs/adminhtml_catalog_product_tab</block>
</action>
</reference>
</adminhtml_catalog_product_edit>
</layout>
This file is quite simple but without it, nothing will show on the product edit page. The last thing to do before we can see our tab in action is to create our new template file.
app/design/adminhtml/default/default/template/customtabs/catalog/product/tab.phtml
<?php
/**
* Custom tab template
*/
?>
<div class="input-field">
<label for="custom_field">Custom Field</label>
<input type="text" class="input-text" name="custom_field" id="custom_field" />
</div>
Although this example is simple, you could enter anything you want in here. Using this and your block hopefully you can see how truly limitless the options are to you.
Testing Our Custom Magento Admin Tab
Now that we have the code in place, let's refresh our cache, go to a product edit page and see the tab in action!
Did it work? If it didn't work, check through your XML again as a slight error in any of that will stop everything working.
Now that we have our tab, let's take a look at how to process the data once the user hits the 'Save' button.
Saving our Custom Tab Data
To access the data during the saving process, we have hooked into an event called catalog_product_save_after (see config.xml above). This event is triggered each time a product model is saved. As we have declared our observer for this event inside the adminhtml block, we will only trigger our code when a product model is saved from the Magento Admin.
app/code/local/Fishpig/Customtabs/Model/Observer.php
<?php
class Fishpig_Customtabs_Model_Observer
{
/**
* Flag to stop observer executing more than once
*
* @var static bool
*/
static protected $_singletonFlag = false;
/**
* This method will run when the product is saved from the Magento Admin
* Use this function to update the product model, process the
* data or anything you like
*
* @param Varien_Event_Observer $observer
*/
public function saveProductTabData(Varien_Event_Observer $observer)
{
if (!self::$_singletonFlag) {
self::$_singletonFlag = true;
$product = $observer->getEvent()->getProduct();
try {
/**
* Perform any actions you want here
*
*/
$customFieldValue = $this->_getRequest()->getPost('custom_field');
/**
* Uncomment the line below to save the product
*
*/
//$product->save();
}
catch (Exception $e) {
Mage::getSingleton('adminhtml/session')->addError($e->getMessage());
}
}
}
/**
* Retrieve the product model
*
* @return Mage_Catalog_Model_Product $product
*/
public function getProduct()
{
return Mage::registry('product');
}
/**
* Shortcut to getRequest
*
*/
protected function _getRequest()
{
return Mage::app()->getRequest();
}
}
Although this code doesn't do anything with the data, it hopefully illustrates how to access the data, after which you can do what ever you want.
Wrapping it Up
The extension you have just written is a simple extension that will allow you to add custom tabs in the Product edit page of the Magento Admin. This concept can be applied to any of the other sections in Magento (categories, customers etc) so take the time to study the code and understand how it works. For a more practical implementation of this, take a look at the code in FishPig's WordPress Integration as this uses this system to add custom data grid's as AJAX tabs to the Product edit page of the Magento Admin.
Tuesday, July 2, 2013
Magento: Update Product Prices Globally
There are many ways to mass update product attributes in Magento, each well suited to a different purpose. Magento's built-in mass product attribute updater is great if you want to modify a selection of products or the new attribute value is the same for all products you want to edit. Alternatively, if you wish to alter the attributes in more dynamic ways, updating them programmatic ally via PHP is probably a better way. (The best way would be using our new Magento Price Changer extension!). The downside to both of these methods is speed, with each product update taking a few seconds to complete. While this time can be dramatically reduced by disabling indexing, the wait can still be too long for a store with a massive catalog. A more efficient way to update product attributes is to write direct SQL queries. As an example, I will show you how to mass update product pricing for all products, products from a certain store and products that use a certain attribute set.
Why would I want to mass update price?
When I was first asked to do this I asked myself the same question, however, the reason is quite simple. In Magento, shipping costs aren't usually displayed to the user until they enter their delivery address. While this makes sense, the customer usually enters their delivery address during the checkout process, meaning a lot of customers weren't aware of this extra cost. During a study of one site, I found that almost 30% of customers were leaving the store during checkout and that this bounce rate could almost definitely be attributes to the shipping cost. To remove this problem, it was decided I should add £6 (the shipping cost) on to every product price and offer free shipping instead. As soon as this was done a lot less people left the site during checkout!
How do I update product price globally?
In this first example, I will add £6 to every single product price.
<?php
$priceToAdd = 6;
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
UPDATE catalog_product_entity_decimal val
SET val.value = (val.value + $priceToAdd)
WHERE val.attribute_id = (
SELECT attribute_id FROM eav_attribute eav
WHERE eav.entity_type_id = 4
AND eav.attribute_code = 'price'
)
");
If you have a development site, add the code to a template file or run Magento's code in an external PHP file and all of your products should now cost £6 more.
How do I update all prices from a certain store?
This technique is useful when working in a multi-store Magento environment. The SQL query used is very similar, except you will need to add a clause in the WHERE section to limit the records updated by store ID.
<?php
$priceToAdd = 6;
$storeId = 4;
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
UPDATE catalog_product_entity_decimal val
SET val.value = (val.value + $priceToAdd)
WHERE val.attribute_id = (
SELECT attribute_id FROM eav_attribute eav
WHERE eav.entity_type_id = 4
AND eav.attribute_code = 'price'
)
AND val.store_id = $storeId
");
How do I update all product prices with a certain attribute set?
The concept behind this is the same, however you will need to join an extra table so that you can filter using attribute_set_id.
<?php
$priceToAdd = 6;
$attributeSetId = 4;
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
UPDATE catalog_product_entity_decimal val
SET val.value = (val.value + $priceToAdd)
WHERE val.attribute_id = (
SELECT attribute_id FROM eav_attribute eav
WHERE eav.entity_type_id = 4
AND eav.attribute_code = 'price'
)
AND entity_id = (
SELECT p.entity_id FROM catalog_product_entity p
WHERE p.attribute_set_id = $attributeSetId
)
");
How do I update the Special Price?
This one is also extremely easy! If you take any of the above examples and swap 'price' for 'special_price' they will all work! See below for an example of how to update the special price for every product.
<?php
$priceToAdd = 6;
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
UPDATE catalog_product_entity_decimal val
SET val.value = (val.value + $priceToAdd)
WHERE val.attribute_id = (
SELECT attribute_id FROM eav_attribute eav
WHERE eav.entity_type_id = 4
AND eav.attribute_code = 'special_price'
)
");
These features only scratch the surface of the Magento database but should hopefully give you an insight into the possibility of modifying data directly in the database. This method is much quicker than the alternatives, however can go drastically wrong extremely easily. I would make sure you test ALL queries on a development server and always back up your live server before running a query!
Why would I want to mass update price?
When I was first asked to do this I asked myself the same question, however, the reason is quite simple. In Magento, shipping costs aren't usually displayed to the user until they enter their delivery address. While this makes sense, the customer usually enters their delivery address during the checkout process, meaning a lot of customers weren't aware of this extra cost. During a study of one site, I found that almost 30% of customers were leaving the store during checkout and that this bounce rate could almost definitely be attributes to the shipping cost. To remove this problem, it was decided I should add £6 (the shipping cost) on to every product price and offer free shipping instead. As soon as this was done a lot less people left the site during checkout!
How do I update product price globally?
In this first example, I will add £6 to every single product price.
<?php
$priceToAdd = 6;
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
UPDATE catalog_product_entity_decimal val
SET val.value = (val.value + $priceToAdd)
WHERE val.attribute_id = (
SELECT attribute_id FROM eav_attribute eav
WHERE eav.entity_type_id = 4
AND eav.attribute_code = 'price'
)
");
If you have a development site, add the code to a template file or run Magento's code in an external PHP file and all of your products should now cost £6 more.
How do I update all prices from a certain store?
This technique is useful when working in a multi-store Magento environment. The SQL query used is very similar, except you will need to add a clause in the WHERE section to limit the records updated by store ID.
<?php
$priceToAdd = 6;
$storeId = 4;
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
UPDATE catalog_product_entity_decimal val
SET val.value = (val.value + $priceToAdd)
WHERE val.attribute_id = (
SELECT attribute_id FROM eav_attribute eav
WHERE eav.entity_type_id = 4
AND eav.attribute_code = 'price'
)
AND val.store_id = $storeId
");
How do I update all product prices with a certain attribute set?
The concept behind this is the same, however you will need to join an extra table so that you can filter using attribute_set_id.
<?php
$priceToAdd = 6;
$attributeSetId = 4;
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
UPDATE catalog_product_entity_decimal val
SET val.value = (val.value + $priceToAdd)
WHERE val.attribute_id = (
SELECT attribute_id FROM eav_attribute eav
WHERE eav.entity_type_id = 4
AND eav.attribute_code = 'price'
)
AND entity_id = (
SELECT p.entity_id FROM catalog_product_entity p
WHERE p.attribute_set_id = $attributeSetId
)
");
How do I update the Special Price?
This one is also extremely easy! If you take any of the above examples and swap 'price' for 'special_price' they will all work! See below for an example of how to update the special price for every product.
<?php
$priceToAdd = 6;
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$write->query("
UPDATE catalog_product_entity_decimal val
SET val.value = (val.value + $priceToAdd)
WHERE val.attribute_id = (
SELECT attribute_id FROM eav_attribute eav
WHERE eav.entity_type_id = 4
AND eav.attribute_code = 'special_price'
)
");
These features only scratch the surface of the Magento database but should hopefully give you an insight into the possibility of modifying data directly in the database. This method is much quicker than the alternatives, however can go drastically wrong extremely easily. I would make sure you test ALL queries on a development server and always back up your live server before running a query!
Direct SQL Queries In Magento
Magento's use of data models provide a great way to access and modify data. Using aptly named methods and clever abstraction, Varien hide away the complex SQL needed to perform data operations. While this makes learning models easier, it often impacts the speed of the operation and therefore the responsiveness of your site. This is especially true when saving models that use the EAV architecture. More often that not, this cannot be avoided, however there are some situations where executing direct SQL queries would be simpler and much quicker. An example of this is updating product prices globally in Magento. It would be easy enough to write some Magento code that looped through all products and modified the price. On a large data set, saving each individual product can take a long time and therefore make the system unusable. To combat this, it is possible to issue a direct SQL query which could update 1000's of products in 1 or 2 seconds.
Database Connections In Magento
By default, Magento will automatically connect to it's database and provide two separate resources which you can use to access data: core_read and core_write. As you can probably guess, core_read is for reading from the database while core_write is for writing to the database. It is important to ensure that you use the correct resource when reading or writing data to the database, especially when writing custom Magento extensions that will be released into the wild.
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
/**
* Retrieve the write connection
*/
$writeConnection = $resource->getConnection('core_write');
Table names and table prefixes
When installing Magento, you are given the option to use a table prefix. A table prefix is a string of characters that is added to the start of every table name in your database. These are useful if you are installing multiple system into 1 database as it helps to distinguish each application's data from another. Fortunately, Magento has a simple built in function which allows you to add the prefix to a given table name.
Get a table name from a string
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Get the table name
*/
$tableName = $resource->getTableName('catalog_product_entity');
/**
* if prefix was 'mage_' then the below statement
* would print out mage_catalog_product_entity
*/
echo $tableName;
Get a table name from an entity name
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Get the table name
*/
$tableName = $resource->getTableName('catalog/product');
/**
* if prefix was 'mage_' then the below statement
* would print out mage_catalog_product_entity
*/
echo $tableName;
Reading From The Database
While Magento models hide the complexity of the EAV system, they sometimes request far more data than is needed. If for example you have a product ID and want it's SKU, it would be much quicker to run a single query to obtain this value than to load in a whole product model (the inverse of this operation is available via the product resource class).
Varien_Db_Select::fetchAll
This method takes a query as it's parameter, executes it and then returns all of the results as an array. In the code example below, we use Varien_Db_Select::fetchAll to return all of the records in the catalog_product_entity table.
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
$query = 'SELECT * FROM ' . $resource->getTableName('catalog/product');
/**
* Execute the query and store the results in $results
*/
$results = $readConnection->fetchAll($query);
/**
* Print out the results
*/
var_dump($results);
Varien_Db_Select::fetchCol
This method is similar to fetchAll except that instead of returning all of the results, it returns the first column from each result row. In the code example below, we use Varien_Db_Select::fetchCol to retrieve all of the SKU's in our database in an array.
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
/**
* Retrieve our table name
*/
$table = $resource->getTableName('catalog/product');
/**
* Execute the query and store the results in $results
*/
$sku = $readConnection->fetchCol('SELECT sku FROM ' . $table . ');
/**
* Print out the results
*/
var_dump($results);
Try this code and look at the results. Notice how all of the SKU's are in a single array, rather than each row having it's own array? If you don't understand this, try changing fetchCol for fetchAll and compare the differences.
Varien_Db_Select::fetchOne
Unlike the previous two methods, Varien_Db_Select::fetchOne returns one value from the first row only. This value is returned on it's own and is not wrapped in an array. In the code example below, we take a product ID of 44 and return it's SKU.
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
/**
* Retrieve our table name
*/
$table = $resource->getTableName('catalog/product');
/**
* Set the product ID
*/
$productId = 44;
$query = 'SELECT sku FROM ' . $table . ' WHERE entity_id = '
. (int)$productId . ' LIMIT 1';
/**
* Execute the query and store the result in $sku
*/
$sku = $readConnection->fetchOne($query);
/**
* Print the SKU to the screen
*/
echo 'SKU: ' . $sku . '<br/>';
When trying out this example, ensure you change the product ID to an ID that exists in your database!
You may think that fetchOne works the same as fetchCol or fetchAll would if you only added 1 column to the SELECT query and added a 'LIMIT 1', however you would be wrong. The main difference with this function is that the value returned is the actual value, where as Varien_Db_Select::fetchCol and Varien_Db_Select::fetchAll would wrap the value in an array. To understand this a little, try swapping the method's and comparing the results.
Writing To The Database
When saving a Magento model, there can be a lot of background data being saved that you weren't even aware of. For example, saving a product model can take several seconds due to the amount of related data saves and indexing that needs to take place. This is okay if you need all the data saving, but if you only want to update the SKU of a product, this can be wasteful.
The example code below will show you how when given a product ID, you can alter the SKU. This is a trivial example but should illustrate how to execute write queries against your Magento database.
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the write connection
*/
$writeConnection = $resource->getConnection('core_write');
/**
* Retrieve our table name
*/
$table = $resource->getTableName('catalog/product');
/**
* Set the product ID
*/
$productId = 44;
/**
* Set the new SKU
* It is assumed that you are hard coding the new SKU in
* If the input is not dynamic, consider using the
* Varien_Db_Select object to insert data
*/
$newSku = 'new-sku';
$query = "UPDATE {$table} SET sku = '{$sku}' WHERE entity_id = "
. (int)$productId;
/**
* Execute the query
*/
$writeConnection->query($query);
To test this has worked, use the knowledge gained from the first part of this tutorial to write a query to extract the SKU that has just been changed.
Varien_Db_Select
The Varien_Db_Select, which has been touched on in this article is a far better option for extracting/wriiting information. Not only is it easy to use, it also provides a layered of security, which if used correctly, is impenetrable. More will be covered on Varien_Db_Select (aka Zend_Db_Select) in a future article.
Conclusion
Sometimes it is necessary to execute direct SQL queries in Magento, however, please be careful! The Magento model's are there for a reason and provide a layer of security which you will have to manually add to your own direct SQL queries. Be sure to escape any user input and when possible, stick to the Magento model methods! If you can't stick to the Magento models, consider using Varien_Db_Select; it won't stop you making errors but it will add an almost impenetrable layer of security to your database queries.
As a side note, if you're going to be querying the database directly, it would be a good idea to learn about
Database Connections In Magento
By default, Magento will automatically connect to it's database and provide two separate resources which you can use to access data: core_read and core_write. As you can probably guess, core_read is for reading from the database while core_write is for writing to the database. It is important to ensure that you use the correct resource when reading or writing data to the database, especially when writing custom Magento extensions that will be released into the wild.
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
/**
* Retrieve the write connection
*/
$writeConnection = $resource->getConnection('core_write');
Table names and table prefixes
When installing Magento, you are given the option to use a table prefix. A table prefix is a string of characters that is added to the start of every table name in your database. These are useful if you are installing multiple system into 1 database as it helps to distinguish each application's data from another. Fortunately, Magento has a simple built in function which allows you to add the prefix to a given table name.
Get a table name from a string
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Get the table name
*/
$tableName = $resource->getTableName('catalog_product_entity');
/**
* if prefix was 'mage_' then the below statement
* would print out mage_catalog_product_entity
*/
echo $tableName;
Get a table name from an entity name
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Get the table name
*/
$tableName = $resource->getTableName('catalog/product');
/**
* if prefix was 'mage_' then the below statement
* would print out mage_catalog_product_entity
*/
echo $tableName;
Reading From The Database
While Magento models hide the complexity of the EAV system, they sometimes request far more data than is needed. If for example you have a product ID and want it's SKU, it would be much quicker to run a single query to obtain this value than to load in a whole product model (the inverse of this operation is available via the product resource class).
Varien_Db_Select::fetchAll
This method takes a query as it's parameter, executes it and then returns all of the results as an array. In the code example below, we use Varien_Db_Select::fetchAll to return all of the records in the catalog_product_entity table.
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
$query = 'SELECT * FROM ' . $resource->getTableName('catalog/product');
/**
* Execute the query and store the results in $results
*/
$results = $readConnection->fetchAll($query);
/**
* Print out the results
*/
var_dump($results);
Varien_Db_Select::fetchCol
This method is similar to fetchAll except that instead of returning all of the results, it returns the first column from each result row. In the code example below, we use Varien_Db_Select::fetchCol to retrieve all of the SKU's in our database in an array.
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
/**
* Retrieve our table name
*/
$table = $resource->getTableName('catalog/product');
/**
* Execute the query and store the results in $results
*/
$sku = $readConnection->fetchCol('SELECT sku FROM ' . $table . ');
/**
* Print out the results
*/
var_dump($results);
Try this code and look at the results. Notice how all of the SKU's are in a single array, rather than each row having it's own array? If you don't understand this, try changing fetchCol for fetchAll and compare the differences.
Varien_Db_Select::fetchOne
Unlike the previous two methods, Varien_Db_Select::fetchOne returns one value from the first row only. This value is returned on it's own and is not wrapped in an array. In the code example below, we take a product ID of 44 and return it's SKU.
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection('core_read');
/**
* Retrieve our table name
*/
$table = $resource->getTableName('catalog/product');
/**
* Set the product ID
*/
$productId = 44;
$query = 'SELECT sku FROM ' . $table . ' WHERE entity_id = '
. (int)$productId . ' LIMIT 1';
/**
* Execute the query and store the result in $sku
*/
$sku = $readConnection->fetchOne($query);
/**
* Print the SKU to the screen
*/
echo 'SKU: ' . $sku . '<br/>';
When trying out this example, ensure you change the product ID to an ID that exists in your database!
You may think that fetchOne works the same as fetchCol or fetchAll would if you only added 1 column to the SELECT query and added a 'LIMIT 1', however you would be wrong. The main difference with this function is that the value returned is the actual value, where as Varien_Db_Select::fetchCol and Varien_Db_Select::fetchAll would wrap the value in an array. To understand this a little, try swapping the method's and comparing the results.
Writing To The Database
When saving a Magento model, there can be a lot of background data being saved that you weren't even aware of. For example, saving a product model can take several seconds due to the amount of related data saves and indexing that needs to take place. This is okay if you need all the data saving, but if you only want to update the SKU of a product, this can be wasteful.
The example code below will show you how when given a product ID, you can alter the SKU. This is a trivial example but should illustrate how to execute write queries against your Magento database.
<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton('core/resource');
/**
* Retrieve the write connection
*/
$writeConnection = $resource->getConnection('core_write');
/**
* Retrieve our table name
*/
$table = $resource->getTableName('catalog/product');
/**
* Set the product ID
*/
$productId = 44;
/**
* Set the new SKU
* It is assumed that you are hard coding the new SKU in
* If the input is not dynamic, consider using the
* Varien_Db_Select object to insert data
*/
$newSku = 'new-sku';
$query = "UPDATE {$table} SET sku = '{$sku}' WHERE entity_id = "
. (int)$productId;
/**
* Execute the query
*/
$writeConnection->query($query);
To test this has worked, use the knowledge gained from the first part of this tutorial to write a query to extract the SKU that has just been changed.
Varien_Db_Select
The Varien_Db_Select, which has been touched on in this article is a far better option for extracting/wriiting information. Not only is it easy to use, it also provides a layered of security, which if used correctly, is impenetrable. More will be covered on Varien_Db_Select (aka Zend_Db_Select) in a future article.
Conclusion
Sometimes it is necessary to execute direct SQL queries in Magento, however, please be careful! The Magento model's are there for a reason and provide a layer of security which you will have to manually add to your own direct SQL queries. Be sure to escape any user input and when possible, stick to the Magento model methods! If you can't stick to the Magento models, consider using Varien_Db_Select; it won't stop you making errors but it will add an almost impenetrable layer of security to your database queries.
As a side note, if you're going to be querying the database directly, it would be a good idea to learn about
How to enable Maintenance Mode – Magento
There’s two ways, that I know of, to enable Maintenance Mode in Magento.
Maintenance.flag
Since the version 1.4 you can activate the Maintenance Mode by just creating a file called maintenance.flag in your root directory.
Downside: it gets activated for everyone, including you with the admin sesion.
Don’t panic! There’s a workaround, here it is:
1. Open your index.php file in the root folder.
2. Look for this line (should be around the 57th line):
if (file_exists($maintenanceFile)) {
3. Replace it with this one:
if (file_exists($maintenanceFile) && !in_array($ip, $allowed)) {
4. Paste the following code above the line you just changed:
$ip = $_SERVER['REMOTE_ADDR'];
$allowed = array('0.0.0.0','1.1.1.1');
5. Change the IP with your own in the $allowed array you just pasted.
What more?
You can edit the 503 page by just going to: /errors/default/503.php
Maintenance.flag
Since the version 1.4 you can activate the Maintenance Mode by just creating a file called maintenance.flag in your root directory.
Downside: it gets activated for everyone, including you with the admin sesion.
Don’t panic! There’s a workaround, here it is:
1. Open your index.php file in the root folder.
2. Look for this line (should be around the 57th line):
if (file_exists($maintenanceFile)) {
3. Replace it with this one:
if (file_exists($maintenanceFile) && !in_array($ip, $allowed)) {
4. Paste the following code above the line you just changed:
$ip = $_SERVER['REMOTE_ADDR'];
$allowed = array('0.0.0.0','1.1.1.1');
5. Change the IP with your own in the $allowed array you just pasted.
What more?
You can edit the 503 page by just going to: /errors/default/503.php
Get Store and Admin values in your template – Magento
The post title might be a little confusing, let me enlighten you ;)
Have you ever wanted to print a value from your Magento Admin?
Have you ever needed to retrieve a value from a custom Module?
You can easily do that with the following core function:
<?php echo Mage::getStoreConfig('general/store_information/name'); ?>
As you can see, this getStoreConfig accepts a parameter. This parameter is the location of the value you’re trying to retrieve.
In the example above we’re printing:
System > Configuration > General > Store Information > Name
Have you ever wanted to print a value from your Magento Admin?
Have you ever needed to retrieve a value from a custom Module?
You can easily do that with the following core function:
<?php echo Mage::getStoreConfig('general/store_information/name'); ?>
As you can see, this getStoreConfig accepts a parameter. This parameter is the location of the value you’re trying to retrieve.
In the example above we’re printing:
System > Configuration > General > Store Information > Name
How to set a Store View via .htaccess Magento
.htaccess
You can set the Store View code adding the following line to your .htaccess
In this example my Store View code is “french”:
SetEnv MAGE_RUN_CODE french
Hint: you can see your Store Views codes through your Admin > System > Manage Stores
How does it work?
In your index.php file in your root directory you will find the following lines (probably at the end of the file).
As you can see it uses the $_SERVER['MAGE_RUN_CODE'] if it has been set.
/* Store or website code */
$mageRunCode = isset($_SERVER['MAGE_RUN_CODE']) ? $_SERVER['MAGE_RUN_CODE'] : '';
/* Run store or run website */
$mageRunType = isset($_SERVER['MAGE_RUN_TYPE']) ? $_SERVER['MAGE_RUN_TYPE'] : 'store';
You can set the Store View code adding the following line to your .htaccess
In this example my Store View code is “french”:
SetEnv MAGE_RUN_CODE french
Hint: you can see your Store Views codes through your Admin > System > Manage Stores
How does it work?
In your index.php file in your root directory you will find the following lines (probably at the end of the file).
As you can see it uses the $_SERVER['MAGE_RUN_CODE'] if it has been set.
/* Store or website code */
$mageRunCode = isset($_SERVER['MAGE_RUN_CODE']) ? $_SERVER['MAGE_RUN_CODE'] : '';
/* Run store or run website */
$mageRunType = isset($_SERVER['MAGE_RUN_TYPE']) ? $_SERVER['MAGE_RUN_TYPE'] : 'store';
Magento - Customer Profile Photo upload
To upload profile photo for customer in magento we need to follow few steps as below.
Add a new field for profile photo(How to create new fields for customer - Check this link it will helps you).
The above link helps you to add a new filed in DB and you need to upload that photo manually the below code will helps you to upload photos in magento.
if(isset($_FILES['logo']['name']) and (file_exists($_FILES['logo']['tmp_name'])))
{
try {
$uploader = new Varien_File_Uploader('logo');
$uploader->setAllowedExtensions(array('jpg','jpeg','gif','png'));
$uploader->setAllowRenameFiles(false);
$uploader->setFilesDispersion(false);
$path = Mage::getBaseDir('media') . DS .'catalog/customer/logo/';
$newName = time() . $_FILES['logo']['name'];
$uploader->save($path, $newName);
$customer->setLogo($newName);
// actual path of image
$imageUrl = $path . $newName;
// path of the resized image to be saved
// here, the resized image is saved in media/resized folder
$imageResized = $path . $newName;
// resize image only if the image file exists and the resized image file doesn't exist
// the image is resized proportionally with the width/height 135px
if (!file_exists($imageResized)&&file_exists($imageUrl)) :
$imageObj = new Varien_Image($imageUrl);
$imageObj->constrainOnly(TRUE);
$imageObj->keepAspectRatio(TRUE);
$imageObj->keepFrame(FALSE);
$imageObj->resize(150, 150);
$imageObj->save($imageResized);
endif;
}catch(Exception $e) {
}
}
After upload we need to save file name in DB.
$customer->setLogo($newName);
Add a new field for profile photo(How to create new fields for customer - Check this link it will helps you).
The above link helps you to add a new filed in DB and you need to upload that photo manually the below code will helps you to upload photos in magento.
if(isset($_FILES['logo']['name']) and (file_exists($_FILES['logo']['tmp_name'])))
{
try {
$uploader = new Varien_File_Uploader('logo');
$uploader->setAllowedExtensions(array('jpg','jpeg','gif','png'));
$uploader->setAllowRenameFiles(false);
$uploader->setFilesDispersion(false);
$path = Mage::getBaseDir('media') . DS .'catalog/customer/logo/';
$newName = time() . $_FILES['logo']['name'];
$uploader->save($path, $newName);
$customer->setLogo($newName);
// actual path of image
$imageUrl = $path . $newName;
// path of the resized image to be saved
// here, the resized image is saved in media/resized folder
$imageResized = $path . $newName;
// resize image only if the image file exists and the resized image file doesn't exist
// the image is resized proportionally with the width/height 135px
if (!file_exists($imageResized)&&file_exists($imageUrl)) :
$imageObj = new Varien_Image($imageUrl);
$imageObj->constrainOnly(TRUE);
$imageObj->keepAspectRatio(TRUE);
$imageObj->keepFrame(FALSE);
$imageObj->resize(150, 150);
$imageObj->save($imageResized);
endif;
}catch(Exception $e) {
}
}
After upload we need to save file name in DB.
$customer->setLogo($newName);
How to create new fields for customer
I don't know what you tried so I'm just going to list all the steps needed to add a new schooL customer attribute to the Magento 1.6.1 registration form.
Create a module preferably, or place similiar code to this in some .phtml file and run it once. If you're doing this proper and creating a module, put code like this into the mysql_install file:
<?php
$installer = $this;
$installer->startSetup();
$setup = Mage::getModel('customer/entity_setup', 'core_setup');
$setup->addAttribute('customer', 'school', array(
'type' => 'int',
'input' => 'select',
'label' => 'School',
'global' => 1,
'visible' => 1,
'required' => 0,
'user_defined' => 1,
'default' => '0',
'visible_on_front' => 1,
'source'=> 'profile/entity_school',
));
if (version_compare(Mage::getVersion(), '1.6.0', '<='))
{
$customer = Mage::getModel('customer/customer');
$attrSetId = $customer->getResource()->getEntityType()->getDefaultAttributeSetId();
$setup->addAttributeToSet('customer', $attrSetId, 'General', 'school');
}
if (version_compare(Mage::getVersion(), '1.4.2', '>='))
{
Mage::getSingleton('eav/config')
->getAttribute('customer', 'school')
->setData('used_in_forms', array('adminhtml_customer','customer_account_create','customer_account_edit','checkout_register'))
->save();
}
$installer->endSetup();
?>
In your module config.xml file. Note that the name of my module is Excellence_Profile.
<profile_setup> <!-- Replace with your module name -->
<setup>
<module>Excellence_Profile</module> <!-- Replace with your module name -->
<class>Mage_Customer_Model_Entity_Setup</class>
</setup>
</profile_setup>
Here we will add our attribute, to the customer registration form. In version 1.6.0(+) the phtml file used is persistance/customer/register.phtml and in version 1.6.0(-) the phtml file used is customer/form/register.phtml So we need to open the phtml file, based on magento version and add this code in the tag.
<li>
<?php
$attribute = Mage::getModel('eav/config')->getAttribute('customer','school');
?>
<label for="school" class="<?php if($attribute->getIsRequired() == true){?>required<?php } ?>"><?php if($attribute->getIsRequired() == true){?><em>*</em><?php } ?><?php echo $this->__('School') ?></label>
<div class="input-box">
<select name="school" id="school" class="<?php if($attribute->getIsRequired() == true){?>required-entry<?php } ?>">
<?php
$options = $attribute->getSource()->getAllOptions();
foreach($options as $option){
?>
<option value='<?php echo $option['value']?>' <?php if($this->getFormData()->getSchool() == $option['value']){ echo 'selected="selected"';}?>><?php echo $this->__($option['label'])?></option>
<?php } ?>
</select>
</div>
</li>
For magento 1.4.2(+) that is all that is required for the registration step. If you create a user from here, you should see the school text field in admin. For magento 1.4.1(-), we need to do another thing open the your modules config.xml file and add:
<global>
<fieldsets>
<customer_account>
<school><create>1</create><update>1</update><name>1</name></school>
</customer_account>
</fieldsets>
</global>
Once, user has created his account in the MyAccount->Account Information section he should be able to edit the school field as well. For this open the phtml file customer/form/edit.phtml and put in the code in the :
<?php
<li>
<?php
$attribute = Mage::getModel('eav/config')->getAttribute('customer','school');
?>
<label for="is_active" class="<?php if($attribute->getIsRequired() == true){?>required<?php } ?>"><?php if($attribute->getIsRequired() == true){?><em>*</em><?php } ?><?php echo $this->__('School') ?></label>
<div class="input-box">
<select name="school" id="school" class="<?php if($attribute->getIsRequired() == true){?>required-entry<?php } ?>">
<?php
$options = $attribute->getSource()->getAllOptions();
foreach($options as $option){
?>
<option value='<?php echo $option['value']?>' <?php if($this->getCustomer()->getSchool() == $option['value']){ echo 'selected="selected"';}?>><?php echo $this->__($option['label'])?></option>
<?php } ?>
</select>
</div>
</li>
A registration form also shows up at the checkout page in magento. To add you field here, you need to edit checkout/onepage/billing.phtml for magento version 1.6(-) and persistant/checkout/onepage/billing.phtml for magento version 1.6(+) file and then find the code:
<?php if(!$this->isCustomerLoggedIn()): ?>
inside this if condition add your field
<li>
<li>
<?php
$attribute = Mage::getModel('eav/config')->getAttribute('customer','school');
?>
<label for="school" class="<?php if($attribute->getIsRequired() == true){?>required<?php } ?>"><?php if($attribute->getIsRequired() == true){?><em>*</em><?php } ?><?php echo $this->__('School') ?></label>
<div class="input-box">
<select name="billing[school]" id="school" class="<?php if($attribute->getIsRequired() == true){?>required-entry<?php } ?>">
<?php
$options = $attribute->getSource()->getAllOptions();
foreach($options as $option){
?>
<option value='<?php echo $option['value']?>'><?php echo $this->__($option['label'])?></option>
<?php } ?>
</select>
</div>
</li>
Next open your module config.xml or any other config.xml file, add the following lines:
<global>
<fieldsets>
<checkout_onepage_quote>
<customer_school>
<to_customer>school</to_customer>
</customer_school>
</checkout_onepage_quote>
<customer_account>
<school>
<to_quote>customer_school</to_quote>
</school>
</customer_account>
</fieldsets>
</global>
Next we need to make some changes in the quote table i.e sales_flat_quote table in magento. If you have a module then create an upgrade version of your sql file and put in this code:
$tablequote = $this->getTable('sales/quote');
$installer->run("
ALTER TABLE $tablequote ADD `customer_school` INT NOT NULL
");
After doing this make sure to clear you magento cache, specifically “Flush Magento Cache” and “Flush Cache Storage”. Now when you place order, the customer is created with the correct school attribute.
Create a module preferably, or place similiar code to this in some .phtml file and run it once. If you're doing this proper and creating a module, put code like this into the mysql_install file:
<?php
$installer = $this;
$installer->startSetup();
$setup = Mage::getModel('customer/entity_setup', 'core_setup');
$setup->addAttribute('customer', 'school', array(
'type' => 'int',
'input' => 'select',
'label' => 'School',
'global' => 1,
'visible' => 1,
'required' => 0,
'user_defined' => 1,
'default' => '0',
'visible_on_front' => 1,
'source'=> 'profile/entity_school',
));
if (version_compare(Mage::getVersion(), '1.6.0', '<='))
{
$customer = Mage::getModel('customer/customer');
$attrSetId = $customer->getResource()->getEntityType()->getDefaultAttributeSetId();
$setup->addAttributeToSet('customer', $attrSetId, 'General', 'school');
}
if (version_compare(Mage::getVersion(), '1.4.2', '>='))
{
Mage::getSingleton('eav/config')
->getAttribute('customer', 'school')
->setData('used_in_forms', array('adminhtml_customer','customer_account_create','customer_account_edit','checkout_register'))
->save();
}
$installer->endSetup();
?>
In your module config.xml file. Note that the name of my module is Excellence_Profile.
<profile_setup> <!-- Replace with your module name -->
<setup>
<module>Excellence_Profile</module> <!-- Replace with your module name -->
<class>Mage_Customer_Model_Entity_Setup</class>
</setup>
</profile_setup>
Here we will add our attribute, to the customer registration form. In version 1.6.0(+) the phtml file used is persistance/customer/register.phtml and in version 1.6.0(-) the phtml file used is customer/form/register.phtml So we need to open the phtml file, based on magento version and add this code in the tag.
<li>
<?php
$attribute = Mage::getModel('eav/config')->getAttribute('customer','school');
?>
<label for="school" class="<?php if($attribute->getIsRequired() == true){?>required<?php } ?>"><?php if($attribute->getIsRequired() == true){?><em>*</em><?php } ?><?php echo $this->__('School') ?></label>
<div class="input-box">
<select name="school" id="school" class="<?php if($attribute->getIsRequired() == true){?>required-entry<?php } ?>">
<?php
$options = $attribute->getSource()->getAllOptions();
foreach($options as $option){
?>
<option value='<?php echo $option['value']?>' <?php if($this->getFormData()->getSchool() == $option['value']){ echo 'selected="selected"';}?>><?php echo $this->__($option['label'])?></option>
<?php } ?>
</select>
</div>
</li>
For magento 1.4.2(+) that is all that is required for the registration step. If you create a user from here, you should see the school text field in admin. For magento 1.4.1(-), we need to do another thing open the your modules config.xml file and add:
<global>
<fieldsets>
<customer_account>
<school><create>1</create><update>1</update><name>1</name></school>
</customer_account>
</fieldsets>
</global>
Once, user has created his account in the MyAccount->Account Information section he should be able to edit the school field as well. For this open the phtml file customer/form/edit.phtml and put in the code in the :
<?php
<li>
<?php
$attribute = Mage::getModel('eav/config')->getAttribute('customer','school');
?>
<label for="is_active" class="<?php if($attribute->getIsRequired() == true){?>required<?php } ?>"><?php if($attribute->getIsRequired() == true){?><em>*</em><?php } ?><?php echo $this->__('School') ?></label>
<div class="input-box">
<select name="school" id="school" class="<?php if($attribute->getIsRequired() == true){?>required-entry<?php } ?>">
<?php
$options = $attribute->getSource()->getAllOptions();
foreach($options as $option){
?>
<option value='<?php echo $option['value']?>' <?php if($this->getCustomer()->getSchool() == $option['value']){ echo 'selected="selected"';}?>><?php echo $this->__($option['label'])?></option>
<?php } ?>
</select>
</div>
</li>
A registration form also shows up at the checkout page in magento. To add you field here, you need to edit checkout/onepage/billing.phtml for magento version 1.6(-) and persistant/checkout/onepage/billing.phtml for magento version 1.6(+) file and then find the code:
<?php if(!$this->isCustomerLoggedIn()): ?>
inside this if condition add your field
<li>
<li>
<?php
$attribute = Mage::getModel('eav/config')->getAttribute('customer','school');
?>
<label for="school" class="<?php if($attribute->getIsRequired() == true){?>required<?php } ?>"><?php if($attribute->getIsRequired() == true){?><em>*</em><?php } ?><?php echo $this->__('School') ?></label>
<div class="input-box">
<select name="billing[school]" id="school" class="<?php if($attribute->getIsRequired() == true){?>required-entry<?php } ?>">
<?php
$options = $attribute->getSource()->getAllOptions();
foreach($options as $option){
?>
<option value='<?php echo $option['value']?>'><?php echo $this->__($option['label'])?></option>
<?php } ?>
</select>
</div>
</li>
Next open your module config.xml or any other config.xml file, add the following lines:
<global>
<fieldsets>
<checkout_onepage_quote>
<customer_school>
<to_customer>school</to_customer>
</customer_school>
</checkout_onepage_quote>
<customer_account>
<school>
<to_quote>customer_school</to_quote>
</school>
</customer_account>
</fieldsets>
</global>
Next we need to make some changes in the quote table i.e sales_flat_quote table in magento. If you have a module then create an upgrade version of your sql file and put in this code:
$tablequote = $this->getTable('sales/quote');
$installer->run("
ALTER TABLE $tablequote ADD `customer_school` INT NOT NULL
");
After doing this make sure to clear you magento cache, specifically “Flush Magento Cache” and “Flush Cache Storage”. Now when you place order, the customer is created with the correct school attribute.
Monday, July 1, 2013
Magento – Add custom comment box to each product in Cart
Are you looking for the solution i.e. customer can provide their inputs or comments along with the products they are going to order. To make it easier, one way is to allow them enter the comments for each individual item they order.
On the other hand, admin should be able to view the comment on the order page.
Adding a custom comment box for each item in the cart is actually very easy. First lets add the textarea field for each item.
In your theme, for the file: template/checkout/cart.phtml
Add the new heading along with other heading for cart items.
<th><?php echo $this->__('Comments') ?></th>
In the file: template/checkout/cart/item/default.phtml
Add a new column
<td class="a-center">
<textarea name="cart[<?php echo $_item->getId() ?>][comments]" rows="3" cols="20"><?php echo $_item->getItemcomment() ?></textarea>
</td>
For Older version of Magento it would be:
<td class="a-center">
<textarea name="cart[<?php echo $_item->getId() ?>][comments]" rows="3" cols="20"><?php echo $this->getItemItemcomment($_item) ?></textarea>
</td>
Doing upto this. shoul show the text area added
The next step is to save the comment in DB, when customer update the cart.
So add a new field ‘itemcomment’ in the tabel ‘sales_flat_quote_item’. (For older version of Magento the table would be ‘sales_quote_item’)
Now we are going to add the code which will do the DB operation. For this we will need to modify the file:
app/code/core/Mage/Checkout/Model/Cart.php (Note: If you are planning to upgrade your Magento setup, copy this file to local & modify.)
Here we need to add some code to the function updateItems(), such a way that the function should now look like below:
public function updateItems($data)
{
Mage::dispatchEvent('checkout_cart_update_items_before', array('cart'=>$this, 'info'=>$data));
foreach ($data as $itemId => $itemInfo) {
$item = $this->getQuote()->getItemById($itemId);
if (!$item) {
continue;
}
if (!empty($itemInfo['remove']) || (isset($itemInfo['qty']) && $itemInfo['qty']=='0')) {
$this->removeItem($itemId);
continue;
}
$qty = isset($itemInfo['qty']) ? (float) $itemInfo['qty'] : false;
if ($qty > 0) {
$item->setQty($qty);
}
/* Start: Custom code added for comments */
if(!empty($itemInfo['comments'])) {
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
# make the frame_queue active
$query = "UPDATE `sales_flat_quote_item` SET itemcomment = '".$itemInfo['comments']."' where item_id = $itemId";
$write->query($query);
$item->setItemcomment($itemInfo['comments']);
}
/* End: Custom code added for comments */
}
Mage::dispatchEvent('checkout_cart_update_items_after', array('cart'=>$this, 'info'=>$data));
return $this;
}
Showing the comment in Admin -> View Order
Add a new function getItemcomment() to the file below:
app/code/core/Mage/Adminhtml/Block/Sales/Order/View/Items.php
If you are on verstion 1.5 or later.. add it to the file below.
app/code/core/Mage/Adminhtml/Block/Sales/Order/View/Items.php
public function getItemcomment($item) {
$itemId = $item->getId();
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$query = "SELECT q.* FROM `sales_flat_order_item` o
LEFT JOIN `sales_flat_quote_item` q on o.quote_item_id = q.item_id
WHERE o.item_id = $itemId";
# For older versions of Magento
/* $query = "SELECT q.* FROM `sales_order_entity_int` o
LEFT JOIN `sales_flat_quote_item` q on o.value = q.entity_id
WHERE o.entity_id = $itemId AND o.attribute_id = 343"; */
$res = $write->query($query);
while ($row = $res->fetch() ) {
if(key_exists('itemcomment',$row)) {
echo nl2br($row['itemcomment']);
}
}
}
To add the comments column to the items edit the .phtml file below:
app/design/adminhtml/default/default/template/sales/order/view/items.phtml
Adding header for items to make it look like below:
<tr class="headings">
<th><?php echo $this->helper('sales')->__('Product') ?></th>
<th><?php echo $this->helper('sales')->__('Comments') ?></th>
<th><?php echo $this->helper('sales')->__('Item Status') ?></th>
Adding Column with comments. app/design/adminhtml/default/default/template/sales/order/view/items/renderer/default.phtml
Add a column for item comments juts before status columns to make it look a like below.
<td><?php echo $this->getItemcomment($_item) ?></td> <!-- New column added for item comments -->
<td class="a-center"><?php echo $_item->getStatus() ?></td>
Doing upto this will show the comments column in the item table.
On the other hand, admin should be able to view the comment on the order page.
Adding a custom comment box for each item in the cart is actually very easy. First lets add the textarea field for each item.
In your theme, for the file: template/checkout/cart.phtml
Add the new heading along with other heading for cart items.
<th><?php echo $this->__('Comments') ?></th>
In the file: template/checkout/cart/item/default.phtml
Add a new column
<td class="a-center">
<textarea name="cart[<?php echo $_item->getId() ?>][comments]" rows="3" cols="20"><?php echo $_item->getItemcomment() ?></textarea>
</td>
For Older version of Magento it would be:
<td class="a-center">
<textarea name="cart[<?php echo $_item->getId() ?>][comments]" rows="3" cols="20"><?php echo $this->getItemItemcomment($_item) ?></textarea>
</td>
Doing upto this. shoul show the text area added
The next step is to save the comment in DB, when customer update the cart.
So add a new field ‘itemcomment’ in the tabel ‘sales_flat_quote_item’. (For older version of Magento the table would be ‘sales_quote_item’)
Now we are going to add the code which will do the DB operation. For this we will need to modify the file:
app/code/core/Mage/Checkout/Model/Cart.php (Note: If you are planning to upgrade your Magento setup, copy this file to local & modify.)
Here we need to add some code to the function updateItems(), such a way that the function should now look like below:
public function updateItems($data)
{
Mage::dispatchEvent('checkout_cart_update_items_before', array('cart'=>$this, 'info'=>$data));
foreach ($data as $itemId => $itemInfo) {
$item = $this->getQuote()->getItemById($itemId);
if (!$item) {
continue;
}
if (!empty($itemInfo['remove']) || (isset($itemInfo['qty']) && $itemInfo['qty']=='0')) {
$this->removeItem($itemId);
continue;
}
$qty = isset($itemInfo['qty']) ? (float) $itemInfo['qty'] : false;
if ($qty > 0) {
$item->setQty($qty);
}
/* Start: Custom code added for comments */
if(!empty($itemInfo['comments'])) {
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
# make the frame_queue active
$query = "UPDATE `sales_flat_quote_item` SET itemcomment = '".$itemInfo['comments']."' where item_id = $itemId";
$write->query($query);
$item->setItemcomment($itemInfo['comments']);
}
/* End: Custom code added for comments */
}
Mage::dispatchEvent('checkout_cart_update_items_after', array('cart'=>$this, 'info'=>$data));
return $this;
}
Showing the comment in Admin -> View Order
Add a new function getItemcomment() to the file below:
app/code/core/Mage/Adminhtml/Block/Sales/Order/View/Items.php
If you are on verstion 1.5 or later.. add it to the file below.
app/code/core/Mage/Adminhtml/Block/Sales/Order/View/Items.php
public function getItemcomment($item) {
$itemId = $item->getId();
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$query = "SELECT q.* FROM `sales_flat_order_item` o
LEFT JOIN `sales_flat_quote_item` q on o.quote_item_id = q.item_id
WHERE o.item_id = $itemId";
# For older versions of Magento
/* $query = "SELECT q.* FROM `sales_order_entity_int` o
LEFT JOIN `sales_flat_quote_item` q on o.value = q.entity_id
WHERE o.entity_id = $itemId AND o.attribute_id = 343"; */
$res = $write->query($query);
while ($row = $res->fetch() ) {
if(key_exists('itemcomment',$row)) {
echo nl2br($row['itemcomment']);
}
}
}
To add the comments column to the items edit the .phtml file below:
app/design/adminhtml/default/default/template/sales/order/view/items.phtml
Adding header for items to make it look like below:
<tr class="headings">
<th><?php echo $this->helper('sales')->__('Product') ?></th>
<th><?php echo $this->helper('sales')->__('Comments') ?></th>
<th><?php echo $this->helper('sales')->__('Item Status') ?></th>
Adding Column with comments. app/design/adminhtml/default/default/template/sales/order/view/items/renderer/default.phtml
Add a column for item comments juts before status columns to make it look a like below.
<td><?php echo $this->getItemcomment($_item) ?></td> <!-- New column added for item comments -->
<td class="a-center"><?php echo $_item->getStatus() ?></td>
Doing upto this will show the comments column in the item table.
Custom Add to Cart button on CMS page
Magento provides an easy way of adding products to shopping cart via query string. To create a simple add to cart button on any magento’s CMS page open it in WYSIWYG editor and the following HTML to its contents:
<p><button onclick=”location.href =’{{config path=”web/unsecure/base_url”}}/checkout/cart/add?product=1&qty=1′”>Buy It Now</button></p>
Save the page and refresh the cache. Now if you open the page in you should be able to see “Buy It Now” button. When clicked it adds 1 product with ID=1 to your shopping cart.
To change the qty of the product to add set qty query string parameter value to desired quantity. Here’s an html sample for adding 5 products at once:
<p><button onclick=”location.href =’{{config path=”web/unsecure/base_url”}}/checkout/cart/add?product=1&qty=5′”>Buy 4 Get 1 Free</button></p>
<p><button onclick=”location.href =’{{config path=”web/unsecure/base_url”}}/checkout/cart/add?product=1&qty=1′”>Buy It Now</button></p>
Save the page and refresh the cache. Now if you open the page in you should be able to see “Buy It Now” button. When clicked it adds 1 product with ID=1 to your shopping cart.
To change the qty of the product to add set qty query string parameter value to desired quantity. Here’s an html sample for adding 5 products at once:
<p><button onclick=”location.href =’{{config path=”web/unsecure/base_url”}}/checkout/cart/add?product=1&qty=5′”>Buy 4 Get 1 Free</button></p>
Subscribe to:
Posts (Atom)