3

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?

4 Answers 4

12

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);
4
  • where should i insert it actually in InstallSchema.php ? after creating my custom table?
    – Vigna S
    Commented Sep 28, 2016 at 4:28
  • 1
    @Rushvi i got a warning "Possible raw SQL statement \"INSERT INTO \" detected." How to solve it.. Commented Sep 7, 2017 at 7:11
  • is this not open to SQL injection if VALUES() are $_POST/$_GET?
    – treyBake
    Commented Dec 20, 2018 at 9:11
  • Do not use this answer. This is an unsecure bad habit. Commented Dec 20, 2022 at 23:59
3

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.

1
1

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);
                }

            }
        }
    }
}
3
  • Hey @CarComp, i am trying to use similar way to save data from Observer, could you please help me
    – Sushivam
    Commented Aug 22, 2016 at 9:59
  • ANd what is customer_address here?
    – Sushivam
    Commented Aug 22, 2016 at 10:09
  • customer_address is the type. I didn't use the magento global. You can use any entity model name, even ones you create yourself.
    – CarComp
    Commented Aug 24, 2016 at 14:23
0

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
    )
  1. Get the connection object
    $this->conn = $resource->getConnection();
  1. 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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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