How to run Direct SQL Queries in Magento?

Today we will learn how to run direct SQL queries in the Magento 1.x. By default Magento usages data model to access and modify the database. The data model provides good abstraction which hides the tricky queries but they also make the operation slow. Direct SQL queries come very handily whenever we need to update/insert a large number of data. For example, you have to update the price of all the products of a manufacturer and there are 15k products in that manufacturer. Doing this via loading product model in Magento will take a lot of time.

We can also use Magento resource to directly run our SQL queries without loading the whole collection.

Get the core resource model –

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

After this we need to create read or write connection as per our need.

/**

* Retrieve the read connection

*/

$readConnection = $resource->getConnection('core_read');

/**

* Retrieve the write connection

*/

$writeConnection = $resource->getConnection('core_write');

Fetching the correct Table name –

In case you are unaware of the prefix of the table then you can use below code to fetch the correct table name.

$_tableName = $resource->getTableName('catalog_product_entity');

If the prefix is mage_ then the table name it will return will be mage_catalog_product_entity

Reading Data –

To read data from the table use the following code

$query = “select * from {$_tableName}”;

$query_result = $readConnection->fetchAll($query);

Here, $query_result will return the result of select query. In our case it will return the array of all the product. In case you need data of only product then use following command.

$query = “select * from {$_tableName} where entity_id={$_productId}”;

$query_result = $readConnection->fetchAll($query);

Here $_productId is the id of the product whose data we need.

Writing Data –

For inserting data into the table use the following code.

$insert_query = “INSERT INTO {$_tableName} (‘COL_NAME1’,’COL_NAME2’) VALUES                                                                 (‘COL_VALUE1’,’COL_VALUE2’)”;

$writeConnection->query($insert_query);

Updated Data –

If we have to update the type_id of a product to “configurable” then we can use following code. Here $_productId is the product id.

$update_query = “UPDATE {$_tableName} SET type_id=”configurable” WHERE 						entity_id={$_productId}”;
$writeConnection->query($update_query);

But Magento has provided the concept of the model for database connection as they provide abstraction and they are more secure that the direct SQL queries.