Direct SQL Query in Magento

13 Aug

Sometimes it is necessary to execute direct SQL queries in Magento. However it not a standard way of doing it right.Be careful!!

ACCESSING THE DATABASE CONNECTION RESOURCE

By default, Magento will automatically connect to it’s database and provide two separate resources which you can use to access data: core_read and core_write. As you can probably guess, core_read is for reading from the database while core_write is for writing to the database.

<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton(‘core/resource’);

/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection(‘core_read’);

/**
* Retrieve the write connection
*/
$writeConnection = $resource->getConnection(‘core_write’);

When installing Magento, you are given the option to use a table prefix. A table prefix is a string of characters that is added to the start of every table name in your database.These are very useful if you are maintaing more than one store as it will help you to distinguish.

Get a table name from a string

/**
* Get the table name
*/
$tableName = $resource->getTableName(‘catalog_product_entity’);

/**
* if prefix was ‘dbn_the below statement
* would print out dbn_catalog_product_entity
*/
echo $tableName;

Reading From The Database

Varien_Db_Select::fetchAll

This method takes a query as it’s parameter, executes it and then returns all of the results as an array. In the code example below, we use Varien_Db_Select::fetchAll to return all of the records in the catalog_product_entity table.

/**
* Get the resource model
*/
$resource = Mage::getSingleton(‘core/resource’);

/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection(‘core_read’);

$query = ‘SELECT * FROM ‘ . $resource->getTableName(‘catalog/product’);

/**
* Execute the query and store the results in $results
*/
$results = $readConnection->fetchAll($query);

/**
* Print out the results
*/
var_dump($results);

Varien_Db_Select::fetchCol

This method is similar to fetchAll except that instead of returning all of the results, it returns the first column from each result row. In the code example below, we use Varien_Db_Select::fetchCol to retrieve all of the SKU’s in our database in an array.

<?php
/**
* Get the resource model
*/
$resource = Mage::getSingleton(‘core/resource’);

/**
* Retrieve the read connection
*/
$readConnection = $resource->getConnection(‘core_read’);

/**
* Retrieve our table name
*/
$table = $resource->getTableName(‘catalog/product’);

/**
* Execute the query and store the results in $results
*/
$sku = $readConnection->fetchCol(‘SELECT sku FROM ‘ . $table . ‘);

/**
* Print out the results
*/
var_dump($results);

Varien_Db_Select::fetchOne

Unlike the previous two methods, Varien_Db_Select::fetchOne returns one value from the first row only. This value is returned on it’s own and is not wrapped in an array. In the code example below, we take a product ID of 4 and return its sku.

/**
* Retrieve our table name
*/
$table = $resource->getTableName(‘catalog/product’);

/**
* Set the product ID
*/
$productId = 4;

$query = ‘SELECT sku FROM ‘ . $table . ‘ WHERE entity_id = ‘
. (int)$productId . ‘ LIMIT 1’;

/**
* Execute the query and store the result in $sku
*/
$sku = $readConnection->fetchOne($query);

/**
* Print the SKU to the screen
*/
echo ‘SKU: ‘ . $sku . ‘<br/>’;

Refrence: http://fishpig.co.uk Blog

For any help, don’t hesitate to comment

Advertisements

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