0

I am trying to find code found in a tutorial that would enable me to update all pricing per sku for a specific store id. I am wondering if row 151 the INSERT qyuery shouldn't that be an update query to update the price and not an insert of a new row into the catalog_product_entity_decimal table?

The error I am getting is ERROR:: While updating SKU (at0042-63) with Price (177) => SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (6l7tzyfknugxk_stg.catalog_product_entity_decimal, CONSTRAINT CAT_PRD_ENTT_DEC_ROW_ID_CAT_PRD_ENTT_ROW_ID FOREIGN KEY (row_id) REFERENCES catalog_product_entity (row_id) ON DELETE C), query was: INSERT INTO catalog_product_entity_decimal (attribute_id, store_id, row_id, value) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE value=VALUES(value)

Here is the file I am trying to execute:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Capture warning / notice as exception
set_error_handler('ctv_exceptions_error_handler');
function ctv_exceptions_error_handler($severity, $message, $filename, $lineno) {
if (error_reporting() == 0) {
    return;
}
if (error_reporting() & $severity) {
    throw new ErrorException($message, 0, $severity, $filename, $lineno);
}
}

require __DIR__ . '/../app/bootstrap.php';
$bootstrap = \Magento\Framework\App\Bootstrap::create(BP, $_SERVER);

$obj = $bootstrap->getObjectManager();

$state = $obj->get('Magento\Framework\App\State');
$state->setAreaCode('adminhtml');

function _mpLog($data, $includeSep = false)
{
  $fileName = BP . '/var/log/m2-import-prices.log';
  if ($includeSep) {
    $separator = str_repeat('=', 70);
    file_put_contents($fileName, $separator . '<br />' . PHP_EOL,  FILE_APPEND | LOCK_EX);
 }
file_put_contents($fileName, $data . '<br />' .PHP_EOL,  FILE_APPEND | LOCK_EX);

}

function mpLogAndPrint($message, $separator = false)
{
 _mpLog($message, $separator);
 if (is_array($message) || is_object($message)) {
    print_r($message);
} else {
    echo $message . '<br />' . PHP_EOL;
}

if ($separator) {
    echo str_repeat('=', 70) . '<br />' . PHP_EOL;
}

}

function getIndex($field)

{ global $headers; $index = array_search($field, $headers); if ( !strlen($index)) { $index = -1; } return $index; }

function readCsvRows($csvFile) { $rows = []; $fileHandle = fopen($csvFile, 'r'); while(($row = fgetcsv($fileHandle, 0, ',', '"', '"')) !== false) { $rows[] = $row; } fclose($fileHandle); return $rows; }

function _getResourceConnection() { global $obj; return $obj->get('Magento\Framework\App\ResourceConnection'); }

function _getReadConnection() { return _getConnection('core_read'); }

function _getWriteConnection() { return _getConnection('core_write'); }

function _getConnection($type = 'core_read') { return _getResourceConnection()->getConnection($type); }

function _getTableName($tableName) { return _getResourceConnection()->getTableName($tableName); }

function _getAttributeId($attributeCode) { $connection = _getReadConnection(); $sql = "SELECT attribute_id FROM " . _getTableName('eav_attribute') . " WHERE entity_type_id = ? AND attribute_code = ?"; return $connection->fetchOne( $sql, [ _getEntityTypeId('catalog_product'), $attributeCode ] ); }

function _getEntityTypeId($entityTypeCode) { $connection = _getConnection('core_read'); $sql = "SELECT entity_type_id FROM " . _getTableName('eav_entity_type') . " WHERE entity_type_code = ?"; return $connection->fetchOne( $sql, [ $entityTypeCode ] ); }

function _getIdFromSku($sku) { $connection = _getConnection('core_read'); $sql = "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?"; return $connection->fetchOne( $sql, [ $sku ] );

}

function checkIfSkuExists($sku) { $connection = _getConnection('core_read'); $sql = "SELECT COUNT(*) AS count_no FROM " . _getTableName('catalog_product_entity') . " WHERE sku = ?"; return $connection->fetchOne($sql, [$sku]); }

function updatePrices($sku, $price, $storeId = 28) { $connection = _getWriteConnection(); $entityId = _getIdFromSku($sku); $attributeId = _getAttributeId('price');

$sql = "INSERT INTO " . _getTableName('catalog_product_entity_decimal') . " (attribute_id, store_id, row_id, value) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE value=VALUES(value)";
$connection->query(
    $sql,
    [
        $attributeId,
        $storeId,
        $entityId,
        $price
    ]
);

}

// // UTILITY FUNCTIONS - END //

try { $csvFile = 'var/import/prices.csv'; #EDIT - The path to import CSV file (Relative to Magento2 Root) $csvData = readCsvRows(BP . '/' . $csvFile); $headers = array_shift($csvData);

$count   = 0;
foreach($csvData as $_data) {
    $count++;
    $sku   = $_data[getIndex('sku')];
    $price = $_data[getIndex('price')];

    if ( ! checkIfSkuExists($sku)) {
        $message =  $count .'. FAILURE:: Product with SKU (' . $sku . ') doesn\'t exist.';
        mpLogAndPrint($message);
        continue;
    }

    try {
        updatePrices($sku, $price);
        $message = $count . '. SUCCESS:: Updated SKU (' . $sku . ') with price (' . $price . ')';
        mpLogAndPrint($message);
    } catch(Exception $e) {
        $message =  $count . '. ERROR:: While updating  SKU (' . $sku . ') with Price (' . $price . ') => ' . $e->getMessage();
        mpLogAndPrint($message);
    }
}

} catch (Exception $e) { mpLogAndPrint( 'EXCEPTION::' . $e->getTraceAsString() ); }

1 Answer 1

0

Possibly, there are multiple/duplicate entries in the catalog_product_entity_decimal table. You should once check in that table and tried to delete that entry.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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