Magento execute custom query

Reading Time: < 1 minute

How to Magento execute custom query?In here you can execute custom query to insert,update,delete and fetch data from database with using Magento existing database connection.

Magento execute custom query to insert data

// fetch write database connection that is used in Mage_Core module
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
// now $write is an instance of Zend_Db_Adapter_Abstract
$write->query("INSERT INTO tablename VALUES ('111','aaa','abc123')");

---

Execute custom query to fetch data

// FETCH DATA
$write = Mage::getSingleton('core/resource')->getConnection('core_write');
// now $write is an instance of Zend_Db_Adapter_Abstract
$result = $write->query("SELECT * FROM  `pepitashop_catalog_product_entity` LIMIT 0 , 30 ");
while ($row = $result->fetch() ) {
	$categoryIds[] = $row['id']; //insert into an array
}

---

Getting data into phtml files/view files

Method-1

// GETTING DATA FROM ANOTHER METHOD
$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_read');
$query = 'SELECT * FROM ' . $resource->getTableName('catalog/product');
$results = $readConnection->fetchAll($query);

Method-2

$resource = Mage::getSingleton('core/resource');
$conn     = $resource->getConnection('core_read');
$results  = $conn->query("SELECT entity_id FROM catalog_product_entity_varchar WHERE entity_id = 65");
$row = $results->fetchAll();

---

Execute custom query to update data

// Use in phtml files
$resource   = Mage::getSingleton('core/resource');
$conn       = $resource->getConnection('externaldb_read');
$conn->query("UPDATE sales_flat_order SET discount_qbt=12,comment_qbt ='test' WHERE increment_id=125");