Magento mass-assign products to category

Question

As the title says,i need to mass-assign products to a category and from the admin i can only edit one product at a time; i dont know why it just doesnt work to mass add them from the "category products" tab in the category page.
Thats why i need another method that's fast,like using phpMyAdmin or something alike.

Any help?

Thanks in advance!

Sacramentalism

Also try out my extension: Dynamic Category Products - it uses simple to define rules, allowing assignment of products to the category. – Lallans

Answer 1

I managed to resolve the problem with the following code :

$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$x = 1171;
$y = 2000;
$categoryID = 4;
$productPosition = 0;
while($x <= $y) {
$write->query("REPLACE INTO `catalog_category_product` (`category_id`, `product_id`,  `position`) VALUES ($categoryID, $x++, $productPosition)");
}
echo "The job is done";
?>

I hope the code is clear for everyone,if it's not,reply and i'll try to explain it.

@nachito : here it is.

Sacramentalism

You should use models instead of raw SQL for updating Magento data whenever you can. During save there are many methods which are executed and have impact on data. – Chong

Answer 2

I created a simple script to do this outside of Magento. Be sure to test this first on a single product and make sure it looks as you'd expect.

// Load Magento
require_once 'path/to/app/Mage.php';
Mage::app();

// $productIds is an array of the products you want to modify.
// Create it however you want, I did it like this...
$productsIds = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToFilter('sku', array('like' => 'something'))
    ->getAllIds();

// Array of category_ids to add.
$newCategories = array(20);
foreach ($productIds as $id) {
    $product = Mage::getModel('catalog/product')->load($id);
    $product->setCategoryIds(
        array_merge($product->getCategoryIds(), $newCategories)
    );
    $product->save();
}

If you wish to overwrite a product's existing categories, change array_merge(...) to just $newCategories.

Eider

When i try your solution this error appears : code Fatal error: Uncaught exception 'Mage_Eav_Model_Entity_Attribute_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1059-1-0-10.0000-0' for key 'E8AB433B9ACB00343ABB312AD2FAB087'' in /home/lumeatap/public_html /app/code/core/Mage/Core/Model/Config.php:1350 code – Sacramentalism

Not sure what to say about that one... did you somehow get duplicated SKUs in the system? – Eider

I eventually managed to assign all products to their categories. You were close with your answer,but the code you provided may be using "insert" sql command instead of "replace". I did other simple script in which i wrote the sql query that dealt directly with the database and i was getting the same error by using "INSERT" instead of "REPLACE". I also tried your advice by changing "array_merge()" with "$newcategories" but it had no effect. Thank you! – Sacramentalism

@Sacramentalism Well I'm glad you got it solved. Can you post your answer so that others can use it? – Eider

i made another post containing the solution. Thanks for trying to help. – Sacramentalism

how to do bulk remove assigned category of products – Wanitawanneeickel

Answer 3

I would shy away from tackling this problem from the database side of things. If you do go that direction make sure and take lots of backups and do it during low usage.

The following thread on the Magento forum identifies the very same problem. One poster recommends a raw sql approach with example. Again, I would be careful - make sure you take backups.

The answer I like best from the thread (posted by Magento MVP):

Go into the category you don’t want them in, find the product list. Click the check boxes on the products you want to remove and select delete from the little dropdown.

Now go into the category where you do want them, go to the product list. Select the NO dropdown so it shows items not in the category. You might have to do a selective search to limit stuff and do it in a couple iterations. Click the check boxes and tell it to add stuff.

Monetta

@ RThomas : about the answer you like most : i know it's strange and i dont have any hints why this is not working. I have loads of unassigned products and when i try to assing them from the category page,magento acts strange and doesnt want to add them and sometimes he even decreases the total number of products in that category. – Sacramentalism

yes i did,but no avail. Do,i finally got it working and i've posted the solution in the bottom of this page. :) Thanks! – Sacramentalism

Answer 4

You may as well do this using the magento API This is the script I use for mass adding products. sku.txt contains one sku per line.

<?php
$wsdlUrl = "magento-root/index.php/api/soap/?wsdl";
$proxy = new SoapClient($wsdlUrl);
$sessionId = $proxy->login('apiuser', 'apipasswd');


$listOfDiscountedSKUFile = "sku.txt";


function readinFile($filePath)
{
    $fp = fopen($filePath,'r') or exit("Unable to open file!");
    $dataItems = array();
    while(!feof($fp))
    {
        $dataItems[] = trim(fgets($fp));
    }
    fclose($fp);
    var_dump($dataItems);
    return $dataItems;
}

function addToCategory($sku,$categoryId)
{
    global $proxy,$sessionId;
    $proxy->call($sessionId, 'category.assignProduct', array($categoryId, $sku));
}

function IsNullOrEmptyString($question){
        return (!isset($question) || trim($question)==='');
}
$categoryId = 82;//e.g.
$listOfSKU = readinFile($listOfDiscountedSKUFile);
foreach($listOfSKU as $sku)
{
    addToCategory($sku,$category);
}

?>

Scat