Magento 2: Run Custom SQL Query
This article shows how to write/run/execute custom SQL queries in Magento 2. We need to instantiate class Magento\Framework\App\ResourceConnection for this. You have to inject this resource class in your module’s Block/Model/Controller class constructor. After that, you can use that object to run custom sql queries.
In this example, I will simply be using object manager to instantiate the resource class.
Suppose, I have a table named mytest with fields id, name, age, & email.
1 2 3 4 5 6 7 | CREATE TABLE IF NOT EXISTS `mytest` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `age` tinyint(3) NOT NULL, `email` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; |
The following example code will insert, update, delete and select records from the table mytest.
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 | $objectManager = \Magento\Framework\App\ObjectManager::getInstance(); $resource = $objectManager->get('Magento\Framework\App\ResourceConnection'); $connection = $resource->getConnection(); $tableName = $resource->getTableName('mytest'); // the table name in this example is 'mytest' // INSERT DATA $name = 'Mukesh Chapagain'; $age = 99; $email = 'mukesh@example.com'; $sql = "INSERT INTO " . $tableName . " (id, name, age, email) VALUES ('', '$name', $age, '$email')"; $connection->query($sql); $sql = "INSERT INTO " . $tableName . " (id, name, age, email) VALUES ('', 'Hello World', 88, 'hello@example.com')"; $connection->query($sql); // SELECT DATA $sql = "SELECT * FROM " . $tableName; $result = $connection->fetchAll($sql); echo '<pre>'; print_r($result); echo '</pre>'; // UPDATE DATA $id = 1; // table row id to update $sql = "UPDATE " . $tableName . " SET name = 'Your Name', email = 'your-email@example.com' WHERE id = " . $id; $connection->query($sql); // DELETE DATA $id = 1; // table row id to delete $sql = "DELETE FROM " . $tableName . " WHERE id = " . $id; $connection->query($sql); |
In the above code, when we run the select query using fetchAll function, the following output is obtained. It returns a multi-dimentional array with table field name and value.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | Array ( [0] => Array ( [id] => 1 [name] => Mukesh Chapagain [age] => 99 [email] => mukesh@example.com ) [1] => Array ( [id] => 2 [name] => Hello World [age] => 88 [email] => hello@example.com ) ) |
Instead of writing the whole select query by ourselves, we can also use the SQL SELECT Query generation functions that are already provided by Magento and Zend_Db_Select. Here’s an example code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $objectManager = \Magento\Framework\App\ObjectManager::getInstance(); $resource = $objectManager->get('Magento\Framework\App\ResourceConnection'); $connection = $resource->getConnection(); $tableName = $resource->getTableName('mytest'); // the table name in this example is 'mytest' // SELECT DATA $id = 2; $fields = array('id', 'name'); $sql = $connection->select() ->from($tableName) // to select all fields //->from($tableName, $fields) // to select some particular fields ->where('id = ?', $id) ->where('age > ?', 30); // adding WHERE condition with AND $result = $connection->fetchAll($sql); echo '<pre>'; print_r($result); echo '</pre>'; |
You can see in detail about this in class Magento\Framework\DB\Select present in vendor/magento/framework/DB/Select.php.
/**
* Class for SQL SELECT generation and results.
*
* @method \Magento\Framework\DB\Select from($name, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select join($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinInner($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinLeft($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinNatural($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinFull($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinRight($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinCross($name, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select orWhere($cond, $value = null, $type = null)
* @method \Magento\Framework\DB\Select group($spec)
* @method \Magento\Framework\DB\Select order($spec)
* @method \Magento\Framework\DB\Select limitPage($page, $rowCount)
* @method \Magento\Framework\DB\Select forUpdate($flag = true)
* @method \Magento\Framework\DB\Select distinct($flag = true)
* @method \Magento\Framework\DB\Select reset($part = null)
* @method \Magento\Framework\DB\Select columns($cols = ‘*’, $correlationName = null)
*
*/
Note: If you are running your script in custom external file then you need to write the following code to instantiate ObjectManager. Otherwise, you might get the following error:
Fatal error: Class ‘Magento\Framework\App\ObjectManager’ not found
So, here is the solution to it. In the code below, I have selected data from table eav_entity_type using custom sql script.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | use Magento\Framework\App\Bootstrap; //require __DIR__ . '/app/bootstrap.php'; // you can use this if your custom file is in your magneto root directory $rootDirectory = '/var/www/html/magento2'; // YOUR MAGENTO ROOT DIRECTORY require $rootDirectory . '/app/bootstrap.php'; $params = $_SERVER; $bootstrap = Bootstrap::create(BP, $params); $objectManager = $bootstrap->getObjectManager(); $resource = $objectManager->get('Magento\Framework\App\ResourceConnection'); $connection = $resource->getConnection(); $tableName = $resource->getTableName('eav_entity_type'); // accessing table 'eav_entity_type' // SELECT DATA $sql = "SELECT * FROM " . $tableName; $result = $connection->fetchAll($sql); echo '<pre>'; print_r($result); echo '</pre>'; |
Hope this helps. Thanks.
Find me on
Mukesh Chapagain is a graduate of Kathmandu University (Dhulikhel, Nepal) from where he holds a Masters degree in Computer Engineering. Mukesh is a passionate web developer who has keen interest in open source technologies, programming & blogging. 
