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:
<?php
$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');
$collection->getConnection();
$model->getResource()->getReadConnection();
Getting a Table’s Name
<?php
$table = Mage::getSingleton('core/resource')->getTableName('llapgoch_banners/banner');
Getting part of a SELECT
$collection->getSelect()->getPart(Zend_Db_Select::FROM);
Fetch Col
<?php
$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
<?php
$connection->fetchOne('SELECT * FROM ' . $resource->getTableName('llapgoch_banners/banner'));
– Returns a single row for the query
Fetch All
<?php
$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:
<?php
$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:
<?php
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_read');
$select = new Varien_Db_Select($connection);
$select->from($resource->getTableName('llapgoch_banners/banner'))
->where('banner_id > :num_banners');
$results = $connection->fetchAll($select, array(
'num_banners' => 1
));
Select Object Methods
Where
<?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);
Limit
<?php $select->limit($number, $offset);
OrderRand
<?php $select->orderRand();
From
<?php $select->from($tableName, $cols = array());
Columns
<?php $select->columns($cols = "*", $correlationName = null);?>
– Specifies the columns to be brought in the select. Can contain aggregate queries.
Union
<?php $select->union($select = array(), $type = self::SQL_UNION);
Join
<?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);
Group
<?php $select->group($spec)
– $spec can be an array or a string
Having
<?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.
Order
<?php $select->order($spec);
– $spec can be a string or an array of columns.
Query
<?php $select->query($fetchMode = null, $bind = array());
– $fetchMode is an integer. This is optional to using the connection’s fetchAll, fetchOne, and fetchCols methods
Reset
<?php $select->reset($part = null);
– Allows parts of the query to be reset
getAdapter
<?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