Jump to content

[SOLVED] Delete massive products from database (10420 products)


Recommended Posts

Hello, I'm wondering if there's any SQL script that could delete all products (and just products, not categories, or anything else) from my database.

 


 

I'm currently using prestashop 1.6.1.7 and I have 10,420 products in my catalog. I want to delete them all. I tried deleting every 10 products from BO, but it takes aprox. ~ 3min.

 

Thanks.

Edited by devzk (see edit history)

Share this post


Link to post
Share on other sites

It's better to use a PHP script for that purpose, you'll make sure that the product gets deleted with all the dependencies, images.

 

Something like: 

$res = Db::getInstance()->executeS('SELECT `id_product` FROM `'._DB_PREFIX_.'product` ');

if ($res) {
	foreach ($res as $row) {
		$p = new Product($row['id_product']);
		$p->delete();
	}
}

Share this post


Link to post
Share on other sites

 

It's better to use a PHP script for that purpose, you'll make sure that the product gets deleted with all the dependencies, images.

 

Something like: 

$res = Db::getInstance()->executeS('SELECT `id_product` FROM `'._DB_PREFIX_.'product` ');

if ($res) {
	foreach ($res as $row) {
		$p = new Product($row['id_product']);
		$p->delete();
	}
}

This PHP script will take the same effect as pressing "Delete" button from BO? I mean, all attributes, and specific prices depending on that product will be deleted?

Share this post


Link to post
Share on other sites

Yes it should.

 

Okay, Thanks!

 

I'll try and I'll post my results here so if anyone else is going through the same problem maybe could help.

Share this post


Link to post
Share on other sites

Well, definitely works as expected, thanks to @Daresh.

 

I built this script to delete products from Daresh's code:

<?php
require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'config.inc.php');
include(dirname(__FILE__) . '/init.php');

$res = Db::getInstance()->executeS('SELECT `id_product` FROM `'._DB_PREFIX_.'product` ORDER BY `id_product` DESC LIMIT 100 ');
echo "<p>(".date('Y/m/d H:i:s').") Starting to delete products...</p>";
if ($res) {
    foreach ($res as $row) {
        echo "<p>(".date('Y/m/d H:i:s').") Deleting product with ID <b>".$row['id_product']."</b>...";
        $p = new Product($row['id_product']);
        if(!$p->delete()) {
            echo " <span style='color: red'>Error deleting this product!</span></p>";
        } else {
            echo " <span style='color: green'>DELETED</span></p>";
        }
    }
}

So what I did was create a php file and place it on root's folder. Now I'm accessing it like: www.example.com/my-file.php

 

I limited the execution every 100 products (due my server's timeout). For me it takes 30s aprox to execute and delete all 100 products.

Edited by devzk (see edit history)
  • Like 1

Share this post


Link to post
Share on other sites

Make it:

<?php
require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'config.inc.php');
include(dirname(__FILE__) . '/init.php');

$res = Db::getInstance()->executeS('SELECT `id_product` FROM `'._DB_PREFIX_.'product` ORDER BY `id_product` DESC LIMIT 100 ');
echo "<p>(".date('Y/m/d H:i:s').") Starting to delete products...</p>";
if ($res) {
    foreach ($res as $row) {
        echo "<p>(".date('Y/m/d H:i:s').") Deleting product with ID <b>".$row['id_product']."</b>...";
        $p = new Product($row['id_product']);
        if(!$p->delete()) {
            echo " <span style='color: red'>Error deleting this product!</span></p>";
        } else {
            echo " <span style='color: green'>DELETED</span></p>";
        }
    }
}
?>

<script>
window.location.reload();
</script>

And you have a fully automatic script :)

  • Like 1
  • Thanks 1

Share this post


Link to post
Share on other sites
  • 1 month later...

Hello Daresh,

 

I'm very interested to your script cause actually I created about 100000 all with a mistake inside and I have to delete all but it takes very long manually.

 

I would like to modify your script in order to specify a product id range to delete for example from product id 3551 to product id 103551.

 

I have good knowledge of php scripting but I'm not pretty sure how to modify the script and wanyt to avoid further mistakes.

 

Your reply would be very appreciated.

 

Thank you in advance.

 

Silvio

Edited by silvioscavone (see edit history)

Share this post


Link to post
Share on other sites
  • 10 months later...

Hello devzk,

 

