Magento 1

SQL in Magento

Although Magento’s collections and models are convenient, they can often be cumbersome and awkward to get to perform certain tasks. Fortunately, through the connection (Magento_Db_Adapter_Pdo_Mysql, and ultimately Zend’s MySql connection stack down to Zend_Db_Adapter_Abstract) and select (Varien_Db_Select, and ultimately Zend’s Zend_Db_Select) allow us perform database queries without too much hassle.

Getting the Connection Object

A connection object can be used from the following places:

$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');


Getting a Table’s Name

$table = Mage::getSingleton('core/resource')->getTableName('llapgoch_banners/banner');

Getting part of a SELECT


Fetch Col

$connection->fetchCol('SELECT sku from ' . $resource->getTableName('catalog/product'));

– This puts the columns into a single array. I.e. all skus will be in a one dimensional array (array(‘sku1’, ‘sku2’, ‘sku3’) etc.

Fetch One

$connection->fetchOne('SELECT * FROM ' . $resource->getTableName('llapgoch_banners/banner'));

– Returns a single row for the query

Fetch All

$connection->fetchAll('SELECT * FROM ' . $resource->getTableName('llapgoch_banners/banner'));

– Returns all rows for the query

Combining with a Select Object

Writing direct SQL is all well and good, but it’s often neater and safer to write such queries using a select object, and passing that to the connection:

$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
$select = new Varien_Db_Select($connection);
$select->from($resource->getTableName('llapgoch_banners/banner'), array('title', 'is_active'))
     ->where('banner_id > 1');
$results = $connection->fetchAll($select);

Binding Values

Binding values is useful for automatic value escaping. By referencing variables using :variable_name, we can pass in arrays of replacement variables into the connection’s fetch methods:

$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
$select = new Varien_Db_Select($connection);
	->where('banner_id > :num_banners');
$results = $connection->fetchAll($select, array(
	'num_banners' => 1

Select Object Methods


 <?php $select->where($condition, $value = null);

– where provides the option of passing a single value into the condition to be replaced, but can also be used like the above example using binds. To use the value in the condition, we use a question mark:

 <?php $select->where('banner_id > ?', 1);


 <?php $select->limit($number, $offset);


 <?php $select->orderRand();


 <?php $select->from($tableName, $cols = array());


 <?php $select->columns($cols = "*", $correlationName = null);?>

– Specifies the columns to be brought in the select. Can contain aggregate queries.


 <?php $select->union($select = array(), $type = self::SQL_UNION); 


 <?php $select->join($name, $cond, $cols = self::SQL_WILDCARD, $schema = null);

– This is the same as the joinInner method.

Join Left

 <?php $select->joinLeft($name, $cond, $cols = self::SQL_WILDCARD, $schema = null);

Join Right

 <?php $select->joinRight($name, $cond, $cols = self::SQL_WILDCARD, $schema = null); 

OR Where

 <?php $select->orWhere($cond, $value = null, $type = null); 


 <?php $select->group($spec) 

– $spec can be an array or a string


 <?php $select->having($cond, $value = null, $type = null); 

– $value will be replaced into $cond using a question mark in the query.

OR Having

 <?php $select->orHaving($cond, $value = null, $type = null);

– $value will be replaced into $cond using a question mark in the query.


 <?php $select->order($spec);

– $spec can be a string or an array of columns.


 <?php $select->query($fetchMode = null, $bind = array()); 

– $fetchMode is an integer. This is optional to using the connection’s fetchAll, fetchOne, and fetchCols methods


 <?php $select->reset($part = null);

– Allows parts of the query to be reset


 <?php $select->getAdapter();

– Returns the adapter (connection object) passed into the constructor.

TODO: Add info about the Zend Connection (adapter) methods in Zend_Db_Adapter_Abstract