Magento includes a database abstraction layer and object relationship manager that is really great at ensuring data integrity and consistent data access. In general, you should avoid writing to the database and instead use a well-written Magento extension that performs the task for you. However, sometimes a direct query needs to be made whether for performance or convenience and in this case you need to know how to do direct queries the right way.
Step 1: Get the database connection
To get the database connection, so you can execute the direct query you should use one of the following depending on whether you need to read or write:
|
1 2 |
$read = Mage::getSingleton('core/resource')->getConnection('core_read'); $write = Mage::getSingleton('core/resource')->getConnection('core_write'); |
Step 2: Get the table names
Almost every SQL query will require the use of a table name. Magento tables can be prefixed, so it is important to not hardcode the table name. There are two methods to get a table name that will include the prefix:
Get the table name from an entity
|
1 |
$productTableName = Mage::getSingleton('core/resource')->getTableName('catalog/product'); |
Get the table name from a string
|
1 |
$productTableName = Mage::getSingleton('core/resource')->getTableName('catalog_product_entity'); |
Step 3: Reading from a table
Now that we have the read connection and table name we are ready to read from a table. It is oftentimes much faster to perform a direct read of a Magento database, than to the load the object property. In this example we will read the product table in get all products with their ID and SKU:
|
1 2 3 4 5 6 7 8 |
$read = Mage::getSingleton('core/resource')->getConnection('core_read'); $productTableName = Mage::getSingleton('core/resource')->getTableName('catalog/product'); $sqlQuery = sprintf('SELECT entity_id, sku FROM `%s`', $productTableName); $results = $read->fetchAll($sqlQuery); // return an array where each item contains the product ID and sku print_r($results); |
Step 4: Writing to a table
Just as direct reading can be faster, so can direct writing. Sometimes much faster. Here is a simple example of how to update the SKU of a product with the ID of 1:
|
1 2 3 4 5 |
$write = Mage::getSingleton('core/resource')->getConnection('core_write'); $productTableName = Mage::getSingleton('core/resource')->getTableName('catalog/product'); $sqlQuery = sprintf('UPDATE `%s` set SKU = "newsku" WHERE entity_id = 1', $productTableName); $write->query($sqlQuery); |
Conclusion
Direct reading / writing can be faster or more convenient than using Magento models. However, it is important to do it with cautsion especially when doing direct writing. Magento models ensure data integrity and allow triggers that do things like the Magento full page cache or Magento indexing. Directly writing can create unforeseen issues.