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.
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.
|
1
2
3
4
5
6
7
8
9
|
<?php$resource = Mage::getSingleton('core/resource');$tableName = $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
|
1
2
3
4
|
<?php$read = Mage::getSingleton('core/resource')->getConnection('core_read');$write = Mage::getSingleton('core/resource')->getConnection('core_write'); |
The getConnection() function takes the name of the resource you're trying to request as a parameter and returns an object of Varien_Db_Adapter_Pdo_Mysql. The class extends from Zend_Db_Adapter_Abstract and therefore you allows you to perform all Zend DB Adapter's functions on it.
For a list of functions available, copy the following code into a Magento template.
|
1
2
3
4
5
6
7
8
9
10
|
<?php$read = Mage::getSingleton('core/resource')->getConnection('core_read');echo '<pre>';print_r(get_class_methods($read));echo '</pre>';exit;?> |
This should produce a list of available functions and will help illustrate just exactly what you can do using this object.
Reading data from the database
|
1
2
3
4
5
6
7
|
<?php$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); |
If you have any products in your Magento store, the above code will print out data corresponding to the core product entities. Try modifying the query to access other pieces of data.
Writing information to the database
|
1
2
3
4
5
6
7
8
9
|
<?php$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); |
Add your own query into the query variable above and run the code. If your query is valid SQL then it should be executed correctly and data changed however your query instructed.
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!
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. For more information on this, see the following link:
good article ;) thanks ! if you are interested in learning how to create a custom magento module wich interact with the database you can look at http://www.about-magento.com/magento-model-database-tutorial-54 and go deeper in magento ;) I hope to talk with you soon, Pierre.