2

In ResourceModel model I need to execute update query.

$sql = "UPDATE 2067_ci_sessions SET data='$cisess_data' WHERE id= $id";

instead of writing direct mysql query how can i write update query in magento 2 format?. Thanks in advance for your valuable answer

public function clearMlmSession($cisess_data, $cisess_cookie)
{   

    $table = "2067_ci_sessions";
    $bind = ['id' => $cisess_cookie];

    //For user information
    //select query
    $connection = $this->getConnection();
    $select = $connection->select()->from(
        $table,
        ['data']
    )->where(
        'id = :id'
    )->limit(
        1
    );
    $cisess_data = $connection->fetchRow($select, $bind);
    //$cisess_data = decode_session_data($cisess_data);
    if (empty($cisess_data)) {
        return false;
    }
    //select query


    //Update Data into table
    $sql = "Update " . $table . " Set data = '$cisess_data' where id = '$cisess_cookie'";
    $result = $connection->exec($sql);
    //$result = $connection->exec($sql);

    print_r($result); die("debug");
}

In this function select working correctly like that i need write update, i wrote my update query directly. how to write this update query in magento 2 format?

4
  • If your ResourceModel is extending \Magento\Framework\Model\ResourceModel\Db\AbstractDb, why you can't simply use the default save() function?
    – PY Yick
    Jan 15, 2018 at 5:12
  • Am new in magento 2. So i searched a lot for update but i didnt get what i need.
    – Midlaj
    Jan 15, 2018 at 5:22
  • So actually you should clarify what tasks you want to perform. I don't think writing query inside resourcemodel is a good idea.
    – PY Yick
    Jan 15, 2018 at 5:33
  • I edited my question please checkit. do u undesrtand what i mean?
    – Midlaj
    Jan 15, 2018 at 5:48

5 Answers 5

16

OK now I get your point. It's legistimate to use update SQL.

According to the Github belongs to Marius, this is the SQL way:

$this->getConnection()->update(
    $object->getResource()->getTableName($table),
    $cisess_data,
    ['id = ?' => (int)$cisess_cookie]
);

Replace the above code after the comment //Update Data into table.

BTW @Piyush, never use Object instance :)

2
  • Please accept my answer too :)
    – PY Yick
    Jan 15, 2018 at 9:02
  • How to write this query in the form of above format update eav_attribute_set set attribute_set_name = replace(attribute_set_name, '_src', '') where attribute_set_name LIKE '%_src%'; Mar 18, 2020 at 6:13
6

You can write like

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); 
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('employee'); //gives table name with 
 prefix
//Update Data into table
$sql = "Update " . $tableName . "Set emp_salary = 20000 where emp_id = 12";
$connection->query($sql);
1
2

Please try this:

$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()
                    ->get('Magento\Framework\App\ResourceConnection');
        $connection= $this->_resources->getConnection();

// select query
$Table = $this->_resources->getTableName('table_name');
$read = "Select * FROM ". $Table;
$result = $connection->fetchAll($read);

// update query
$write = "Update " . $Table . "Set name = 'test'";
$connection->query($write);
2
0

For single record

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); 
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('custom_table'); //gives table name with 
 prefix
//Update Data into table
$sql = "Update " . $tableName . "Set qty = '123' where id = '2'";
$connection->query($sql);

For multiple records

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); 
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();

$updateData = [1 => 7, 2 =>15, 3 => 19, 4=>3];
$insertData = [];
foreach($updateData as $id => $qty) {
$insertData[] = ['id' => $id, 'qty' => $qty];
}
try {
    $connection->beginTransaction();
    $connection->insertMultiple($this->getTable('custom_table'), $insertData);
    $connection->commit();
} catch(\Exception $e) {
    $connection->rollBack();
}
1
0

The proper way:

assuming:

  • $this->resourceConnection is instance of Magento\Framework\App\ResourceConnection
  • $this->connection is $resourceConnection->getConnection(ResourceConnection::DEFAULT_CONNECTION)
  • $cisess_data = 'THIS_IS_CISESS_DATA'
  • $id = 42

Build up a special select:

        $select = $this->connection->select()
            ->from(
                false,
                ['data' => $cisess_data]
            )
            ->where('id = ?', $id);

please note that we did not select any table, we are using columns to pass thru field-value pairs, and using the conditions required to the update

translate above select as update query:

        $updateSelect = $this->connection->updateFromSelect(
            $select,
            $this->resourceConnection->getTableName('2067_ci_sessions')
        );

this will result the proper UPDATE command as per

UPDATE 2067_ci_sessions SET data='THIS_IS_CISESS_DATA' WHERE id= 42;

at this point you already can run the query

        $this->connection->query($updateSelect);

or if you like one liners:

$this->connection->query(
    $this->connection->updateFromSelect(
        $this->connection->select()
            ->from(
                false,
                ['data' => $cisess_data]
            )
            ->where('id = ?', $id),
        $this->resourceConnection->getTableName('2067_ci_sessions')
    )
);

and the reason why this is the proper way:

with simple $this->getConnection()->update() you cannot join other table to the update. With this way, you can. Just extend original select with join, be sure that you using aliases for all tables (during running updateFromSelect() as well) and you are golden

see https://github.com/magento/magento2/blob/2.3.7-p2/app/code/Magento/Cron/Model/ResourceModel/Schedule.php#L77

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.