Direct SQL Queries In Magento

Ref: http://fishpig.co.uk/direct-sql-queries-magento/

Table names and table prefixes

$tableName = Mage::getSingleton(‘core/resource’)
->getTableName(‘catalog_product_entity’);

// if prefix was ‘mage_’ then the below statement
// would print out mage_catalog_product_entity
echo $tableName;

Accessing the database connection resource

$read = Mage::getSingleton(‘core/resource’)->getConnection(‘core_read’);
$write = Mage::getSingleton(‘core/resource’)->getConnection(‘core_write’);

For a list of functions available, copy the following code into a Magento template.
$read = Mage::getSingleton(‘core/resource’)->getConnection(‘core_read’);

echo ‘<pre>’;
print_r(get_class_methods($read));
echo ‘</pre>’;
exit

Reading data from the database

$read = Mage::getSingleton(‘core/resource’)->getConnection(‘core_read’);
$query = ‘SELECT * FROM ‘ . Mage::getSingleton(‘core/resource’)->getTableName(‘catalog_product_entity’);
$results = $read->fetchAll($query);

print_r($results);

Writing information to the database


$write = Mage::getSingleton(‘core/resource’)->getConnection(‘core_write’);

// Add your own query below
// I didn’t add one as I didn’t want you to run the code
// and me break your database!
$query = ‘add your query here’;
$write->query($query);

3 thoughts on “Direct SQL Queries In Magento

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s