How to remove all products from a supplier?

[massalexx]

Hi guys,

I removed a supplier from backoffice (catalog/suppliers) but the products from the supplier is still on my site. Am I missing something or is this a known bug in prestashop?

I wonder also if there is a way to replace old products with the new ones when I upload a new csv file from a supplier?

[massalexx]

I discovered that the supplier is removed only from the table ps_supplier but the products are still there in the table ps_product under the same id_supplier. Any suggestions?

[more-control]

This would be very handy; especially for larger shop sites. The shop I am using has over 100 different category groups from 8 different suppliers and requires updates of stock and prices on a daily basis. Having to delete all items and then re-import makes the shop quite unstable; while having to update products by category is quite a time consuming task.

 

If there is a means to delete all products by manufacturer / supplier; that would make the whole import and update procedure of stock to be more manageable and less time consuming.

[TweetClean]

Here is a stand alone utility I wrote. It actually will also clean up any orphan foreign key entries that may be left from poor product management. I use this on Prestashop 1.4.9

 

<?php
ini_set('max_execution_time', 1800);
ini_set('max_input_time', 1800);
ini_set('memory_limit', '1536M');

set_time_limit(1800);
define('_DB_NAME_', 'yourdatabasename');
define('_DB_SERVER_', 'localhost');
define('_DB_USER_', 'yourusername');
define('_DB_PASSWD_', 'yourpassword');

/* Delete All Products for a Manufacturer */
$db_handle = mysql_connect(_DB_SERVER_, _DB_USER_, _DB_PASSWD_);
$db_found = mysql_select_db(_DB_NAME_, $db_handle);

//Put your manufacturer ID here
$current_manufacturer = 8;


$deletesql = "DELETE FROM ps_product WHERE id_manufacturer=".$current_manufacturer;
$deleteresult = mysql_query($deletesql);
print($deleteresult." product entries deleted<br>");

$deletesql = "
 DELETE FROM ps_product_attribute
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product attributes deleted<br>");


$deletesql = "
 DELETE FROM ps_product_sale
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product sales deleted<br>");

$deletesql = "
 DELETE FROM ps_product_lang
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product language entries deleted<br>");

$deletesql = "
 DELETE FROM ps_category_product
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product category entries deleted<br>");

$deletesql = "
 DELETE FROM ps_image
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product image entries deleted<br>");

