Magento Database Models, Helpers and Raw SQL Queries

Magento Database Models

The best way to access and manipulate data in Magento is by using the provided objects and their associated functionality..
For example, you can get a Magento category model like this:

$model_category = Mage::getModel('catalog/category');

With your category model you now retrieve data
e.g. get all child categories of category ID 2 and echo the name

$model_category = Mage::getModel('catalog/category');
foreach ($model_category->getCategories($id=2) as $category) {
	echo $category->getName();
}

Magento Helper Classes

Magento has helper classes which are pretty handy also.
For example, you can get a Magento category helper like this:

$helper = Mage::helper('catalog/category');

Your helper will have a bunch of methods defined for performing common tasks.
For example, you can get all your stores top level categories as follows:

$helper = Mage::helper('catalog/category');
foreach ($helper->getStoreCategories() as $category) {
	echo $category->getName();
}

Check this file to see what methods are available in the catalog category helper:
magento/app/code/core/Mage/Catalog/Helper/Category.php

Raw SQL Queries in Magento

That’s a VERY brief intro into using Magento’s models and helpers. They should serve your needs most of the time, but if you really need to deal with raw queries here’s how to do it.

Depending on what sort of operation you wish to perform (read or write) you will use one of the following to get a database connection object:

$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');

These connections are essentially Zend database objects so you can run any of the methods defined here. Probably of most interest will be query(), fetchAll(), and fetchOne().

Write directly to a Magento database table

This example creates a core_write connection object, then directly inserts a row with raw SQL via the query() method.

// 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);

Read directly from a Magento database table

This example creates a core_read connection object, then uses the fetchAll() method to retrieve and iterate all the rows returned by a raw sql query.

// display all regions defined for the United States
$sql = "SELECT * FROM directory_country_region WHERE country_id='US'";
$connection = Mage::getSingleton('core/resource')->getConnection('core_read');
foreach ($connection->fetchAll($sql) as $arr_row) {
	echo $arr_row['default_name'];
}