Magento 2: How to insert data into custom table in model file?

Question

Magento version: 2.0

I created a table to store some data. but maybe I have to use many file to do it from some tutorials, such as model, resource and collection folder's files. I just save, update or delete the data into table, don't need to many works with it. So, there is simple method to do it?

Answer 1

If you wants to insert data you can try below way,

Insert Custom Query.

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

$themeTable = $this->_resources->getTableName('yourtablename');
$sql = "INSERT INTO " . $themeTable . "(field1, field2) VALUES ('1', 'NameABC')";
$connection->query($sql);

Answer 2

You should leverage the ORM available and not write SQL queries directly, it's bad practice.

At minimum you need to create ResourceModel and use it to load/save model.

Answer 3

Here is an entire Observer with an example on how to write data to the database.

<?php

namespace Company\Customer\Observer {

    use Magento\Framework\Event\ObserverInterface;


    class AddressSave implements ObserverInterface {

        protected $resourceConnection;
        protected $eavAttributeFactory;

        public function __construct(
            \Magento\Framework\App\ResourceConnection $resourceConnection,
            \Magento\Eav\Model\Entity\AttributeFactory $eavAttributeFactory) {
            $this->resourceConnection = $resourceConnection;
            $this->eavAttributeFactory = $eavAttributeFactory;

        }

        public function execute(\Magento\Framework\Event\Observer $observer)
        {

            $event = $observer->getEvent();
            $address = $event->getDataByKey('customer_address');

            if (!$address == null && $address->getId() > 0) {
                $uri = 'https://blahblah.azurewebsites.net/api/AddressUpdate?code=poj3bkwn1al9hfoljw1c92j4idegetujxow967aw1ukm3zyqfr4vzke8qrircnf24pdf6dh1tt9';

                // We use Zend_Json for encoding because it can handle the magento data natively
                $options = array(
                    'http' => array(
                        'method'  => 'POST',
                        'content' => \Zend_Json::encode($address->getData(), false, []),
                        'header'=>  "Content-Type: application/json\r\n" . "Accept: application/json\r\n"
                    )
                );

                $context  = stream_context_create( $options );
                $results = file_get_contents($uri, false, $context);

                // If the address does not contain a cxa key, save it from the results
                if (!$address->getData('cxa_key')) {
                    $decRes = json_decode($results);

                    $eva = $this->eavAttributeFactory->create();
                    $cxaCodeId = $eva->loadByCode('customer_address','cxa_key')->getAttributeId();

                    $themeTable = $this->resourceConnection->getTableName('customer_address_entity_text');
                    $sql = "INSERT INTO " . $themeTable . "(attribute_id, entity_id, value) Values ('" . $cxaCodeId . "', '". $address->getId() ."', '" . $decRes->Id . "' )";
                    $connection = $this->resourceConnection->getConnection()->query($sql);
                }

            }
        }
    }
}

Answer 4

If you want to use queries I wouldn't use pure SQL as you can't always sanitize your values that way and there's a risk of SQL injection. Instead, you can use Magento objects to make them.

1. In a constructor of your model class inject Magento\Framework\App\ResourceConnection

    public function __construct(
     Magento\Framework\App\ResourceConnection $resource
    )

2. Get the connection object

    $this->conn = $resource->getConnection();

3. Make an insert query

    $table_name = $this->conn->getTableName("custom_table");
    $new_data = [
      "key" => "my_custom_field",
      "value" => $custom_field_value
    ];
    $this->conn->insert($table_name, $new_data);

*) You can also write an update query that way

    $table_name = $this->conn->getTableName("custom_table");
    $new_data = [
      "key" => "my_custom_field",
      "value" => $custom_field_value
    ];
    $where = ["entity_id = ?" => $entity_id];
    $this->conn->update($table_name, $new_data, $where);

*) Or select query:

    $sql = "SELECT * FROM `some_table` WHERE `name_col` = :name";
    $bind = [
      ":name" => $name
    ];
    $result = $this->conn->query($sql, $bind);