Your script is very helpfull. I have the simmilar problem, I have 13.000 products, but from them only 4.000 active.

Can you please modify this script so I can delete only disabled products.

 

Thanks in advance.

 

Best regards

Share this post


Link to post
Share on other sites
  • 6 months later...
On 5/11/2017 at 11:53 AM, Daresh said:

Make it:


<?php
require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'config.inc.php');
include(dirname(__FILE__) . '/init.php');

$res = Db::getInstance()->executeS('SELECT `id_product` FROM `'._DB_PREFIX_.'product` ORDER BY `id_product` DESC LIMIT 100 ');
echo "<p>(".date('Y/m/d H:i:s').") Starting to delete products...</p>";
if ($res) {
    foreach ($res as $row) {
        echo "<p>(".date('Y/m/d H:i:s').") Deleting product with ID <b>".$row['id_product']."</b>...";
        $p = new Product($row['id_product']);
        if(!$p->delete()) {
            echo " <span style='color: red'>Error deleting this product!</span></p>";
        } else {
            echo " <span style='color: green'>DELETED</span></p>";
        }
    }
}
?>

<script>
window.location.reload();
</script>

And you have a fully automatic script :)

This is very interesting, @Daresh. I have a couple of thousand disabled/inactive products to remove entirely (product images etc) each week. Can you instruct me how I can use this script to only remove all disabled/inactive products?

Is it possible to run by cron?

With kind regards,
Johan.

Edited by hstrom (see edit history)

Share this post


Link to post
Share on other sites
  • 2 weeks later...
1 hour ago, Daresh said:

Of course it is, go to your catalog, select products you want to delete and choose delete.

Thank you for your hint.

But I was interested in the code behind the "delete" - Funktion, I am wondering which columns will be touched when deleting an article/product.

How can I figure out which tables will be touched? Is there a way to understand the code correctly?

Share this post


Link to post
Share on other sites
  • 1 month later...

Hey Guys,

I have approx. 22.000 Subcategories I have to delete COMPLETELY.
I have all the IDs in a CSV file. 

Would that kind of function work with categories? Is that possible?

 

Thx for help,

 

Share this post


Link to post
Share on other sites

Ok, well - we need:

  • the script to delete PRODUCTS  by IDs ->  we have a list of the Product-IDs
  • the Script to delete CATEGORIES by IDs ->  we have a list of the Category-IDs

 

Send  me your Paypal, if thats fine for you. I donate for help.

 

Edited by idee (see edit history)

Share this post


Link to post
Share on other sites
  • 5 weeks later...
On 5/11/2017 at 11:53 AM, Daresh said:

Make it:


<?php
require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'config.inc.php');
include(dirname(__FILE__) . '/init.php');

$res = Db::getInstance()->executeS('SELECT `id_product` FROM `'._DB_PREFIX_.'product` ORDER BY `id_product` DESC LIMIT 100 ');
echo "<p>(".date('Y/m/d H:i:s').") Starting to delete products...</p>";
if ($res) {
    foreach ($res as $row) {
        echo "<p>(".date('Y/m/d H:i:s').") Deleting product with ID <b>".$row['id_product']."</b>...";
        $p = new Product($row['id_product']);
        if(!$p->delete()) {
            echo " <span style='color: red'>Error deleting this product!</span></p>";
        } else {
            echo " <span style='color: green'>DELETED</span></p>";
        }
    }
}
?>

<script>
window.location.reload();
</script>

And you have a fully automatic script :)

Hello, I resume this interesting discussion because I hope you can give me a hand. Could you help me to modify the script in order to delete only inactive products from my catalog? Thank you

Share this post


Link to post
Share on other sites
10 hours ago, Daresh said:

Try this change:


('SELECT `id_product` FROM `'._DB_PREFIX_.'product` WHERE `active` = 0 ORDER BY `id_product` DESC LIMIT 100 ')

 

It works, thank you very much. I can ask you one last question: if I wanted to delete the inactive products of a certain category (for example the category with ID 305)?

Last thing: what are the advantages of using this script with respect to the normal prestashop back-office procedure? It's faster?

Share this post


Link to post
Share on other sites

If it's the default category you can try:

('SELECT `id_product` FROM `'._DB_PREFIX_.'product` WHERE `active` = 0 AND `id_category_default` = 350 ORDER BY `id_product` DESC LIMIT 100 ')

It's as fast as clicking "delete" in back office, you just don't need to click a 1000 times :)

