Our Guide to Run Direct SQL Query in Magento 2
The developers use models to access as well as modify the data in the Magento 2 database. However, sometimes, the project requirements demand to communicate with the database using custom SQL queries.
This post helps to run direct SQL query in Magento 2.
Also Check: Read CSV File Using Root Script in Magento 2
Learn the easy method to write, execute/run the direct SQL query as shown below:
Method to run direct SQL Query in Magento 2:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
<?php namespace [Vendor]\[Module]\Helper; use Magento\Framework\App\Helper\AbstractHelper; use Magento\Framework\App\Helper\Context; use Magento\Framework\App\ResourceConnection; use Magento\Framework\App\ObjectManager; class Data extends AbstractHelper { protected $resourceConnection; public function __construct(Context $context, ResourceConnection $resourceConnection) { $this->resourceConnection = $resourceConnection; parent::__construct($context); } public function runSqlQuery($table) { $connection = $this->resourceConnection->getConnection(); // $table is table name $table = $connection->getTableName('my_custom_table'); //For Select query $query = "Select * FROM " . $table; $result = $connection->fetchAll($query); $this->_logger->log(print_r($result, true)); $id = 2; $query = "SELECT * FROM `" . $table . "` WHERE id = $id "; $result1 = $connection->fetchAll($query); $this->_logger->log(print_r($result1, true)); //For Insert query $tableColumn = ['id', 'name', 'age']; $tableData[] = [5, 'xyz', '20']; $connection->insertArray($table, $tableColumn, $tableData); $query = "INSERT INTO `" . $table . "`(`id`, `name`, `age`) VALUES (7,'mtm',33)"; $connection->query($query); // For Update query $id = 1; $query = "UPDATE `" . $table . "` SET `name`= 'test' WHERE id = $id "; $connection->query($query); $query1 = "UPDATE `" . $table . "` SET `name`= 'test', `age` = 14 WHERE id = $id "; $connection->query($query1); // For Delete query $id = 1; $query = "DELETE FROM `" . $table . "` WHERE id = $id "; $connection->query($query); } } |
Any doubts about the topic or implementation? Please mention them in the Comments section below. I’d be happy to help.
Feel free to share the post with the Magento community via social media.
Thank you.
Still need help? Hire our Adobe-certified Magento experts.
×
5
(based on 14 Reviews)
![Meetanshi Magento Extensions Launches and Updates April [2020]](png/meetanshi-magento-extensions-launches-and-updates-april-2020-150x150.png)
![Magento 2.3.5: All About The Latest Version [April 28, 2020]](png/magento-2.3.5-all-about-the-latest-version-april-28-2020.png)
6 Comments
Would you be able to how us how to do this while working with foreign keys?
Hello Lucio,
SQL query can work with primary and foreign keys. You need to create queries according to your needs.
Thank You.
Thanks for this, saved a lot of processing time when updating multiple records.
Happy to help 🙂
very usefull thank you
Hello Hemang, Thank you for the appreciation 🙂