Tag: MYSQL

You can run a direct SQL query for fetching only first value using fetchOne() method.

fetchOne() Fetches the first column of the first row of the SQL result as output.

You can use direct SQL query for the fetch the first column of the first row using below way, Get Product id by Product SKU,

<?php
class UpdateQuery {
   public function __construct(
       \Magento\Framework\App\ResourceConnection $resource
   ) {
       $this->resource = $resource;
   }

   /**
    * Update Sql Query
    */
   public function runUpdateQuery()
   {
      $connection  = $this->resource->getConnection();
      $tableName = $connection->getTableName("catalog_product_entity");
      
      $select = $connection->select()->from($tableName, 'entity_id')->where('sku = :sku');
      $sku = "24-MB01";
      $bind = [':sku' => (string)$sku];

      return (int)$connection->fetchOne($select, $bind);
  }
}

Output is 1. //entity_id for SKU 24-MB01

Check for other Direct SQL Query in Magento 2

Run Update SQL query in a given database table by Magento 2 in a standard way without the use of Object Manager.

You can write an update MySQL query in the ResouceModel PHP class.

When you write any custom update query, You need to create a function in the ResourceModel folder PHP file.
Continue reading “How to write Mysql Update database query using Magento 2?”

Use of insertOnDuplicate query using Magento 2,  You need to create a Connection object first to run the query using ResourceConnection class.

When you insert any new row into a table if the row causes a duplicate in the Primary key or UNIQUE index, throw an error in MySQL.

Base Definition of function:

/**
 * Inserts a table row with specified data.
 *
 * @param mixed $table The table to insert data into.
 * @param array $data Column-value pairs or array of column-value pairs.
 * @param array $fields update fields pairs or values
 * @return int The number of affected rows.
 */
public function insertOnDuplicate($table, array $data, array $fields = []);

Example,
Continue reading “How to use insertOnDuplicate query in Magento 2.”