Share this post


Link to post
Share on other sites
20 minutes ago, Daresh said:

If it's the default category you can try:


('SELECT `id_product` FROM `'._DB_PREFIX_.'product` WHERE `active` = 0 AND `id_category_default` = 350 ORDER BY `id_product` DESC LIMIT 100 ')

It's as fast as clicking "delete" in back office, you just don't need to click a 1000 times :)

thank you very much! the script also deletes all the images associated with the product, right?

Share this post


Link to post
Share on other sites
  • 2 weeks later...
On 5/11/2017 at 11:53 AM, Daresh said:

Make it:


<?php
require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'config.inc.php');
include(dirname(__FILE__) . '/init.php');

$res = Db::getInstance()->executeS('SELECT `id_product` FROM `'._DB_PREFIX_.'product` ORDER BY `id_product` DESC LIMIT 100 ');
echo "<p>(".date('Y/m/d H:i:s').") Starting to delete products...</p>";
if ($res) {
    foreach ($res as $row) {
        echo "<p>(".date('Y/m/d H:i:s').") Deleting product with ID <b>".$row['id_product']."</b>...";
        $p = new Product($row['id_product']);
        if(!$p->delete()) {
            echo " <span style='color: red'>Error deleting this product!</span></p>";
        } else {
            echo " <span style='color: green'>DELETED</span></p>";
        }
    }
}
?>

<script>
window.location.reload();
</script>

And you have a fully automatic script :)

Hi,

tried this and it seems to delete products for sure, but the echo command doesn't produce any result... The browser wheel just keeps spinning without any visual result..?

I guess I can run this as a cron at night?

With kind regards,
Johan.

 

Share this post


Link to post
Share on other sites
On 5/11/2017 at 11:53 AM, Daresh said:

Make it:


<?php
require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'config.inc.php');
include(dirname(__FILE__) . '/init.php');

$res = Db::getInstance()->executeS('SELECT `id_product` FROM `'._DB_PREFIX_.'product` ORDER BY `id_product` DESC LIMIT 100 ');
echo "<p>(".date('Y/m/d H:i:s').") Starting to delete products...</p>";
if ($res) {
    foreach ($res as $row) {
        echo "<p>(".date('Y/m/d H:i:s').") Deleting product with ID <b>".$row['id_product']."</b>...";
        $p = new Product($row['id_product']);
        if(!$p->delete()) {
            echo " <span style='color: red'>Error deleting this product!</span></p>";
        } else {
            echo " <span style='color: green'>DELETED</span></p>";
        }
    }
}
?>

<script>
window.location.reload();
</script>

And you have a fully automatic script :)

Hi,

tried this and it seems to delete products for sure, but the echo command doesn't produce any result... The browser wheel just keeps spinning without any visual result..? Actually, after 2 deleted products, it return a 404 error..?

I guess I can run this as a cron at night?

With kind regards,
Johan.

 

Share this post


Link to post
Share on other sites
10 minutes ago, Daresh said:

For a single shot or cron delete remove this part:


<script>
window.location.reload();
</script>

 

Thanks, will try that! 

A question: I inserted:

WHERE `active` = 0

But what happens when it is completed with the deletion of inactive products? Will it just look for inactive products and do nothing then?

With kind regards,
Johan

Share this post


Link to post
Share on other sites
1 hour ago, Daresh said:

Yes, it will do nothing.

Thanks, thanks! One nice thing with this solution is that it seems actually quicker and less server intensive than deleting products in backend... If I try to batch delete 10 products in backend my frontend completely freezes, but when this run in the background, it works excellent! 

I also checked out your search plugin - very nice! I will buy that in the future, when the site has settled a bit. 

With kind regards,
 Johan.

  • Like 1

Share this post


Link to post
Share on other sites
  • 3 months later...

how about all the images, attributes and everything that's generated when a product is created . 

this scripts seams to delete just one DB table ? 

Share this post


Link to post
Share on other sites
<?php
require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'config.inc.php');
include(dirname(__FILE__) . '/init.php');
$res = Db::getInstance()->executeS('SELECT `id_product` FROM `'._DB_PREFIX_.'product` WHERE id_product = "76" ');
echo "<p>(".date('Y/m/d H:i:s').") Starting to delete products...</p>";
if ($res) {
    foreach ($res as $row) {
        echo "<p>(".date('Y/m/d H:i:s').") Deleting product with ID <b>".$row['id_product']."</b>...";
        $p = new Product($row['id_product']);
        if(!$p->delete()) {
            echo " <span style='color: red'>Error deleting this product!</span></p>";
        } else {
            echo " <span style='color: green'>DELETED</span></p>";
        }

    }
}
?>

 

