Read More
Read More
Menu Close

    Magento2 – Write Custom Mysql Query (Without Using Model)

    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.
    Custom Mysql Query

    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);

    Searching for an experienced
    Magento 2 Company ?
    Read More
    . . .
    Discuss on Helpdesk

    Leave a Comment

    Your email address will not be published. Required fields are marked*


    8 comments

  • Pranab Natta
    $objectManager = MagentoFrameworkAppObjectManager::getInstance(); // Instance of object manager
    $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

    • Webkul Support

      Hello Pranab,

      If you are getting issue due to single quote then you can wrap your string in double quotes and it will work fine.
      For Example-
      $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’;
      use following
      $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”;

      Also use double quotes instead for single quote in sql query.

      Hope it will help you.
      if you have any issue or query let me know.

      Thanks

      Rahul Mahto

  • @limonazzo
    Thanks, also
    $connection = $resource->getConnection(); // isa PDO instance so you can do this:
    $sql = “INSERT INTO `usuario`(‘nombre’) VALUES (:nombre);
    $stmt = $connection->prepare($sql);
    $stmt->bindValue(“:nombre”,’Limón’);
    $stmt->execute();
  • Shoaib Munir
    Thanks alot man (Y)
    • Webkul Support
      You are most welcome.
  • Raj Kumar
    Where to place it?
  • Sachin S
    I want to insert data using my Observer for catalog_product_save_after event..please help?
  • sbckarthi
    works like charm………….
  • Back to Top