Magento – Magento 2 direct SQL query for insert not working

event-observermagento2querysql

I was trying to insert values into mlx_cot_custom_option_type_value because module files are encrypted. I want to add something after product save

I am using catalog_product_save_after event

Here is what I have tried.

    protected $_resource;

    public function __construct(
        \Magento\Framework\App\ResourceConnection $resource
    ) {
        $this->_resource = $resource;
    } 
    
    $this->connection = $this->_resource->getConnection('core_write');
    $option_id = 186;
    $valueTable = 'mlx_cot_custom_option_type_value';
    $table = $this->_resource->getTableName($valueTable); 
    
    echo $sql = "Insert Into " . $table . " (option_type_id, option_id, sku, sort_order,weight) Values ('','".$option_id."','".$value['sku']."','1','')";
    $result = $this->connection->query($sql);
    echo "<pre>";
    print_r($this->connection->lastInsertId());
    die;

Output is

Insert Into mlx_cot_custom_option_type_value (option_type_id, option_id, sku, sort_order,weight) Values ('','186','Custom-Create-Any-Song-in-a-Music-Box-New','1','')

2047

$this->connection->lastInsertId() give new increment id every time but I can not see any new entry into database

When I run above query using phpmyadmin it will create new entry

Best Answer

If you guys are trying to save an attribute to product through observer "catalog_product_save_after", then this can be help:
https://webkul.com/blog/mass-update-products-attribute-magento-2/

Related Solutions

Magento SQL Insert Query Not Working – No Errors Shown

You have a comma spare in your $orderQuery just before the closing ):

 ...
 `shipping_address_country_id`,
 )
 VALUES (:order_id, :order_number, ....

Than one comma needs to be removed:

$orderQuery = "insert into `neonandmore_timeclock`.`nam_orders` (
            `order_id` ,
            `order_number` ,
            `order_type` ,
            `customer_id` ,
            `sugarcrm_qms_id` ,
            `date_created` ,
            `date_modified` ,
            `order_status` ,
            `total_item_count` ,
            `order_shipping_price` ,
            `order_tax_price` ,
            `order_items_price` ,
            `order_grand_total_price` ,
            `order_shipping_tracking` ,
            `order_shipping_ship_date` ,
            `order_notes`,
            `billing_address_firstname`,
            `billing_address_lastname`,
            `billing_address_telephone`,
            `billing_address_email`,
            `billing_address_street`,
            `billing_address_city`,
            `billing_address_region`,
            `billing_address_postcode`,
            `billing_address_country_id`,
            `shipping_address_firstname`,
            `shipping_address_lastname`,
            `shipping_address_telephone`,
            `shipping_address_email`,
            `shipping_address_street`,
            `shipping_address_city`,
            `shipping_address_region`,
            `shipping_address_postcode`,
            `shipping_address_country_id`
             )
             VALUES (:order_id, :order_number, :order_type, :customer_id, :sugarcrm_qms_id, :date_created, :date_modified, :order_status, :total_item_count, :order_shipping_price, :order_tax_price, :order_items_price, :order_grand_total_price, :order_shipping_tracking, :order_shipping_ship_date, :order_notes, :billing_address_firstname, :billing_address_lastname, :billing_address_telephone, :billing_address_email, :billing_address_street, :billing_address_city, :billing_address_region, :billing_address_postcode, :billing_address_country_id, :shipping_address_firstname, :shipping_address_lastname, :shipping_address_telephone, :shipping_address_email, :shipping_address_street, :shipping_address_city, :shipping_address_region, :shipping_address_postcode, :shipping_address_country_id)";

Magento – How to run direct SQL query on magento and bind WHERE IN params

Why don't you just use the following:

Mage::getSingleton('catalog/product')->getIdBySku($productSku);

You can also get multiple products from a collection:

$collection = Mage::getSingleton('catalog/product')->getCollection();
$collection->addAttributeToFilter('sku', array('in' => array('sku1', 'sku2', ...)));

foreach ($collection as $product) {
  echo $product->getId();
}

If you want to keep the SQL approach then use something like this:

$resource = Mage::getSingleton('core/resource');
$readConnection = $resource->getConnection('core_write');

$productTable = $resource->getTableName('catalog/product');

$qry = $readConnection->select()->from($productTable)->where('sku=?', $productSku);
$rows = $readConnection->fetchAll($qry);

foreach ($rows as $row) {
...
}

This is just an example, so fix it with your logic.

For escape you can also use:

$readConnection->quote($myValueToEscape)

BUt you should not directly type SQL. Starting from Magento 1.6 they completely isolated the SQL layer, so you should only use the PDO calls.

Best Answer

Related Solutions

Magento SQL Insert Query Not Working – No Errors Shown

Magento – How to run direct SQL query on magento and bind WHERE IN params

Related Topic