Custom Query in Magento a Zend Approach [Updated]
I’ve already mentioned about custom query in Magento, before but now I think its time to upgrade that method to next level because it would always be good to have standard code for reference.So it is just a new improved way of using a custom query.
First of all we will need is a connection instance. Lets use default_setup instance because it can used for read and write purpose.
[source language=”php”]
$connection = Mage::getSingleton(‘core/resource’)->getConnection(‘default_setup’);
[/source]
Then we will use this connection to instanciate Zend_Db_Select object and write query like
[source language=”php”]
$select = new Zend_Db_Select($connection);
$table = ‘catalog_product_index_price’;
$select->from($table, array(‘entity_id’))
->where(‘entity_id = ?’, $entityId);
// Getting result
$result = $select->query();
$rowCount = $result->rowCount();
if ($rowCount) {
foreach ($result->fetchAll() as $data) {
return $data[‘entity_id’];
}
}
[/source]
The above one is for select but what about inserting data? It is simple as well. All you have to do is create an associative array of data to be inserted and use connection to add data. For example
[source language=”php”]
$connection = Mage::getSingleton(‘core/resource’)->getConnection(‘default_setup’);
$table = ‘catalog_product_index_price’;
$insertData = array();
$insertData [‘price’] = $productPrice;
$insertData [‘final_price’] = $productPrice;
$insertData [‘min_price’] = $productPrice;
$insertData [‘max_price’] = $productPrice;
$insertData [‘tier_price’] = $productPrice;
$connection->insert($table, $insertData);
[/source]
I think this should be a good enough code for using custom query in Magento. About Zend_Db_Select please visit http://framework.zend.com/manual/en/zend.db.select.html.
[UPDATE]
If you want to update the data with custom query and have a condition then you can send the conditions as array as third params to update function. For example.
[source language=”php”]
$connection = Mage::getSingleton(‘core/resource’)->getConnection(‘default_setup’);
$table = ‘catalog_product_index_price’;
$updateCond = array(); // Update condition array container.
$insertData = array();
$insertData [‘price’] = $productPrice;
$insertData [‘final_price’] = $productPrice;
$insertData [‘min_price’] = $productPrice;
$insertData [‘max_price’] = $productPrice;
$insertData [‘tier_price’] = $productPrice;
$updateCond [] = ‘store_id = 0’;
$updateCond [] = ‘entity_id = 3’;
$connection->insert($table, $insertData, $updateCond);
[/source]
Happy Brogramming!!