Debug a SQL query in Magento

Magento offers a powerful built-in DBA layer ( proudly powered by the Zend Framework ) and a set of custom API to perform basic actions in Magento’s way.

Using Zend_Db_Select is simple to debug a bad smell in your SQL, but with Magento’s APIs?

There is a very simple solution, when we talk about, for example, the collections.

Look at this example:

1
2
3
4
<?php

$collection = Mage::getModel('catalog/category')->getCollection()
->addAttributeToSelect('name', '%');

Seems like we’re trying to get a list of ALL categories with MySQL wildcard ( stop just a second: this example is silly, is just to make you understand ) but, printint the $collection, we don’t get a single category.

Ok, it’s time to see why our SQL is failing:

1
2
3
<?php

echo $collection->getSelect();

Nothing more.

Just to be clear: if you want to run a query conditional in Magento you must use ->addAttributeToFilter('name', array('like' => '$likeValue')).

Note, wildcards are not automatically inserted; so, for example, your $likeValue might look like %myLikeValue instead of myLikeValue.

About getSelect(): it is a very powerful method because it lets you switch between Magento SQL APIs and ZF’s ones. So after having described your getCollection() ( magento ) you can call the getSelect() and add a where() ( Zend Framework ).


Hi there! I recently wrote an ebook on web application security, currently sold on leanpub, the Amazon Kindle store and gumroad.

It contains 160+ pages of content dedicated to securing web applications and improving your security awareness when building web apps, with chapters ranging from explaining how to secure HTTP cookies with the right flags to understanding why it is important to consider joining a bug bounty program.

Feel free to skim through some of the free chapters published on this blog and, if the content seems interesting enough to you, grab a copy on leanpub, the Amazon Kindle store, gumroad or simply checkout right down below!

Buy the Web Application Security ebook for $6.99

In the mood for some more reading?

...or check the archives.