1

I am using CE 1.7.0.2, I want to disable all products from one specific category.

How I can disable all products from category? (SQL is fine)

  • can you please tell me? if a product would also included in other categories then what would be happen – Amit Bera Jan 16 '15 at 13:06
  • if product is in another category as well even then product should be disable. – Sandesh Jan 16 '15 at 13:23
4

Create a new file in in your Magento root, call it products-disable.php and paste in the following

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

    $categoryId = 123;
    $category = Mage::getModel('catalog/category')->load($categoryId);

    if ($category->getId()) {
        $products = $category->getProductCollection();
        foreach ($products as $product) {
            $product->setData('status', false);
            $product->save();
        }
    } else {
        die("category failed to load");
    }

Then browse to your site http://mymagesite.url/products-disable.php and it should set the status of all the products in that category as false

DONT RUN THIS STRAIGHT ON YOUR PRODUCTION SITE. Test it out first :)

edit :

This magento code will update your database as a bulk action, so far quicker than the initial save on each row. I've batched it in pages of 500, change that value in the setPageSize call to whatever you please. I'd recommend this method FAR more than doing a direct sql change as it will trigger events and indexes and stuff.

I haven't tested this code, but believe it should work.

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

$categoryId = 123;
$storeCode = 'default';

$storeId = Mage::getModel('core/store')->load($storeCode)->getId();
$category = Mage::getModel('catalog/category')->load($categoryId);

if ($storeId && $category->getId()) {

    $collection = $category->getProductCollection();
    $collection->getSelect()->reset(Zend_Db_Select::COLUMNS)->columns('entity_id');

    $collection->setPageSize(500);

    for ($curPage = 1; $curPage <= $collection->getLastPageNumber(); $curPage++) {
        $collection->clear();
        $collection->setCurPage($curPage);

        $ids = array();
        foreach ($collection as $_product) {
            $ids[$_product->getId()] = $_product->getId();
        }

        if (!empty($ids)) {
            Mage::getSingleton('catalog/product_action')->updateAttributes($ids, array('status' => Mage_Catalog_Model_Product_Status::STATUS_DISABLED), $storeId);
        }
    }

} else {
    die("category or store failed to load");
}
  • Thanks Luke. Is there any way we can do it using SQL? As I have more than 50000 products in one category. – Sandesh Jan 16 '15 at 13:39
  • Gotcha, give me 5 mins :) – Luke Rodgers Jan 16 '15 at 13:42
  • See my edit for updated code – Luke Rodgers Jan 16 '15 at 13:49
  • For example category has 2000 products. How I can disable all products in this category using your script? – Sandesh Jan 16 '15 at 14:09
  • 1
    Second approach much better than the first one. +1 – Marius Jan 16 '15 at 14:35
1

Hi try this which is give you list of products specifiv cat

For getting this category products:

SELECT `e`.`value_id` FROM `catalog_product_entity_int` AS `e` 
    LEFT JOIN `catalog_category_product` AS `cp` ON e.entity_id = cp.product_id WHERE (cp.category_id IN(15)) and e.attribute_id = 96 )

and disable the product by

update  `catalog_product_entity_int` set  `catalog_product_entity_int`.value_id =0 where `catalog_product_entity_int`.`value_id` in (YOUR_UPPer_value_Ids)

Where 15 in category ids

  • what is product.attribute_id? – Sandesh Jan 16 '15 at 13:53
  • I believe thats the default magento attr id for "status". It's unlikely that it's different on your instance, but it is possible! This is why I prefer the direct Magento approach, works on all systems. select attribute_id, attribute_code,source_model from eav_attribute where attribute_code ='status'; shows 96 as the attribute_id on my instance – Luke Rodgers Jan 16 '15 at 13:54

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.