I am using a script to update prices. So far it gets the data from a CSV and uses this to look up in the database and updates the products with the information form the CSV for that row.
The problem I am having is that the SKU's in the CSV don't match exactly to what they are in Magento. For example in the CSV there may be 1234 however in magento the products that need to update with the info on this row would be 001234_01 and 001234_02 and 001234_03 and so on.
So I have added a LIKE search to get all the results from Magento that are like the SKU for that row in the CSV.
The problem is that it only finds one updates it and then moves onto the next row. So it will update 001234_01 but I need it to also update 001234_02 and 001234_03 before moving onto the next row.
I think I will need to tweak the _updatePrices to kind of foreach through but I cant seem to get it to work.
$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app('admin');
Mage::register('isSecureArea', 1);
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
set_time_limit(0);
ini_set('memory_limit','1024M');
/***************** UTILITY FUNCTIONS ********************/
function _getConnection($type = 'core_read'){
return Mage::getSingleton('core/resource')->getConnection($type);
}
function _getTableName($tableName){
return Mage::getSingleton('core/resource')->getTableName($tableName);
}
function _getAttributeId($attribute_code = 'price'){
$connection = _getConnection('core_read');
$sql = "SELECT attribute_id
FROM " . _getTableName('eav_attribute') . "
WHERE
entity_type_id = ?
AND attribute_code = ?";
$entity_type_id = _getEntityTypeId();
return $connection->fetchOne($sql, array($entity_type_id, $attribute_code));
}
function _getEntityTypeId($entity_type_code = 'catalog_product'){
$connection = _getConnection('core_read');
$sql = "SELECT entity_type_id FROM " . _getTableName('eav_entity_type') . " WHERE entity_type_code = ?";
return $connection->fetchOne($sql, array($entity_type_code));
}
function _getIdFromSku($sku){
$connection = _getConnection('core_read');
$sql = "SELECT entity_id FROM " . _getTableName('catalog_product_entity') . " WHERE sku LIKE '%$sku%'";
return $connection->fetchOne($sql, array($sku));
}
function _checkIfSkuExists($sku){
$connection = _getConnection('core_read');
$sql = "SELECT * FROM " . _getTableName('catalog_product_entity') . " WHERE sku LIKE '%$sku%'";
// I think you can try $conn->fetchAll();
return $connection->fetchAll($sql, array($sku));
// print_r this - it will be an array (empty or not)
print_r($connection);
}
function _updatePrices($data){
$connection = _getConnection('core_write');
$sku = $data[0];
$newPrice = $data[4];
$specialPrice = $data[6];
$status = $data[10];
$productId = $row['entity_id'];
$attributeId = _getAttributeId();
$sql = "UPDATE " . _getTableName('catalog_product_entity_decimal') . " cped
SET cped.value = ?
WHERE cped.attribute_id = ?
AND cped.entity_id = ?";
$connection->query($sql, array($newPrice, $attributeId, $productId));
$sql2 = "UPDATE " . _getTableName('catalog_product_entity_decimal') . " cped
SET cped.value = ?
WHERE cped.attribute_id = ?
AND cped.entity_id = ?";
$connection->query($sql2, array($specialPrice, '567', $productId));
if ($status == "A") {
$sql3 = "UPDATE " . _getTableName('catalog_product_entity_int') . " cped
SET cped.value = ?
WHERE cped.attribute_id = ?
AND cped.entity_id = ?";
$connection->query($sql3, array('1', '273', $productId));
} else {
$sql4 = "UPDATE " . _getTableName('catalog_product_entity_int') . " cped
SET cped.value = ?
WHERE cped.attribute_id = ?
AND cped.entity_id = ?";
$connection->query($sql4, array('2', '273', $productId));
}
}
/***************** UTILITY FUNCTIONS ********************/
// Here comes your code after UTILITY FUNCTIONS:
$csv = new Varien_File_Csv();
$data = $csv->getData('price-update/PRDAH014.csv'); //path to csv
array_shift($data);
$message = '';
$count = 1;
foreach($data as $_data){
// insted of checking if there exists nuber of rows
// if(_checkIfSkuExists($_data[0])){
// you get rows that match your LIKE query
$skuRows = _checkIfSkuExists($_data[0]);
if(0 < sizeof($skuRows)){
// okay you have records, let's iterate through them
foreach ($skuRows as $row) {
try{
// modify your _updatePrices so as to use record ID from $row and CSV data from $data both
_updatePrices($_data, $row);
} catch(Exception $e){
// exception warning
}
}
}else{
// no records warning
}
} // foreach($data as $_data) ends
Okay, your function _checkIfSkuExists($sku) should return not count of rows but rows themselves:
function _checkIfSkuExists($sku){
$connection = _getConnection('core_read');
$sql = "SELECT * FROM " . _getTableName('catalog_product_entity') . " WHERE sku LIKE '%$sku%'";
// I think you can try $conn->fetchAll();
return $connection->fetchAll($sql, array($sku));
// print_r this - it will be an array (empty or not)
}
// in each row's data there's a record (or row) ID
function _updatePrices($data, $row){
$connection = _getConnection('core_write');
$sku = $data[0];
$newPrice = $data[4];
$specialPrice = $data[6];
$status = $data[10];
// here - you don't need to find `entity_id`, you already have it in $row
//$productId = _getIdFromSku($sku);
$productId = $row['entity_id'];
$attributeId = _getAttributeId();
// all your code remains the same
// ....
} // function _updatePrices ends
// ....
// Here comes your code after UTILITY FUNCTIONS:
$csv = new Varien_File_Csv();
$data = $csv->getData('price-update/PRDAH014.csv'); //path to csv
array_shift($data);
$message = '';
$count = 1;
foreach($data as $_data){
// insted of checking if number of rows that
// match your LIKE query is greater than zero
// if(_checkIfSkuExists($_data[0])){
// you get rows themselves that match your LIKE query
$skuRows = _checkIfSkuExists($_data[0]);
if(0 < sizeof($skuRows)){
// okay you have records, let's iterate through them
foreach ($skuRows as $row) {
try{
// modify your _updatePrices so as to use record ID from $row and CSV data from $data both
_updatePrices($_data, $row);
} catch(Exception $e){
// exception warning
}
}
}else{
// no records warning
}
$count++;
} // foreach($data as $_data) ends
// ...
I'm not familiar to Magento, but the algorythm is something like this, I think.