Direct SQL Queries In Magento

Magento's use of data models provide a great way to access and modify data. Using aptly named methods and clever abstraction, Varien hide away the complex SQL needed to perform data operations. While this makes learning models easier, it often impacts the speed of the operation and therefore the responsiveness of your site. This is especially true when saving models that use the EAV architecture. More often that not, this cannot be avoided, however there are some situations where executing direct SQL queries would be simpler and much quicker. An example of this is updating product prices globally in Magento. It would be easy enough to write some Magento code that looped through all products and modified the price. On a large data set, saving each individual product can take a long time and therefore make the system unusable. To combat this, it is possible to issue a direct SQL query which could update 1000's of products in 1 or 2 seconds.

Database Connections In Magento

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. It is important to ensure that you use the correct resource when reading or writing data to the database, especially when writing custom Magento extensions that will be released into the wild.

<?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');

Table names and table prefixes

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 useful if you are installing multiple system into 1 database as it helps to distinguish each application's data from another. Fortunately, Magento has a simple built in function which allows you to add the prefix to a given table name.

Get a table name from a string

<?php

	/**
	 * Get the resource model
	 */
	$resource = Mage::getSingleton('core/resource');
	
	/**
	 * Get the table name
	 */
	$tableName = $resource->getTableName('catalog_product_entity');
	
	/**
	 * if prefix was 'mage_' then the below statement
	 * would print out mage_catalog_product_entity
	 */
	echo $tableName;

Get a table name from an entity name

<?php

	/**
	 * Get the resource model
	 */
	$resource = Mage::getSingleton('core/resource');
	
	/**
	 * Get the table name
	 */
	$tableName = $resource->getTableName('catalog/product');
	
	/**
	 * if prefix was 'mage_' then the below statement
	 * would print out mage_catalog_product_entity
	 */
	echo $tableName;

Reading From The Database

While Magento models hide the complexity of the EAV system, they sometimes request far more data than is needed. If for example you have a product ID and want it's SKU, it would be much quicker to run a single query to obtain this value than to load in a whole product model (the inverse of this operation is available via the product resource class).

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.

<?php
	
	/**
	 * 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);

Try this code and look at the results. Notice how all of the SKU's are in a single array, rather than each row having it's own array? If you don't understand this, try changing fetchCol for fetchAll and compare the differences.

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 44 and return it's SKU.

<?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');
	
	/**
	 * Set the product ID
	 */
	$productId = 44;
	
	$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/>';

When trying out this example, ensure you change the product ID to an ID that exists in your database!

You may think that fetchOne works the same as fetchCol or fetchAll would if you only added 1 column to the SELECT query and added a 'LIMIT 1', however you would be wrong. The main difference with this function is that the value returned is the actual value, where as Varien_Db_Select::fetchCol and Varien_Db_Select::fetchAll would wrap the value in an array. To understand this a little, try swapping the method's and comparing the results.

Writing To The Database

When saving a Magento model, there can be a lot of background data being saved that you weren't even aware of. For example, saving a product model can take several seconds due to the amount of related data saves and indexing that needs to take place. This is okay if you need all the data saving, but if you only want to update the SKU of a product, this can be wasteful.

The example code below will show you how when given a product ID, you can alter the SKU. This is a trivial example but should illustrate how to execute write queries against your Magento database.

<?php

	/**
	 * Get the resource model
	 */
	$resource = Mage::getSingleton('core/resource');
	
	/**
	 * Retrieve the write connection
	 */
	$writeConnection = $resource->getConnection('core_write');

	/**
	 * Retrieve our table name
	 */
	$table = $resource->getTableName('catalog/product');
	
	/**
	 * Set the product ID
	 */
	$productId = 44;
	
	/**
	 * Set the new SKU
	 * It is assumed that you are hard coding the new SKU in
	 * If the input is not dynamic, consider using the
	 * Varien_Db_Select object to insert data
	 */
	$newSku = 'new-sku';
	
	$query = "UPDATE {$table} SET sku = '{$sku}' WHERE entity_id = "
			 . (int)$productId;
	
	/**
	 * Execute the query
	 */
	$writeConnection->query($query);


To test this has worked, use the knowledge gained from the first part of this tutorial to write a query to extract the SKU that has just been changed.

Varien_Db_Select

The Varien_Db_Select, which has been touched on in this article is a far better option for extracting/wriiting information. Not only is it easy to use, it also provides a layered of security, which if used correctly, is impenetrable. More will be covered on Varien_Db_Select (aka Zend_Db_Select) in a future article.

Conclusion

Sometimes it is necessary to execute direct SQL queries in Magento, however, please be careful! The Magento model's are there for a reason and provide a layer of security which you will have to manually add to your own direct SQL queries. Be sure to escape any user input and when possible, stick to the Magento model methods! If you can't stick to the Magento models, consider using Varien_Db_Select; it won't stop you making errors but it will add an almost impenetrable layer of security to your database queries.

