Running arbitrary database queries

As part of a larger modification I have been working on I decided to use straight database queries from inside Magento rather than try and find out (and spend time debugging) what the proper "Magento way" to achieve the same thing was. After a bit of investigation I came up with what I think will be most generally useful and should work from almost anywhere in a Magento system.

What I came up with was to get one of the core MySQL and retrieve the database connector from it. This connector is an instance of a Zend_Db_Adapter_Abstract class.

$conn = Mage::getModel('Core/Mysql4_Config')->getReadConnection();
$conn->query("YOUR QUERY");

You can also use:

$conn = Mage::getModel('Core/Mysql4_Config')->getReadConnection();
$resultArray = $conn->fetchAll("YOUR QUERY");

Pretty simple really. I was worried that you'd only be able to read from the database since it specifies a "read connection" but there didn't seem to be a corresponding getWriteConnection so I went with what I had. You can also recover database connection settings through the same connection with:

$config = Mage::getModel('Core/Mysql4_Config')->getReadConnection()->getConfig();

The ability to run arbitrary database queries is only really useful for tables you've defined yourself, or to make minor tweaks. Anything more advanced and you'd be advised to spend the time to learn how to achieve it through Magento objects as the interrelations of tables in the database is a dark magic that is quite capable of blowing your mind if you dig too deep.