I tried one product product ID 76 after I run it the product deletes from backoffice  , but in the DB I can se the row both in ps_product and ps_product_shop , We have multistore 

Edited by millien (see edit history)

Share this post


Link to post
Share on other sites
  • 5 months later...
  • 8 months later...

I'm sorry for bringing up an old topic, but I believe there is interest in my answer.

I created a script to delete many products using the delete () method of the Product class. With the tables with the engine in InnoDB, it was taking about 14 to 17 seconds per product. After following the instructions of another topic here on the forum, changing the engine of the tables to MyISAM, the time to delete the products decreased to 6 to 7 seconds.

To change the engine, Just export your DB as .sql file, edit-it with gedit or notepad. Replace 'ENGINE = INNODB' with 'ENGINE = MyISAM' and Save the file edited. Number or replacement done should be the number of your tables. Import it to MySQL (phpMyAdmin or command line).

Credits to: https://stackoverflow.com/a/49980987

Share this post


Link to post
Share on other sites
  • 1 month later...

In my opinion, this is not a good solution. MyIsam does not support transactions and using it may cause problems in the future.

I am asking for help where to look for the reason for the long deletion of the product. For me, deleting 1 product takes more than 5 minutes. Everything else like adding, editing and all in backoffice works instantly.

I have 3500 products to delete. Running the above script deletes about 150 products a day. Too slow: /

Share this post


Link to post
Share on other sites
11 hours ago, czarn-y said:

In my opinion, this is not a good solution. MyIsam does not support transactions and using it may cause problems in the future.

I am asking for help where to look for the reason for the long deletion of the product. For me, deleting 1 product takes more than 5 minutes. Everything else like adding, editing and all in backoffice works instantly.

I have 3500 products to delete. Running the above script deletes about 150 products a day. Too slow: /

Please enable profiling. It produces at the bottom of the page a list of all sql queries that are run and how long they take.

https://dh42.com/blog/prestashop-debug-profiling/

Share this post


Link to post
Share on other sites
  • 5 months later...
On 6/20/2019 at 12:48 PM, millien said:



<?php
require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'config.inc.php');
include(dirname(__FILE__) . '/init.php');
$res = Db::getInstance()->executeS('SELECT `id_product` FROM `'._DB_PREFIX_.'product` WHERE id_product = "76" ');
echo "<p>(".date('Y/m/d H:i:s').") Starting to delete products...</p>";
if ($res) {
    foreach ($res as $row) {
        echo "<p>(".date('Y/m/d H:i:s').") Deleting product with ID <b>".$row['id_product']."</b>...";
        $p = new Product($row['id_product']);
        if(!$p->delete()) {
            echo " <span style='color: red'>Error deleting this product!</span></p>";
        } else {
            echo " <span style='color: green'>DELETED</span></p>";
        }

    }
}
?>

 

I tried one product product ID 76 after I run it the product deletes from backoffice  , but in the DB I can se the row both in ps_product and ps_product_shop , We have multistore 

I've just discovered that product->delete() has a bug that prevents it from deleting the product. Underneathdelete() checks what is the shop in your context and doesn't use the shop id used for loading the product. E.g.

$product = new Product(1234);
$product->delete();

It will delete the product in shop A, if you execute your script from shop A URL, only. It will not delete the product at all from other shops. To delete your product in shop B, you must execute your script from shop B URL.

If you try loading the product with shop B, it won't work, too.

$product = new Product(1234, false, null, shop_B_id);
$product->delete();

I've looked at PrestaShop Product->delete() and it seems that underneath it's taking the shop ID from your current context, not from the product when it was loaded.

To me that's a bug that should be fixed. If the product is loaded without id_shop, it should delete the product from all shops. If you load the product specifying id_shop, it should delete the product from the given id_shop, regardless of the domain it was executed from. I'm looking for solution.

 

UPDATE:

For product->delete() to work in multistore environment, you need to set shop's context it's executed in. E.g.

Shop::setContext(Shop::CONTEXT_ALL);

will delete the product from all shops.

Edited by mlemanczyk (see edit history)
  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More