Just another WordPress.com weblog

Archive for April, 2010

How to write simple Query in magento ?

Retrieving database information in Magento can be a bit complicated. Technically it is possible to write raw SQL statements to load entities and all their associated attribute values. However due to the EAV database design it is difficult to write query statements to get exactly what you want. Utilizing an EAV modeling pattern allows for unlimited attributes on any product, category, customer, or order, but EAV also depletes a programmer’s ability to write queries against the data.

SQL query example:

Lets look at an example of an SQL statement which gets all the products (product_id) in a particular category:

$sql = “SELECT product_id FROM catalog_category_product WHERE category_id=3″;
$data = Mage::getSingleton(‘core/resource’) ->getConnection(‘core_read’)->fetchAll($sql);

You will get the product ids as an array in $data. However if we wanted to get all the attributes of a product based on the product_id then it is difficult as all the product info is not present in one table. Due to the EAV design it is loosely present in many tables. So we require to join a number of tables to get the product information. In such cases we should try using Magento’s built-in query methods instead of directly running queries on the database. More on built in query methods later.

Analyzing the code:

Lets analyze the code:

$data = Mage::getSingleton(‘core/resource’);

First of all we require a connection to the database to perform queries. This is done by using resources. The role of a ‘resource’ in Magento is to manage database connections. So using getSingleton(‘core/resource’) we get an instance of Mage_Core_Model_Resource.

Check out that file and you will see a function called getConnection($name). This is used to get a ‘read’ or ‘write’ connection (‘core_read’ – read connection, ‘core_write’ – write connection).

The fetchAll function executes the query and returns all the rows to $data. The results are returned as an array of results (array of arrays).

How do i know the table names ?

You can see Magento’s database design by checking this link http://www.magentocommerce.com/wiki/development/magento_database_diagram.

The core ResourceModel has a method called getTable() to get the table name of any model in the system. Table names do not have to follow the name of the model, an end-user can change the table names by changing an XML setting.Therefore, it is best to use the getTable method of the core resource.

$res = Mage::getSingleton(’core/resource’)->getConnection(’core_read’);
$tableName = $res->getTable(’catalog/product’);

This will give $tableName as ’catalog_product_entity’. This happens because we have the following XML configuration in the catalog module’s config file.





ResourceModels manage the different read/write connections and automatically figure out table names based on convention and perform the database related operations for a particular Model. ie. It acts as an abstraction to retrieve (or write) data from database. For eg: in the Mage_Catalog_Model_Product class you can see that to get data it uses the getResource() function. This function gets the ResourceModel to perform the required database operation.

For the Catalog Module the ResourceModels can be found in Catalog\Model\Resource\Eav\Mysql4 directory. (this is defined in the config.xml – search for ). For getting ResourceModels we use the getResourceModel() method.

Mage::getResourceModel(‘catalog/product’) will create an instance of Mage_Catalog_Model_Resource_Eav_Mysql4_Product.php. (If class names have the term Mysql4 in their names, they are generally called ResourceModels. If the word Entity appears in the class name, then the resource is an EAV Entity). You will get the same result by calling Mage::getModel(‘catalog/resource_eav_mysql4_product’) also.

Now take a look at this Product.php file. You will see some sql statements like the one below:

public function getIdBySku($sku)
{return $this->_read->fetchOne(’select entity_id from ‘.$this->getEntityTable().’ where sku=?’, $sku);}

So if we need to get the product id for a sku value then we can simply do it by:

$id = Mage::getResourceModel(‘catalog/product’)->getIdBysku(5);

Its easier to use predefined functions than write your own query statements. Similiarly there are other funtions predefined in the Resource Models that you can use to retrieve data from database.


Entities extend Magento’s resource objects. Basically, Entities pair up to selected Models and help them save to the database. Entities behave mostly like resource models, as they are just a special sub-class of resources. Entities for catalog module are in Mage\Catalog\Model\Entity.


Entities are designed to load one item, or record, at a time. But, most of the time when we think of database queries we want to write a SELECT statement that gives use a result with multiple rows. The entity models are not able to do that.

What if we want to select all records from the database matching some criteria. Normally, a simple SELECT statement with a WHERE clause would work. But, things are not that simple because of the EAV design. Not all of the data that makes up an entity lives in one table, so we need to JOIN more tables. To properly construct a WHERE clause we would have to know exactly which tables our specific data is stored in. This is the problem that collections solve. Loading an arbitrary number of records based on criteria is the job of entity collection.