As a side note, if you're going to be querying the database directly, it would be a good idea to learn about Magento's EAV database architecture.

23 thoughts on “Direct SQL Queries In Magento”

  • Pete

    Thanks for your article. I have been trying to find an educated answer to whether it ist safe to change product titles, descriptions, rewritable URL directly in the DB using SQL queries. from what I can see (but I am no expert) the magento catalog_product_entity_varchar and catalog_product_entity_text tables contain simple text fields (value) containing product descriptions, URLs and titles. I want to make SEO updates to my products replacing text strings within these tables with a query like

    UPDATE catalog_product_entity_varchar SET value = replace(value, "OLD TITLE", "NEW TITLE");

    After refreshing caches this seems to work ok, but I want to be sure there are no relational tables that will be impacted by changing these product, title, descriptions and URLs directly in the DB before I update 1000s of products!

    Perhaps you can give me your thoughts.

    Reply
  • Sean

    To list the functions, the code had to be modified to:

    [code]<?php

    require_once 'app/Mage.php';
    umask(0);
    Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
    $userModel = Mage::getModel('admin/user');
    $userModel->setUserId(0);

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

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

    ?>
    [/code]

    Reply
  • Sean

    For some reason the following: [code]Mage::getResource('core/resource')->getTableName('catalog_product_entity')[/code] throws the following error:

    Fatal error: Call to undefined method Mage::getresource()

    Line referenced in "Reading data from the database" above as follows:[code]$query = 'SELECT * FROM ' . Mage::getResource('core/resource')->getTableName('catalog_product_entity');[/code]

    If I rewrite it to be: [code]$query = 'SELECT * FROM catalog_product_entity';[/code] it proceeds to list out all the entries as expected.

    Thank you for your articles on EAV and SQL, it's been a real help!

    Reply
    • BT

      Apologies, the code should read:

      Mage::getSingleton('core/resource')->getTableName('catalog_product_entity');

      I'll fix the article now.

      Thanks again!

      Reply
  • Sean

    Sweet! It's functioning now. I'm working on doing some basic SQL data dumps as the Magento data flow process is kind of slow. I'd like to not wait 3 hours for a basic listing of sku, name, price on simple product...

    Reply
  • Sean

    Since one of the things I keep hearing is the need for pricing output within a reasonable time frame for larger sites, here's a base price report using SQL queries. 9k in less than 30 seconds!

    [code]<?php

    /* Include Mage and run as admin in store 0 */

    require_once 'app/Mage.php';
    umask(0);
    Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
    $userModel = Mage::getModel('admin/user');
    $userModel->setUserId(0);

    /* Set variables, since in EAV the entity and attribute id are sequential and uniquely assigned,
    * you'll need to look them up in the eav_attribute table for your magento installation to make
    * sure you have the right value
    */

    $read = Mage::getSingleton('core/resource')->getConnection('core_read');
    $pEntity = Mage::getSingleton('core/resource')->getTableName('catalog_product_entity');
    $pVarchar = Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_varchar');
    $pDecimal = Mage::getSingleton('core/resource')->getTableName('catalog_product_entity_decimal');
    $nameAttrId = 56;
    $priceAttrId = 60;

    /* SQL query to get base prices on simple product for store 0 */

    $query = 'SELECT p_entity.sku AS sku, p_varchar.value AS name, p_decimal.value AS price
    FROM ' . $pEntity . ' p_entity, ' . $pDecimal . ' p_decimal, ' . $pVarchar . ' p_varchar
    WHERE p_entity.entity_id = p_varchar.entity_id AND p_entity.entity_id = p_decimal.entity_id AND ((p_entity.type_id="simple") AND (p_varchar.attribute_id=' . $nameAttrId . ') AND (p_varchar.store_id=0) AND (p_decimal.attribute_id=' . $priceAttrId . ') AND (p_decimal.store_id=0))';

    $results = $read->fetchAll($query);

    /* straight dump to screen without formatting */

    print_r($results);

    ?>[/code]

    Reply
  • Matthias

    Sir, this was incredibly helpful! Thank you very much!

    Reply
  • Aijaz

    Awesome thanks a lot. Good for beginners

    Reply
  • Mehdi

    Hi, This is amazing, but I'm not sur how to put it in action.
    Actually, I don't know which file(s) I have to edit to see those changes.

    I'm trying to find a way to build an array which points out how many products of each have been sold per day in a period time :
    somthing like

    product1 product2 ... product n
    day1 2 10 ... 0
    day2 10 5 ... 7
    ....
    dayn a b ... c


    How can I do that?? My main problem is to find the file I can touch. and If someone has suggestions about the queries I'll be glad..

    thank you!!

    Reply
    • BT

      The best tip is to not touch any files! To achieve what you're trying to do, you should either setup a custom module or if it suits your situation better, load the Magento core files in to a separate, PHP file and write your script contents there

      Reply
  • Mehdi

    My previous post wasn't correctly displayed. I'll give it another try :

    -----------product_1-------------product_2--------------product_n
    day_1---------a-----------------------b------------------------c-------
    day_2---------a1---------------------b2-----------------------c2-------

    Reply
  • Floppy Max

    Hello everyone,

    I'm working on Magento Enterprise v1.9.0.0.
    I need to connect to the database and run SQL directly to query tables.

    Here is what I've used to do that.

    $query = "...SQL QUERY...";
    $databaseConnection = Mage::getSingleton('core/resource')->getConnection('core_read');
    $result = $databaseConnection->fetchAll($query);

    I have Magento Community v1.4.1.1 and Magento Enterprise v1.9.0.0 installed on my local computer.
    So in order to test, I've run the above code on my local machine, and all works fine.

    But it doesn't work on live server.
    Magento Enterprise v1.9.0.0 is installed on the live server and online store is running.
    All works fine on live store and nothing special happens.

    But I've uploaded this code to the server, it doesn't work.
    And further, I can't get any PHP/MySQL error message.

    Here is more detailed codes which I used to track run time.

    // Debug Output
    $this->OutputDebugLogo("");
    $this->OutputDebugLogo("Query : #", $query);

    $databaseConnection = Mage::getSingleton('core/resource')->getConnection('core_read');
    $result = $databaseConnection->fetchAll($query);

    // Debug Output
    $this->OutputDebugLogo("Result : #", $result);
    $this->OutputDebugLogo("");

    I can be sure there is no error on SQL statement.
    And the function OutputDebugLogo() is made by myself to output debug message with variables.

    Here is what I've got after running this script on my local machine.

    Query :
    ' SELECT ... FROM ... WHERE ... '

    Result:
    array ( 0 => array ( 'items' => 'a:2:{s:3:"MEN";a:11:{s:9:... )

    And here you can see what I've got on the live server.

    Query :
    ' SELECT ... FROM ... WHERE ... '

    As you can see, there is no PHP/MySQL error message.
    And we can say the script wass stopped running at database code block.

    I can't make any progress to find the main cause.
    Why did it happen?
    Can anyone know it?

    I'm really looking forward to getting help from everyone.

    Best Regards,
    Floppy

    Reply
  • sanith

    Nice article,very helpfull

    Reply
  • bruce

    i have a question and how can i do if i have an external page something like register.php outside of magento. and i want to include the magento registration page. and also display after registering the customer list.
    remember outside magento.

    im all ready using this
    <?php
    require_once '../app/Mage.php';
    umask(0);
    /* not Mage::run(); */
    Mage::app('default');

    $_layout = Mage::getSingleton('core/layout');
    and is working up to a point .
    but im having a problem calling the register.phtml page how do i do that
    thanks

    Reply
  • Guillermo

    this helped me a lot thank, great article

    Reply
  • Magento Development

    excellent article, i obviously adore this page, continue on it.

    Reply
  • Ian Ryan

    Does anyone have a reliable SQL script that will export a product catalog to a csv in the same form as the System --> Import / Export --> Export magento admin menu option. I have a catalog of 17,000 products running in a shared hosting environment with host restrictions on the max_memory_limit for a php script of 128MB (the server ignores any setting above 128MB in my local php.ini, .htaccess and index.php). The magento export process eats memory and with a limit of 128MB the export times out without exception using the magento built-in export process.

    Reply
  • man_in_black

    Hey thanks a lot. It was a really helpful tutorial. I am currently stuck into a query how do i count a colum data i mean i have points colum in user_points table now i want to count how many points total user have got any idea ?

    Reply
  • Renga

    Can you please send me mysql query to select all product title, SKU, category?

    Thank you!

    Reply
  • Praful

    Hello,

    here is my query and this give proper ouput.
    But How can I set it according to magento rules?

    SELECT main_table.*,
    (select company from sales_flat_order_address sfoa where sfoa.entity_id=sfo.billing_address_id) as bill_to_company,
    (select company from sales_flat_order_address sfoa where sfoa.entity_id=sfo.shipping_address_id) as ship_to_company
    FROM sales_flat_order_grid as main_table inner join sales_flat_order as sfo
    on main_table.entity_id = sfo.entity_id

    please help me....

    Thanks & Reagards
    Praful

    Reply
  • Alan

    Your tags in the code examples are breaking out of the actual tags... Nice article though.

    Reply
  • Surendra

    I am new to Magento please let me.

    the total of base_grand_total attribute. of the table sales_flat_order_grid..

    at admin pannel

    Reply
  • Damu

    Can any one please help me to delete all product prices from store 1, 2, 3 which are not equal to store 0 - http://magento.stackexchange.com/q/36891/3906

    Reply
Leave a Reply
Post your comment

FishPig Ltd