Run Raw SQL statement with Magento Core resource

Monday, 24. October 2011

There are times when you just want to run a simple SQL statement to quickly test things up on Magento without having to load up the entire collection.

On servers with limited resources this can be handy. Also, for someone like me sometimes I find life much easier working directly on the database (as LONG as you KNOW what you are doing! This approach in Magento can easily be your own UNDOING!)

Get the core resource

$_resource = Mage::getSingleton('core/resource');

Get the correct Database name

Because some installations may have table prefixes, this is the best way to make sure you get the correct table name with prefix (if one exists)

$_tableName = $_resource->getTableName('catalogsearch_query');

Start Magento Database Connection

Once everything is in place and you are ready to go, here is how you obtain DB connection

$_conn = $_resource->getConnection('core_write'); #you can also use 'core_read' for just reading data
# example
$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');

Read Data From Magento Database

$sql = "SELECT * FROM directory_country_region WHERE country_id='UK'";
$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
foreach ($connection->fetchAll($sql) as $arr_row) {
	print $arr_row['default_name'];
}

Insert Data Into Magento Database Directly

// insert a new US State into the region table
$sql = "INSERT INTO `directory_country_region` (`region_id`,`country_id`,`code`,`default_name`) VALUES (NULL,'US','XX','New US State')";
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');
$connection->query($sql);

Enjoy!