Probably the most useful method of a collection is the addAttributeToFilter method. This method takes an attribute code and a condition.

$products = Mage::getModel(’catalog/product’)->getCollection();
$products->addAttributeToFilter(’sku’, ’9999’);

In the above example, the condition is a simple string, but the condition can also be an array. When passing a condition array, the key of the array designates the type of comparison. The type can be eq, for equals, like for like comparisons, gt for a greater than comparison. Here is the same example above, but searching for an array of product IDs.

$products = Mage::getModel(’catalog/product’)->getCollection();
$products->addAttributeToFilter(’entity_id’, array(’in’=> array(1,2,36,35) ));

//runs the query
SELECT * FROM ‘catalog_product_entity‘ WHERE (e.entity_id in (1, 2, 36, 35))


For loading selected attribute values , we can use the addAttributeToSelect method.

This is only scratching the surface of the SQL that you can generate with collections. Look at the Eav/Model/Entity/Collection/Abstract.php file for a full list of methods to manipulate your SQL. Remember that collections are the only way to load entity objects if you need to use a WHERE clause other than querying against the table’s primary key field. When dealing with non-entity models, you can always write raw SQL and run it against a resource connection.

Using Zend to prepare statements:

You may have noticed that in the ResourceModel files magento team does not use plain SQL. Instead they prepare it using Zend_Db This means that Magento is using Zend for handling requests.

$read = Mage::getSingleton(’core/resource’)->getConnection(’core_read’);
echo get_class($read);

You will get the class as Varien_Db_Adapter_Pdo_Mysql which is an extension of Zend_Db_Adapter Abstract. You can create an instance of a Zend_Db_Select object using the select() method of a Zend_Db_Adapter_Abstract object. This can be used to build ‘SELECT’ queries. When building the query, you can add clauses of the query one by one. There is a separate method to add each clause to the Zend_Db_Select object. For example:

To Build this query:
“SELECT product_id, product_name, price FROM “products” WHERE price > 100.00″ //this is not a magento query

We can write:
$select = $read->select()->from(‘products’, array(‘product_id’, ‘product_name’, ‘price’))->where(‘price > 100.00′);

More on how to build ‘SELECT’ statements using Zend here : http://framework.zend.com/manual/en/zend.db.select.html.

Lets look at an example:

What if you want to load all products in a particular category:

$resource = Mage::getSingleton(‘core/resource’);
$read = $resource->getConnection(‘core_read’);
$select = $read->select()->from(array(‘cp’=>$categoryProductTable))->where(‘cp.category_id=?’, ‘3′);
foreach($products as $row)
$product = Mage::getModel(‘catalog/product’)->load($row['product_id']);
echo $product->getName();

Changing onepage Checkout from 2column-right to 2column-left layout

We can change onepage checkout from 2column-right to 2column-left layout.

Step1: Open Checkout.xml from your-project/app/design/frontend/default/default/layout/
– Find checkout_onepage_index and replace below mention strings
– Replace remove name=”left” with remove name=”right”
– Replace page/2columns-right.phtml with page/2columns-left.phtml
– Replace reference name=”right” with reference name=”left”

Step2: Open opcheckout.js from your-project/skin/frontend/default/default/js/

– Replace col-right with col-left in below function

reloadProgressBlock: function(){
var updater = new Ajax.Updater($$('.col-right')[0], this.progressUrl, {method: 'get', onFailure: this.ajaxFailure.bind(this)});

Thats it.. only 2 step to change.
If this post is helpful to you than plz reply.

– Bijal Bhavsar 🙂

Method Varien_Object::__tostring() in magento due to php version upgrade

Fatal error: Method Varien_Object::__tostring() cannot take arguments in xyz on line 488
Unknown error (8192): Function split() is deprecated in ….
in the frontend or
Unknown error (8192): Assigning the return value of new by reference is deprecated in …
in the connectManager.

If you update your localhost or your provider update the php version to php5.3 magento or before calls a lot off error messages.

The following steps solved the problems temporary:

1. The fatal error toString at first:

lib/Varien/Object.php (line 484)

change this line

public function ___toString(array $arrAttributes = array(), $valueSeparator=’,’)


public function __invoke(array $arrAttributes = array(), $valueSeparator=’,’)

As next I disable the deprecated messages.
This error messages are only a advice that this string is not allowed in php6!
So at this time we don’t need this deprecated messages (we using php 5.3).

2. Disable the DEPRECATED messages in Fronend and download manager


error_reporting(E_ALL | E_STRICT);


error_reporting(E_ALL & E_STRICT & ~E_DEPRECATED);



error_reporting(E_ALL & ~E_NOTICE);


error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);



