0

|I have this script that I want to use to change the product position in a category programatically but I want like it to get SKUs from a csv

<?php
 require_once './app/Mage.php'; 
Mage::app('default');
Mage::app ()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
umask(0);



$resource = Mage::getSingleton('core/resource'); //get an instance of the core resource
$connection = $resource->getConnection('core_write'); //get an instance of the write connection

$tableName = $resource->getTableName('catalog/category_product'); //this should add the prefix if you have one

$io = new Varien_Io_File();
$io->streamOpen('./var/import/importpositions.csv', 'r+');
//$file = fopen('./var/import/importCats.csv', 'r');
while($csvData = $io->streamReadCsv()){
    if (isset($csvData[0])){ //get the first column - sku
         $sku = trim($csvData[0]);
         //get id by sku
         $id = Mage::getModel('catalog/product')->getIdBySku($sku); //get the product id.
         $sql = "UPDATE {$tableName} SET `position` = 0 WHERE `product_id` = {$id}"; //set the position to 0 for the product in all the categories.
         $connection->query($q); //run the query

    }
}
$io->streamClose();
?>

How to I set it to get a list of SKus in a CSV

I get this error when I run the script

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty' in /var/www/html/lib/Zend/Db/Statement/Pdo.php:228 Stack trace: #0 /var/www/html/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array) #1 /var/www/html/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array) #2 /var/www/html/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array) #3 /var/www/html/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) #4 /var/www/html/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('', Array) #5 /var/www/html/lib/Varien/Db/Adapter/Pdo/Mysql.php(428): Zend_Db_Adapter_Pdo_Abstract->query('', Array) #6 /var/www/html/importposition.php(24): Varien_Db_Adapter_Pdo_Mysql->query(NULL) #7 {main} Next exception 'Zend_Db_Statement_Exception' with message 'SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty' in /var/www/html/lib/Zend/D in /var/www/html/lib/Zend/Db/Statement/Pdo.php on line 234
1
  • it is not that the product does not exists so no id is being returned? Feel free to show use what print_r($sql); actually shows – David Manners Jun 13 '14 at 13:39
1

Here is how you can read a csv.
Let's assume the file has only one column with the sku.

require_once 'app/Mage.php'; 
Mage::app('default');
Mage::app ()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
umask(0);



$resource = Mage::getSingleton('core/resource'); //get an instance of the core resource
$connection = $resource->getConnection('core_write'); //get an instance of the write connection

$tableName = $resource->getTableName('catalog/category_product'); //this should add the prefix if you have one

$io = new Varien_Io_File();
$io->streamOpen('path/to/file.csv', 'r+');
while($csvData = $io->streamReadCsv()){
    if (isset($csvData[0])){ //get the first column - sku
         $sku = trim($csvData[0]);
         //get id by sku
         $id = Mage::getModel('catalog/product')->getIdBySku($sku); //get the product id.
         $sql = "UPDATE {$tableName} SET `position` = 0 WHERE `product_id` = {$id}"; //set the position to 0 for the product in all the categories.
         $connection->query($sql); //run the query

    }
}
$io->streamClose();

If you want to upload the file and not read it from the disk just create a simple html form with a file input

<input type="file" name="file" />

And replace in the code above

$io->streamOpen('path/to/file.csv', 'r+');

with

$io->streamOpen($_FILES['file']['tmp_name'], 'r+');
7
  • I have tried to execute the script but it returns a blank page. – Stifboy Jun 13 '14 at 9:21
  • This is what I am currently using – Stifboy Jun 13 '14 at 9:33
  • @Stifboy I didn't test the code. Maybe I forgot a semicolon or something like that. add this at the top of the file to see the errors ini_set('display_errors', 1); – Marius Jun 13 '14 at 11:20
  • I get some errors. Have posted above the error I get – Stifboy Jun 13 '14 at 12:54
  • @Stifboy. Try it now. the query string variable was named $sql but the call was made with the var name $q that doesn't exist. – Marius Jun 13 '14 at 12:59

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.