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.

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.

$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.

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:

$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.

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.