Magento 2: Create Custom Query in magento

Magento 2: Create Custom Query

Now You are thinking How we will get write the core Query in Magento.

Let’s Start. Here We give the example of insert, update, delete, select.

1. Insert

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

$themeTable = $this->_resources->getTableName('theme');
$sql = "Insert into " . $themeTable . "(theme_id,theme_path) Values (" . $themeId . ",'webmull/christmastheme' )";
$connection->query($sql);

$themeId=3;

$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()

->get('Magento\Framework\App\ResourceConnection');

$connection= $this->_resources->getConnection();

$themeTable = $this->_resources->getTableName('theme');

$sql = "Insert into " . $themeTable . "(theme_id,theme_path) Values (" . $themeId . ",'webmull/christmastheme' )";

$connection->query($sql);

2. Update

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

$themeTable = $this->_resources->getTableName('theme');
$sql = "Update FROM " . $themeTable . " set theme_path='webmull/christmastheme' WHERE theme_id = " . $themeId . ";";
$connection->query($sql);

$themeId=3;

$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()

->get('Magento\Framework\App\ResourceConnection');

$connection= $this->_resources->getConnection();

$themeTable = $this->_resources->getTableName('theme');

$sql = "Update FROM " . $themeTable . " set theme_path='webmull/christmastheme' WHERE theme_id = " . $themeId . ";";

$connection->query($sql);

3. Delete

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

$themeTable = $this->_resources->getTableName('theme');
$sql = "DELETE FROM " . $themeTable . " WHERE theme_id = " . $themeId . ";";
$connection->query($sql);

$themeId=3;

$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()

->get('Magento\Framework\App\ResourceConnection');

$connection= $this->_resources->getConnection();

$themeTable = $this->_resources->getTableName('theme');

$sql = "DELETE FROM " . $themeTable . " WHERE theme_id = " . $themeId . ";";

$connection->query($sql);

4. Select

$themeId=3;

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

$themeTable = $this->_resources->getTableName('theme');
$sql = "Select * FROM " . $themeTable . " WHERE theme_id = " . $themeId . ";";
$connection->query($sql);

$themeId=3;

$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()

->get('Magento\Framework\App\ResourceConnection');

$connection= $this->_resources->getConnection();

$themeTable = $this->_resources->getTableName('theme');

$sql = "Select * FROM " . $themeTable . " WHERE theme_id = " . $themeId . ";";

$connection->query($sql);

Hope, This Will Work in your system

3 Comments

manish April 4, 2016 at 11:20 am

please can you tell here _resources object of which class or interface ??
Spipu April 22, 2016 at 11:49 am

What ?! it is a very bad practice to write by yourself the queries !!!

you MUST use this :

$this->getConnection()->select()>-from(…)->where(…)

and you have to do this ONLY on resource model, and nowhere else !
Jhe June 24, 2016 at 6:11 pm

Can you help me to create a custom form in frontend like: Name, Age, Gender, Address and when the users click the submit button, all data will be save in your custom table

Comments are closed.