Remove all products from the database

[sideone]

All-

How can i remove all products from the database while keeping manuf, sales, etc.. in tact?

thanks,

sideone.

[shokinro]

I think you will have to use SQL script to cleanup.
But you must be very careful, because products linked to other tables, like cart, order.
if you remove products, you will also need to remove all those related data together.
Otherwise, you will get error when you open the orders, or invoice.

[JulianM]

And where to find a SQL Script to cleanup?

[Christian Lescuyer]

Here is one.

 

Be very careful. Backup your database before usage!

 

########################
# BACKUP YOUR DATABASE #
########################
#
# This script empties the product table and all linked tables with dependent data.
#
# This is somewhat arbitrary: for example, we're keeping the tags but we're deleting
# attachments, which could be reused for new products.
#
# "*" indicates a Primary Key
#
# @author	Christian Lescuyer
# @company   Goelette http://goelette.net/
# @copyright 2011 Goelette
# @license   http://opensource.org/licenses/afl-3.0.php  Academic Free License (AFL 3.0)
# @version   1.0 original version, applies to Prestashop 1.4.5.1
#
########################
# BACKUP YOUR DATABASE #
########################


# All these tables have an id_product primary key [or should have!]
TRUNCATE TABLE ps_product;					   # *id_product  id_supplier id_manufacturer id_tax_rules_group id_category_default id_color_default
TRUNCATE TABLE ps_product_attachment;			# *id_product *id_attachment
TRUNCATE TABLE ps_product_country_tax;		   #  id_product  id_country id_tax
TRUNCATE TABLE ps_product_group_reduction_cache; # *id_product *id_group
TRUNCATE TABLE ps_product_lang;				  # *id_product *id_lang
TRUNCATE TABLE ps_product_sale;				  # *id_product
TRUNCATE TABLE ps_product_tag;				   # *id_product *id_tag
TRUNCATE TABLE ps_category_product;			  #  id_product  id_category
TRUNCATE TABLE ps_compare_product;			   #  id_product *id_compare_product id_guest id_customer
TRUNCATE TABLE ps_feature_product;			   # *id_product *id_feature
TRUNCATE TABLE ps_search_index;				  # *id_product *id_word
TRUNCATE TABLE ps_specific_price;				#  id_product *id_specific_price
TRUNCATE TABLE ps_specific_price_priority;	   # *id_product *id_specific_price_priority
# This one is special, links product to product
TRUNCATE TABLE ps_accessory;					 #  id_product_1  id_product_2
# Packs
TRUNCATE TABLE ps_pack;						  # *id_product_pack *id_product_item

# All these tables have an id_attachment primary key
TRUNCATE TABLE ps_attachment;	   # *id_attachment
TRUNCATE TABLE ps_attachment_lang;  # *id_attachment *id_lang

# All these tables have an id_attribute primary key
TRUNCATE TABLE ps_attribute;	   # *id_attribute id_attribute
TRUNCATE TABLE ps_attribute_lang;  # *id_attribute *id_lang

# All these tables have an id_attribute_group primary key
TRUNCATE TABLE ps_attribute_group;	  # *id_attribute_group
TRUNCATE TABLE ps_attribute_group_lang; # *id_attribute_group *id_lang

# All these tables have an id_attribute_impact primary key
TRUNCATE TABLE ps_attribute_impact;	 # *id_attribute_impact id_product id_attribute

# All these tables have an id_feature or id_feature_value key
TRUNCATE TABLE ps_feature;			 # *id_feature
TRUNCATE TABLE ps_feature_lang;		# *id_feature *id_lang
TRUNCATE TABLE ps_feature_value;	   # *id_feature_value  id_feature
TRUNCATE TABLE ps_feature_value_lang;  # *id_feature_value *id_lang

# All these tables have an id_customization_field key
TRUNCATE TABLE ps_customization_field;		   # *id_customization_field  id_product
TRUNCATE TABLE ps_customization_field_lang;	  # *id_customization_field *id_lang

# All these tables have an id_image primary key [or should have!]
TRUNCATE TABLE ps_image;		# *id_image  id_product
TRUNCATE TABLE ps_image_lang;   #  id_image  id_lang

# All these tables have an id_product_attribute primary key
TRUNCATE TABLE ps_product_attribute;			   # *id_product_attribute id_product
TRUNCATE TABLE ps_product_attribute_combination;   # *id_product_attribute *id_attribute
TRUNCATE TABLE ps_product_attribute_image;		 # *id_product_attribute *id_image

# All these tables have an id_product_download primary key
TRUNCATE TABLE ps_product_download;   # *id_product_download id_product