$deletesql = "
 DELETE FROM ps_image_lang
 WHERE id_image NOT IN (SELECT p.id_image FROM ps_image p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product image language entries deleted<br>");

$deletesql = "
 DELETE FROM ps_product_attribute_combination
 WHERE id_product_attribute NOT IN (SELECT p.id_product_attribute FROM ps_product_attribute p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product product attribute entries deleted<br>");
$deletesql = "
 DELETE FROM ps_attribute_impact
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product attribue impact entries deleted<br>");

$deletesql = "
 DELETE FROM ps_product_tag
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product tag entries deleted<br>");

$deletesql = "
 DELETE FROM ps_tag
 WHERE id_tag NOT IN (SELECT p.id_tag FROM ps_product_tag p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product tag entries deleted<br>");

$deletesql = "
 DELETE FROM ps_feature_product
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product feature entries deleted<br>");

$deletesql = "
 DELETE FROM ps_feature_value
 WHERE id_feature NOT IN (SELECT p.id_feature FROM ps_feature_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product feature value entries deleted<br>");

$deletesql = "
 DELETE FROM ps_feature_value_lang
 WHERE id_feature_value NOT IN (SELECT p.id_feature_value FROM ps_feature_value p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product feature value language entries deleted<br>");
echo "<br><br>Finished<br>";
mysql_close($db_handle);
?>

Edited May 2, 2013 by TweetClean (see edit history)

[mundodigital]

Here is a stand alone utility I wrote. It actually will also clean up any orphan foreign key entries that may be left from poor product management. I use this on Prestashop 1.4.9

 

<?php
ini_set('max_execution_time', 1800);
ini_set('max_input_time', 1800);
ini_set('memory_limit', '1536M');

set_time_limit(1800);
define('_DB_NAME_', 'yourdatabasename');
define('_DB_SERVER_', 'localhost');
define('_DB_USER_', 'yourusername');
define('_DB_PASSWD_', 'yourpassword');

/* Delete All Products for a Manufacturer */
$db_handle = mysql_connect(_DB_SERVER_, _DB_USER_, _DB_PASSWD_);
$db_found = mysql_select_db(_DB_NAME_, $db_handle);

//Put your manufacturer ID here
$current_manufacturer = 8;


$deletesql = "DELETE FROM ps_product WHERE id_manufacturer=".$current_manufacturer;
$deleteresult = mysql_query($deletesql);
print($deleteresult." product entries deleted<br>");

$deletesql = "
 DELETE FROM ps_product_attribute
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product attributes deleted<br>");


$deletesql = "
 DELETE FROM ps_product_sale
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product sales deleted<br>");

$deletesql = "
 DELETE FROM ps_product_lang
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product language entries deleted<br>");

$deletesql = "
 DELETE FROM ps_category_product
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product category entries deleted<br>");

$deletesql = "
 DELETE FROM ps_image
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product image entries deleted<br>");

$deletesql = "
 DELETE FROM ps_image_lang
 WHERE id_image NOT IN (SELECT p.id_image FROM ps_image p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product image language entries deleted<br>");

$deletesql = "
 DELETE FROM ps_product_attribute_combination
 WHERE id_product_attribute NOT IN (SELECT p.id_product_attribute FROM ps_product_attribute p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product product attribute entries deleted<br>");
$deletesql = "
 DELETE FROM ps_attribute_impact
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product attribue impact entries deleted<br>");

$deletesql = "
 DELETE FROM ps_product_tag
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product tag entries deleted<br>");

$deletesql = "
 DELETE FROM ps_tag
 WHERE id_tag NOT IN (SELECT p.id_tag FROM ps_product_tag p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product tag entries deleted<br>");

$deletesql = "
 DELETE FROM ps_feature_product
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product feature entries deleted<br>");

$deletesql = "
 DELETE FROM ps_feature_value
 WHERE id_feature NOT IN (SELECT p.id_feature FROM ps_feature_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product feature value entries deleted<br>");

$deletesql = "
 DELETE FROM ps_feature_value_lang
 WHERE id_feature_value NOT IN (SELECT p.id_feature_value FROM ps_feature_value p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product feature value language entries deleted<br>");
echo "<br><br>Finished<br>";
mysql_close($db_handle);
?>

 

 

Hello TweetClean

 

I must say that this script is great!

 

Clean all those unnecessary of a manufacturer in Prestashop

 

I appreciated your contribution.

 

A greeting.

[onezoso]

1) Will this script work in 1.6X? 

 

2) Where do I execute this script? SSH? 

 

Thanks! 

[jhon111]

On 2/5/2013 at 2:10 PM, TweetClean said:

Here is a stand alone utility I wrote. It actually will also clean up any orphan foreign key entries that may be left from poor product management. I use this on Prestashop 1.4.9

 

 

<?php
ini_set('max_execution_time', 1800);
ini_set('max_input_time', 1800);
ini_set('memory_limit', '1536M');

set_time_limit(1800);
define('_DB_NAME_', 'yourdatabasename');
define('_DB_SERVER_', 'localhost');
define('_DB_USER_', 'yourusername');
define('_DB_PASSWD_', 'yourpassword');

/* Delete All Products for a Manufacturer */
$db_handle = mysql_connect(_DB_SERVER_, _DB_USER_, _DB_PASSWD_);
$db_found = mysql_select_db(_DB_NAME_, $db_handle);

//Put your manufacturer ID here
$current_manufacturer = 8;


$deletesql = "DELETE FROM ps_product WHERE id_manufacturer=".$current_manufacturer;
$deleteresult = mysql_query($deletesql);
print($deleteresult." product entries deleted<br>");

$deletesql = "
 DELETE FROM ps_product_attribute
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product attributes deleted<br>");


$deletesql = "
 DELETE FROM ps_product_sale
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product sales deleted<br>");

$deletesql = "
 DELETE FROM ps_product_lang
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product language entries deleted<br>");

$deletesql = "
 DELETE FROM ps_category_product
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product category entries deleted<br>");

$deletesql = "
 DELETE FROM ps_image
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product image entries deleted<br>");

$deletesql = "
 DELETE FROM ps_image_lang
 WHERE id_image NOT IN (SELECT p.id_image FROM ps_image p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product image language entries deleted<br>");

$deletesql = "
 DELETE FROM ps_product_attribute_combination
 WHERE id_product_attribute NOT IN (SELECT p.id_product_attribute FROM ps_product_attribute p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product product attribute entries deleted<br>");
$deletesql = "
 DELETE FROM ps_attribute_impact
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product attribue impact entries deleted<br>");

$deletesql = "
 DELETE FROM ps_product_tag
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product tag entries deleted<br>");

$deletesql = "
 DELETE FROM ps_tag
 WHERE id_tag NOT IN (SELECT p.id_tag FROM ps_product_tag p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product tag entries deleted<br>");

$deletesql = "
 DELETE FROM ps_feature_product
 WHERE id_product NOT IN (SELECT p.id_product FROM ps_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product feature entries deleted<br>");

$deletesql = "
 DELETE FROM ps_feature_value
 WHERE id_feature NOT IN (SELECT p.id_feature FROM ps_feature_product p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product feature value entries deleted<br>");

$deletesql = "
 DELETE FROM ps_feature_value_lang
 WHERE id_feature_value NOT IN (SELECT p.id_feature_value FROM ps_feature_value p)
 ";

$deleteresult = mysql_query($deletesql);
print($deleteresult." product feature value language entries deleted<br>");
echo "<br><br>Finished<br>";
mysql_close($db_handle);
?>

Hello dear friend do you have a version updated to prestashop 1.7.3.0  ?