How to Insert Data in magento 2 database table programatically

Question

We want to insert data in database table. My site is in magento 2. Any one help me for insert data in database table.

namespace Agtech\Productstockupdate\Block\Index;
class Index extends \Magento\Framework\View\Element\Template {
protected $productFactory;
public function __construct(
 \Magento\Catalog\Block\Product\Context $context,
 \Magento\Catalog\Model\ProductFactory $productFactory, 
 array $data = []) {
 $this->productFactory = $productFactory;
 parent::__construct($context, $data);
}
protected function _prepareLayout()
{
 return parent::_prepareLayout();
}
public function updateStock($product_sku,$product_QTY)
{
 $product = $this->productFactory->create()->load($productId);
 if($product->getStatus() == 2){ // 2 => Disable , 1 => Enable
 $product->setStatus(1);
 if($product->getStatus() == 1){
 $product->setStockData([
 'is_in_stock' => 1, 
 'qty' => $productQty 
 ]);
 $product->setQuantityAndStockStatus([
 'qty' => $productQty,
 'is_in_stock' => 1
 ]); 
 }
 $product->setStatus(2);
 try {
 $product->save(); 
 echo 'Quantity update of SKU => '.$product->getSku().'<br/>'; 
 } catch (Exception $e) {
 echo $e->getException();
 }
 }else{
 $product->setStockData([
 'is_in_stock' => 1,
 'qty' => $productQty
 ]);
 $product->setQuantityAndStockStatus([
 'qty' => $productQty,
 'is_in_stock' => 1
 ]); 
 try {
 $product->save(); 
 echo 'Quantity update of SKU => '.$product->getSku().'<br/>'; 
 } catch (Exception $e) {
 echo $e->getException();
 }
 }
}
}

• Vivek Kumar: In which table you want to insert that data?
• Anil: Any new table create in database and insert in it. we want two columns.
• Vivek Kumar: Magento uses CRUD models to insert data, you can read up on it here - mageplaza.com/magento-2-module-development/…

Answer 1

We can insert the data by following way:

namespace YourNamespace\YourModule\Controller;
 
use Magento\Framework\App\Action\Context;
use Magento\Framework\Message\ManagerInterface;
use Magento\Framework\App\ResourceConnection;
 
class YourController extends \Magento\Framework\App\Action\Action
{
 /**
 * @var \Magento\Framework\Message\ManagerInterface
 */
 protected $messageManager;
 
 /**
 * @var \Magento\Framework\App\ResourceConnection $resource
 */
 protected $resource;
 
 protected $connection;
 
 public function __construct(
 Context $context,
 ManagerInterface $messageManager,
 ...
 ...
 ResourceConnection $resource
 )
 {
 $this->messageManager = $messageManager;
 ...
 ...
 $this->resource = $resource;
 $this->connection = $resource->getConnection();
 
 parent::__construct($context);
 }
 public function execute() {
 .....................
 .....................
 $this->yourCustomFunction();
 .....................
 .....................
 }
 
 public function insertMultiple($table, $data)
 {
 try {
 $tableName = $this->resource->getTableName($table);
 return $this->connection->insertMultiple($tableName, $data);
 } catch (\Exception $e) {
 $this->messageManager->addException($e, __('Cannot insert data.'));
 }
 }
 
 public function yourCustomFunction()
 {
 try {
 $yourData = [
 ['id' => 1, 'name' => 'John', 'age' => 22],
 ['id' => 2, 'name' => 'Mary', 'age' => 23],
 ['id' => 3, 'name' => 'Sam', 'age' => 24]
 ];
 
 $tableName = 'your_table_name';
 $this->insertMultiple($tableName, $yourData);
 
 $this->messageManager->addSuccess( __('Successfully inserted data.') );
 } catch (Exception $e) {
 $this->messageManager->addException($e, __('Cannot save data.'));
 }
 }
}

Hope this helps. Thanks!

• Faisal Sheikh: @Anil Here you want to save the data by product id or product sku?
• Anil: here is our custom data; not magento default. If you want any name change so dont worry.
• Faisal Sheikh: @Anil If you want to insert data into custom table then you can use my way, please try it.
• Anil: Is these use with Block file ?
• Faisal Sheikh: No, here I am doing this with controller file.