# All these tables have an id_word primary key
TRUNCATE TABLE ps_search_word;  # *id_word  id_lang

# All these tables have an id_tag primary key
# TRUNCATE TABLE ps_tag

# Empty carts
TRUNCATE TABLE ps_cart;			 # *id_cart ...
TRUNCATE TABLE ps_cart_discount;	#  id_cart
TRUNCATE TABLE ps_cart_product;	 #  id_cart id_product id_product_attribute
TRUNCATE TABLE ps_customization;	# *id_cart *id_customization *id_product
TRUNCATE TABLE ps_customized_data;  # *id_customization (see above)

# Delete Scenes
TRUNCATE TABLE ps_scene;			 # *id_scene
TRUNCATE TABLE ps_scene_category;	# *id_scene *id_category
TRUNCATE TABLE ps_scene_lang;		# *id_scene *id_lang
TRUNCATE TABLE ps_scene_products;	# *id_scene *id_product *x_axis *y_axis

# Delete stock movements
TRUNCATE TABLE ps_stock_mvt;  # *id_stock_mvt id_product ...

[kamiliok]

Hi, thanks for script! But how to run it?

[tidjean]

To run it, you can use phpmyadmin, then you have to choices:

1. Copy and paste the code into a file on your cumputer "script.sql". Select your prestashop database and clique to "import". You select the file "script.sql".
   
2. You clique on "sql" and copy and paste the code.
   

WIthout phpmyadmin:

Copy and paste the code into a file on your cumputer "script.sql". If you can connect to your database with a terminal

mysql -u admin -p -h host database < script.sql

 

Sure there is more option to do it, but phpmyadmin is very friendly.

[PascalVG]

hmm, I smell problems arising here, if you don't really know what you're doing with the SQL script. Then do you really know how to backup your database, do you know how to restore a database etc? Are you sure you want to go this way?

 

Just want to say, be very careful. Hacking your database outside PS is not child play, it can render your shop useless. So if you don't know how to restore your database, rethink twice...

 

Furthermore, I expect the script to work, so nothing about that (thanks for sharing, Christian! Very useful!), just sharing my thoughts about doing things without knowing what it exactly does can create risks.)

 

Pascal

[delroyp]

can you remove items via csv file import.... with a "delete" column?

[sujithm]

Excellent script and I was looking for this for quite a while. This helped me to remove the test data before publishing my site. Is there any script similar to this to remove categories, orders and users?

 

Thanks in advance

[vekia]

i just want to inform that prestashop by default has got nice tool called pscleaner (prestashop cleaner)

 

this module allows to:

1. remove whole catalog data
   
2. remove whole orders and customers data
   
3. allow to check functional integrity constraints (check & fix)
   

[sujithm]

i just want to inform that prestashop by default has got nice tool called pscleaner (prestashop cleaner)

 

this module allows to:

1. remove whole catalog data
   
2. remove whole orders and customers data
   
3. allow to check functional integrity constraints (check & fix)
   

 

Perfect... let me try this. Thank you very much

[mundodigital]

Perfect... let me try this. Thank you very much

i just want to inform that prestashop by default has got nice tool called pscleaner (prestashop cleaner) this module allows to:

1. remove whole catalog data
2. remove whole orders and customers data
3. allow to check functional integrity constraints (check & fix)

 

Please Can you tell me where can I download this ?

[vekia]

what prestashop version you use?

[PascalVG]

Hi all,

I heard there were some problems with the cleaner module. More info here:

http://www.prestasho...tashop-cleaner/

 

So make sure you have working backups before you use it. (Always good advice, especially before you make crucial changes)

 

Mundo, if you use Prestashop 1.5.x, its default shipped with the installation files. So look in your modules list (modules->modules) and search for cleaner. It should be there, ready to get installed.

 

My 2 cents,

pascal

[wippa]

Oh. Thank you.

Just having this issue with the default install of 1.5.6.2. Has demo info and wanna restore. Also for reset all after testings

Tested right now: On a fresh install the table ps_cart_discounts doesn't exist.
I'd commented out with # and worked perfectly.

Maybe you can improve your script adding IF EXISTS to the SQL sentence to make it a bit more compatible.

Thank you again! Will check more

Edited July 11, 2014 by wippa (see edit history)

[wippa]

i just want to inform that prestashop by default has got nice tool called pscleaner (prestashop cleaner)

 

this module allows to:

• remove whole catalog data
• remove whole orders and customers data
• allow to check functional integrity constraints (check & fix)

 

Perfect