Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 2 of 5
Possible to delete all products and reset product id’s? 
 
jbova
Member
 
Avatar
Total Posts:  42
Joined:  2008-07-17
New Ringgold, PA, USA
 

Thank you Sam Figueroa.

If you want to run this from a shell, instead of a browser, you can add the following:

require_once 'app/Mage.php';
Mage :: app"default" ) -> setCurrentStoreMage_Core_Model_App :: ADMIN_STORE_ID );

Which would result in:

#!/usr/bin/php

<?php

deleteAllProducts
();

function 
deleteAllProducts()
{
  
require_once 'app/Mage.php';

  
Mage :: app"default" ) -> setCurrentStoreMage_Core_Model_App :: ADMIN_STORE_ID );
  
$products Mage :: getResourceModel('catalog/product_collection')->setStoreId(1)->getAllIds();
  if(
is_array($products))
  
{
    
foreach ($products as $key => $productId)
    
{
      
try {
        $product 
Mage :: getSingleton('catalog/product')->load($productId);
        
Mage :: dispatchEvent('catalog_controller_product_delete', array('product' => $product));
        
$product->delete();
      
catch (Exception $e{
        
echo "<br/>Can't delete product w/ id: $productId";
      
}
    }
  }
}

?>

You may then set the file to be executable and run from a shell. Note that this is still very slow, as Sam pointed out. I am currently running this on my laptop to delete about 640,000 products on a site I have checked out locally. It averages about 12,000 products per hour for me.

Be sure to edit your cli php.ini file to increase the maximum memory a script may consume.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Red Mouse Computers
Sr. Member
 
Total Posts:  123
Joined:  2009-10-04
 
Sam Figueroa - 29 September 2009 05:48 AM

I think while this will be a whole lot slower it is more future-version-proof-ish since it works against the API.
Works for me.

function deleteAllProducts()
  
{
    $products 
Mage::getResourceModel('catalog/product_collection')->setStoreId(STORE_ID)->getAllIds();
    if(
is_array($products))
    
{
      
foreach ($products as $key => $productId)
      
{
        
try {
          $product 
Mage::getSingleton('catalog/product')->load($productId);
          
Mage::dispatchEvent('catalog_controller_product_delete', array('product' => $product));
          
$product->delete();          
        
catch (Exception $e{
          
echo "<br/>Can't delete product w/ id: $productId";
        
}
      } 
    }
  }

can anyone confirm this works with the latest magento install?

 
Magento Community Magento Community
Magento Community
Magento Community
 
trogfish
Member
 
Total Posts:  42
Joined:  2008-12-03
 

I would very much appreciate it if the previously written future-proof-ish delete script could be tweaked to delete all products in a category, instead of ALL products.  The existing admin pages are unbearable for doing so.... a quicker CLI tool for doing so would rock!!!! Please..... I can barely delete 200 products an hour.  I have categories with 2500 products that need refreshed quarterly.  The import tools I have do not find and delete obsolete data.  Thus, necessitating deleting everything then rebuilding......  Again.... PLEASE??  Or, at least help me identify how to specify products within a category, instead of ALL products.....

Thanks in advance…

 
Magento Community Magento Community
Magento Community
Magento Community
 
jbova
Member
 
Avatar
Total Posts:  42
Joined:  2008-07-17
New Ringgold, PA, USA
 

trogfish,

Here is the script to delete all products in a given category. Change the category id and store id at the top of the script before running it. You can also remove the echo statements if you don’t want any output.

#!/usr/bin/php
<?php

$storeid 
1;
$catid 16;

deleteAllCategoryProducts($storeid,$catid);

function 
deleteAllCategoryProducts($storeid,$catid)
{
  
require_once 'app/Mage.php';
  
Mage::app"default" )->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

  
$_category Mage::getModel('catalog/category')->setStoreId$storeid );
  
$_category $_category->load($catid);

  
$subcats $_category->getAllChildren(true); // true to get as array
  
  
foreach ($subcats as $subcatid{
    
echo 'Deleting from Category ID: ' $subcatid "\n";
    echo 
'Deleting Product IDs: ';
    
$pids Mage::getResourceModel('catalog/product_collection')->setStoreId($storeid)->addCategoryFilter(Mage::getModel('catalog/category')->load($subcatid))->getAllIds();

    if(
is_array($pids))
    
{
      
foreach ( $pids as $pid {
        
echo $pid ' ';
        
$product Mage::getSingleton('catalog/product')->load($pid)->delete();
        unset (
$pids[$pid]);
      
}
    }
    
echo "\n";

  
}

?>

Jim

 
Magento Community Magento Community
Magento Community
Magento Community
 
trogfish
Member
 
Total Posts:  42
Joined:  2008-12-03
 

SWEEEEEEET!  Thanks very much!  I will try it out at my first opportunity!

 
Magento Community Magento Community
Magento Community
Magento Community
 
trogfish
Member
 
Total Posts:  42
Joined:  2008-12-03
 

Thanks to JBOVA for the “all products in a category” script.  I believe it works, but I have a problem on my host server.  Bluehost.com doesn’t allow access to ZEND from the command line.  No exceptions.  No workarounds.
I can call the script from wget, which adds the nescessary HTTP headers and path to ZEND, but the script chokes and has to be recalled again, and again; just like using the backend web admin interface. 

I am actively creating a new VPS.  I have hit the wall with mass shared hosting.  I still plan on using the script in the future, but it isn’t currently helpful with my current configuration… It will be a great convenience once I am setup in a better environment.

 
Magento Community Magento Community
Magento Community
Magento Community
 
sahuspilwal
Member
 
Avatar
Total Posts:  37
Joined:  2009-08-02
Hastings, United Kingdom
 
Mike Smullin - 19 February 2009 02:16 PM

er, not quite.

TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_product_entity`;

TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;

insert  into `catalog_product_link_type`(`link_type_id`,`code`) values (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
insert  into `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) values (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
insert  into `cataloginventory_stock`(`stock_id`,`stock_name`) values (1,'Default');

Probably should also clear/refresh/disable cache from System > Cache Management. I also like to delete everything under my ./var directory as well just to be doubly sure its all clear.

If you forget to truncate the other tables, or if you forget to restore the default values afterward, you’ll have foreign key issues later.

see also: Beautiful up-to-date PDF Database Diagram
http://www.magentocommerce.com/wiki/development/magento_database_diagram

Does anyone know if this solution plus the other tables mentioned on page 1 still work in version 1.3 in particular Magento ver. 1.3.2.4 or are there other tables to consider now??

Or would others recommend other solution to remove all products and reset ids from within Magento or MySQL Database?

Cheers, sasdaman

 
Magento Community Magento Community
Magento Community
Magento Community
 
erow80
Jr. Member
 
Total Posts:  18
Joined:  2009-08-06
 

Forgive my ignorance, but how do I run Sam’s script? I went in to phpMyAdmin and tried running this through the SQL script, results below:

Error

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 54
STR: ::
SQL: function deleteAllProducts()
{
$products = Mage::getResourceModel(’catalog/product_collection’)->setStoreId(STORE_ID)->getAllIds();function deleteAllProducts()
{
$products = Mage::getResourceModel(’catalog/product_collection’)->setStoreId(STORE_ID)->getAllIds();function deleteAllProducts()
{
$products = Mage::getResourceModel(’catalog/product_collection’)->setStoreId(STORE_ID)->getAllIds();function deleteAllProducts()
{
$products = Mage::getResourceModel(’catalog/product_collection’)->setStoreId(STORE_ID)->getAllIds();function deleteAllProducts()
{
$products = Mage::getResourceModel(’catalog/product_collection’)->setStoreId(STORE_ID)->getAllIds();function deleteAllProducts()
{
$products = Mage::getResourceModel(’catalog/product_collection’)->setStoreId(STORE_ID)->getAllIds();function deleteAllProducts()
{
$products = Mage::getResourceModel(’catalog/product_collection’)->setStoreId(STORE_ID)->getAllIds();

SQL query:

function deleteAllProducts() { $products = Mage::getResourceModel(’catalog/product_collection’)->setStoreId(STORE_ID)->getAllIds();

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘function deleteAllProducts()
{
$products = Mage::getResourceModel(’catal’ at line 1
[ Back ]

 
Magento Community Magento Community
Magento Community
Magento Community
 
erow80
Jr. Member
 
Total Posts:  18
Joined:  2009-08-06
 

I was able to use the other TRUNCATE code provided in SQL, and all products were deleted as expected. If anyone needs help on this, feel free to PM me.

 
Magento Community Magento Community
Magento Community
Magento Community
 
trogfish
Member
 
Total Posts:  42
Joined:  2008-12-03
 

I have posted a wiki article about using PHP and SOAP to delete all products in a category.  It is a very, very, very slow method, but works for me.  It is a long read, which is why I posted in the wiki instead of in the forum.  I posted a long explanation of why I arrived at this method and how it works.

http://www.magentocommerce.com/wiki/doc/webservices-api/using_soap_api_to_delete_all_products_in_a_category

 
Magento Community Magento Community
Magento Community
Magento Community
 
edwardtilbury
Member
 
Total Posts:  66
Joined:  2008-08-01
Las Vegas, Nevada
 

It basically takes me all day to delete my products 12,000+

I wish there was a free extension or a button in the back end that would just truncate the appropriate tables.  Magento is so dynamic that I don’t feel safe running these sql scripts, especially if they haven’t been tested in 1.3.2.4

 
Magento Community Magento Community
Magento Community
Magento Community
 
Red Mouse Computers
Sr. Member
 
Total Posts:  123
Joined:  2009-10-04
 

dont run the sql script on 1.3.2.4 it fucked up my installation and had to reinstall

 
Magento Community Magento Community
Magento Community
Magento Community
 
cmuench
Jr. Member
 
Avatar
Total Posts:  15
Joined:  2009-10-27
Mainz
 

We could modify the SQL statement. The problem are some foreign keys. The InnoDB table engine tries internally to truncate the data of a table with a combination of a DROP TABLE and CREATE TABLE statement. If there are some foreign keys this does not work. The trick is to disable the foreign key checks during truncation. This statement deletes >20.000 products in some seconds. If there are problems with the statement please report it to me.

SET FOREIGN_KEY_CHECKS 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock`;
INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
INSERT  INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');
TRUNCATE TABLE `catalog_product_entity`;
SET FOREIGN_KEY_CHECKS 1;
 
Magento Community Magento Community
Magento Community
Magento Community
 
ZenMasta
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2010-03-12
 

Glad I found this topic. Trying to delete in the backend is super slow… usually resulting in being locked out of the admin cp until the operation is complete but not before a 500 internal error occurs. The truncate SQL queries are really handy because I have been testing importing lots of products and needed a fast way to “reset”.

I ran Mikes script on 1.4.0.1 it seemed to work fine. I had to remember to add my prefix to the table names though.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ZenMasta
Sr. Member
 
Avatar
Total Posts:  108
Joined:  2010-03-12
 

Hey Sam, can you give an example of how slow it is?

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 2 of 5