:::: MENU ::::
Posts tagged with: sql query

Magento 2 use custom query

Today we will discuss how in Magento 2 custom query works. Sometime magento 2 model is not enough to get data from database as per our requirements. So to fulfill this requirement we use custom query. In this example we will use select custom query, custom delete query, custom insert query and custom update query. So let’s start with example.

Get connection

First of all we will establish a connection to execute the query.

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

Insert Query

It is simple to use insert query just set the values for columns. Use the inset method.

$array=array(‘column1’=>'column1','column2'=>'column2');

$connection->insert($this->_resources->getTableName(test_table), $array);

Select Query

Selecting table data in custom query you can use following code.

$select = $connection->select()->from(['o' =>  $this->_resources->getTableName(test_table)]);
$result = $connection->fetchAll($select);

       foreach ($result as $data) {
           echo $data['id'];
            }

Update Query

Updating table data in custom query you can use following code.

$id = 1;
 $sql = "Update " . $this->_resources->getTableName('test_table') . " Set `title` ='test title' where `id` = ".$id;
     $connection->query($sql);

Delete Query

To delete table data in custom query you can use following code.

$id = 1;
$condition = $this->getConnection()->quoteInto('id = ?', $id);
            $this->getConnection()->delete($this->getTable('test_table'), $condition);

Join Table

To join table in custom query you can use following code.

        $select = $connection->select()->from(['o' =>  $this->_resources->getTableName('test_table')])->joinleft(
        ['test_table1' => $this->_resources->getTableName('test_table1')],
        'o.id = test_table1.id',
        ['column1','column2']
    );
Author: Qaisar Satti
Category: Magento 2
Last Modified: July 12, 2018


Magento 2 reset admin password

Today we discuss how in Magento 2 reset admin password. This topic include how to reset admin password with sql query, reset password through command line interface (cli). Sometime you forgot the admin password so today we discuss how to reset your admin password. There are two ways to do that. First go to database and reset your with sql query and second create new user from command line and then login and reset the password from admin panel.

Reset with Query

Change test123 to your required password.

UPDATE `admin_user` SET `password` = CONCAT(SHA2('xxxxxxxxtest123', 256), ':xxxxxxxx:1') WHERE `username`= 'admin'

Reset From Cli

Create new user and login to admin panel and change you old user password.

<path_to_magento> php bin/magento admin:user:create --admin-user="admin" --admin-password="123123q" --admin-email="[email protected]" --admin-firstname="Admin" --admin-lastname="Admin"
Author: Qaisar Satti
Category: Magento 2
Last Modified: July 12, 2018