Here we learn how to write custom mysql query in Magento2.
Suppose we have table ’employee’ with fields emp_id, emp_name, emp_code and emp_salary.
Now use following code snippet to run custom queries in magento2 without using model.
Custom Mysql Query
$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('employee'); //gives table name with prefix
//Select Data from table
$sql = "Select * FROM " . $tableName;
$result = $connection->fetchAll($sql); // gives associated array, table fields as key in array.
//Delete Data from table
$sql = "Delete FROM " . $tableName." Where emp_id = 10";
$connection->query($sql);
//Insert Data into table
$sql = "Insert Into " . $tableName . " (emp_id, emp_name, emp_code, emp_salary) Values ('','XYZ','ABD20','50000')";
$connection->query($sql);
//Update Data into table
$sql = "Update " . $tableName . " Set emp_salary = 20000 where emp_id = 12";
$connection->query($sql);
$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('employee'); //gives table name with prefix
//Select Data from table
$sql = "Select * FROM " . $tableName;
$result = $connection->fetchAll($sql); // gives associated array, table fields as key in array.
//Delete Data from table
$sql = "Delete FROM " . $tableName." Where emp_id = 10";
$connection->query($sql);
//Insert Data into table
$sql = "Insert Into " . $tableName . " (emp_id, emp_name, emp_code, emp_salary) Values ('','XYZ','ABD20','50000')";
$connection->query($sql);
//Update Data into table
$sql = "Update " . $tableName . " Set emp_salary = 20000 where emp_id = 12";
$connection->query($sql);
$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('employee'); //gives table name with prefix
//Select Data from table
$sql = "Select * FROM " . $tableName;
$result = $connection->fetchAll($sql); // gives associated array, table fields as key in array.
//Delete Data from table
$sql = "Delete FROM " . $tableName." Where emp_id = 10";
$connection->query($sql);
//Insert Data into table
$sql = "Insert Into " . $tableName . " (emp_id, emp_name, emp_code, emp_salary) Values ('','XYZ','ABD20','50000')";
$connection->query($sql);
//Update Data into table
$sql = "Update " . $tableName . " Set emp_salary = 20000 where emp_id = 12";
$connection->query($sql);
$resource = $objectManager->get(‘MagentoFrameworkAppResourceConnection’);
$connection = $resource->getConnection();
$tableName = $resource->getTableName(’employee’); //gives table name with prefix
$emp_name=’Rana Roy’;
$emp_code=’123′;
$emp_salary=’5000′;
$emp_desc=’Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry’s standard dummy text ever since the 1500s’;
//Insert Data into table
$sql = “Insert Into ” . $tableName . ” (emp_id, emp_name, emp_code, emp_salary,emp_desc) Values (”,’$emp_name’,’$emp_code’,’$emp_salary’,’$emp_desc’)”;
$connection->query($sql);
its not working coz emp_desc there s spacial character is there “industry’s”. please suggest me how to solve this issue with spacial character