This tutorial mainly demonstrates direct SQL queries in Magento. Magento data models are giving great flexibility for the data processing. We don’t need to touch the complex SQL statements because of this feature. This often gives really quick development speed and accuracy more over a bulletproof approach. But several situations it much needed to execute a direct SQL query in order to update some product attributes. suppose if you want to update a customer attribute you want to loop through huge customer model. Here comes the importance of direct SQL queries in Magento( personally I don’t recommend this approach). These magento query building technique helping you to getting your hand dirty with some raw SQL queries in Magento
Basically, it’s providing two resources core_read and core_write to connect to the database. As the name says core_read for reading operations while core_write is for write operations.
Table names and table prefixes
You may be noticed that Magento using table prefix option while installing Magento. It’s nothing but a short string to identify the tables of our application if a large number of tables are sharing with other applications.
Get a table name from a string
Get a table name from an entity name
Read From The Database
It returns the first column from each result row.
Note that this method is used for fetch one row in magneto direct SQL.
Unlike the previous two methods, Varien_Db_Select::fetchOne returns one value from the first row only. This value is returned on its 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. It’s just returning the selected field as output, unlike others it won’t wrap in an array.
Writing To The Database
When saving a model this can take a lot of database resource, by using direct SQL query we are saving that time and DB resources.
Note: Be careful while updating/inserting a value into Magento database as its heavily normalized and using EAV architectures. Sometimes it is necessary to execute direct SQL queries in Magento. Even a small error will cause more serious damages to your database.