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.
$connection = Mage::getSingleton('core/resource')->getConnection('default_setup');
Then we will use this connection to instanciate Zend_Db_Select object and write query like
$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'];
}
}
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
$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);
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.
$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);
Happy Brogramming!!
Hi Subesh
In the following code:
$connection = Mage::getSingleton(‘core/resource’)->getConnection(‘default_setup’);
it would be better if you explain the possible argument values of getConnection() method.
Great article though!!
Keep on writing.
Thanks
Yes other options includes core_read and core_write. But with default_setup you will have access to both the operations.
Your tutorial is very enlightening.Thank you so much for this clear and detailed explanation
Great Tutorial.Very helpful.. Thanks for sharing ..
Great post about custom query in Magento. good job.