error_reporting(E_ALL & ~E_NOTICE);


error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED);

All modifications are temporary!
If you update magento this modifications can to overwrite.
Then you must repeat all this modifications.
Hope varien repair this in the future.

If this information is helpful to you than plz do reply…

– Bijal Bhavsar 🙂

Join any table with collection in admin


We can easily display different table values as a column in grid in adminend of magento.

For eg:
I have to add one field for displaying ‘rule_id’, ‘status’, ‘send_date’ field as a columns in customer grid.
//Customer grid collection
$collection = Mage::getResourceModel('customer/customer_collection')

/***From here we can left join with ‘giftcard’ table, join criteria is ‘gf.customer_id = e.entity_id’ and in array we can specifie the ‘rule_id’ as index in column and its values are from table giftcard. i.e ‘gf.rule_id’ and i have grouped with customer primary key i.e ‘e.entity_id’ ***/

'gf.customer_id = e.entity_id',
'rule_id'=> 'gf.rule_id',
'status' => 'IF(gf.status IS NULL,"Unsend","Send")',
'send_date' => 'gf.created_time'

//To add column
Add below

$this->addColumn('created_time', array(
'header' => Mage::helper('giftcard')->__('Coupon Send Date'),
'align' => 'left',
'index' => 'send_date',
'type' => 'datetime',
'gmtoffset' => true

I hope this will help you..
If this post is help you than please do reply,..

Bijal Bhavsar 🙂

Get Attribute options and values

// Add the attribute code here.
// Checking if the attribute is either select or multiselect type.
// Getting all the sources (options)
$options = $attribute->getSource()->getAllOptions(false);
// print as label-value pair
echo '<pre>'. print_r($options) .'</pre>';

– Bijal Bhavsar 🙂

Magento Table Structure

Magento Table Structure

When you first look at Magento database, it can be overwhelming because of its complexity. The Magento database is heavily dependent on EAV (Entity Attribute Value) model. While the EAV model makes it easier to expand the database, i.e. adding your own entities or attributes specific for your store, SQL codes can be very difficult to write.

Before you customize Magento database, make sure you read through EAV model and you understand Magento table structure thoroughly.

EAV Core Tables
All EAV tables are prefixed with “eav_”.

* eav_entity_type: table of all entitiesIt contains information about entity codes, entity models, entity tables and more.
Example entities: customer, order, catalog_category, catalog_product, invoice, shipment, and so on.
Each entity has a corresponding data table prefixed with “_entity”, i.e. customer_entity, sales_order_entity, catalog_category_entity, and so on.
* eav_attribute: table of all attributes
It defines all necessary attributes for each entity. For example, a customer has first name, last name, email address and so on. Customer is defined as an entity in the eav_entity_type table and customer’s attributes such as first name, last name or email are defined in the eav_attribute table. Hundreds of attributes are defined by default with Magento installation.
* eav_attribute_option, eav_attribute_option_value
These two tables are used to define options for each attribute. For example, the manufacturer attribute can have “Toshiba”, “Dell”, or “HP” for its options. These option values are stored in the eav_attribute_option_value table and the relationship of each option and the attribute is stored in the eav_attribute_option table. An option can also have multiple option values when it’s used for multiple stores.
* eav_attribute_set
This table is used to define different attribute sets for an entity. For example, a cell phone has different options from a camera. Both cell phone and camera are products (entities) that have different option sets (attribute sets).

Table Sets (Table collections)
In Magento database, an entity can have several tables that share the same prefix. For example, the product entity has the catalog_product_entity table for its main data and several other tables prefixed with “catalog_product_” such as catalog_product_entity_int, catalog_product_entity_media_gallery, catalog_product_entity_text and so on.

To store the data more efficiently, product details are stored separately depending on their data types. When the value of the data is an integer type, it’s saved in the catalog_product_entity_int table, and when its type is an image, it’s saved in the catalog_product_entity_media_gallery table. The whole point is not saving big image data with small integer data in the same table.

Tables to Define Relationships
The catalog_category_product table, catalog_product_website or downloadable_link_purchased are examples of the tables that show relationships. The catalog_category_product table show which category includes which products and the downloadable_link_purchased table shows which order has which downloadable links.

– Bijal Bhavsar 🙂