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

[devzk]

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 searched and found this: https://www.prestashop.com/forums/topic/790[spam-filter]remove-all-products-from-the-database/, but it is from 2011, pretty old.

 

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 May 11, 2017 by devzk (see edit history)

[Daresh]

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();
	}
}

[devzk]

 

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?

[Daresh]

Yes it should.

[devzk]

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.

[Daresh]

Great, you need to use this script in some module or a standalone PHP file (but then remember to include the config.inc.php)

[devzk]

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 May 11, 2017 by devzk (see edit history)

[Daresh]

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

[silvioscavone]

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 July 2, 2017 by silvioscavone (see edit history)

[nnvlahovic]

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

[hstrom]

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 November 18, 2018 by hstrom (see edit history)

[ElectrostuffUG]

Is there a way to delete Products without using Prestashops internal structures? I mean how exactly or what exactly does

if(!$p->delete()) {

looks as sql query? The delete function 😃

Can you help me with that?

[Daresh]

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

[ElectrostuffUG]

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?

[Daresh]

The delete function will have exactly the same effect as if you clicked "delete" on a product in your back office, nothing more and nothing less.

[idee]

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,

 

[Daresh]

Sure it is possible, but the script would need to be adapted.

[idee]

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 January 17, 2019 by idee (see edit history)

[Daresh]

Sure, send me a message so we can discuss the details.

[zklid]

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

[Daresh]

Try this change:

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

 

[zklid]

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?

[Daresh]

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

[zklid]

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?

[Daresh]

Yes, the delete() function should do that.

[hstrom]

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.

 

[hstrom]

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.

 

[Daresh]

For a single shot or cron delete remove this part:

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

 

[hstrom]

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

[Daresh]

Yes, it will do nothing.

[hstrom]

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.

[millien]

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 ? 

[Daresh]

The script deletes everything in the same way as when you click "Delete" on a product in your back office.

[millien]

<?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 June 20, 2019 by millien (see edit history)

[Presta_lover]

Guys please, could you explain for newbies how to implement this script?

step by step

thanks a lot

[FACCRA]

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

[czarn-y]

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: /

[musicmaster]

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/

[mlemanczyk]

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 February 27, 2021 by mlemanczyk (see edit history)

[blumouse]

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 am very ignorant in some respects, but just copy it exactly as it is or do you have to customize it in some fields? and yes which ones?

[Dreamer83]

ciao ho visto lo script ma domanda le immagini come vengono cancellate?

[bruno.iaria-brik]

No le immagini non vengono cancellate, ma questo non accade neanche se li elimini manualmente, mi sono trovati diversi siti intasati a livello di grandezza causate da immagini inutili di prodotti eliminati, ci sono diversi moduli che fanno proprio questo, eliminano tutte le foto prodotti, brand etc di prodotti non piu disponibili nello store! 

[Andrejkov]

On 5/11/2017 at 11:53 AM, Daresh said:

Make it:

And you have a fully automatic script

Hi,

It seems that the script is for PS 1.6 version. Are you able to modify the script so that I can run it on PS 1.7.8? Thanks a lot.

Regards.

 

[apollux]

And if you really want to take it further, you can do it with delete_products.csv file, just by adding the id's that you want to be delete into row id_product. It should delete images and any other characteristics of the product id. Remember, back up you database before doing so.

The script will be saved as something you want .php. All the ID's from the CSV will be sent into an array. Script 2 will provide a much faster deletion, considering that you have enough hosting resources to do so.

script 1.

<?php
// Include PrestaShop configuration
require_once dirname(__FILE__) . '/config/config.inc.php';
require_once dirname(__FILE__) . '/init.php';

// Path to the CSV file
$csvFile = dirname(__FILE__) . '/delete_products.csv';

// Check if the CSV file exists
if (!file_exists($csvFile)) {
    die("CSV file not found.");
}

// Initialize an array to store the product IDs from the CSV
$product_ids_to_delete = [];

// Open the CSV file and read product IDs into an array
if (($handle = fopen($csvFile, "r")) !== FALSE) {
    $header = fgetcsv($handle, 1000, ";"); // Adjust the delimiter if needed
    $idIndex = array_search('id_product', $header);

    if ($idIndex === false) {
        die("Column 'id_product' not found in CSV.");
    }

    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
        $product_ids_to_delete[] = (int)$data[$idIndex];
    }

    fclose($handle);
} else {
    die("Error opening CSV file.");
}

// Confirm that we have IDs to delete
if (empty($product_ids_to_delete)) {
    die("No product IDs found to delete.");
}

// Establish a connection to the PrestaShop database
$db = Db::getInstance();

foreach ($product_ids_to_delete as $product_id) {
    // Load the product object
    $product_obj = new Product($product_id);

    if (Validate::isLoadedObject($product_obj)) {
        // Delete product images from server
        $product_obj->deleteImages();

        // Delete product and associated data from the database
        if ($product_obj->delete()) {
            echo "Product ID " . $product_id . " deleted successfully.<br>";
        } else {
            echo "Failed to delete Product ID " . $product_id . ".<br>";
        }
    } else {
        echo "Product ID " . $product_id . " does not exist.<br>";
    }
}

echo "All specified products in the CSV have been processed.";

script 2

<?php
// Include PrestaShop configuration
require_once dirname(__FILE__) . '/config/config.inc.php';
require_once dirname(__FILE__) . '/init.php';

// Path to the CSV file
$csvFile = dirname(__FILE__) . '/delete_products.csv';

// Check if the CSV file exists
if (!file_exists($csvFile)) {
    die("CSV file not found.");
}

// Load product IDs from CSV
$product_ids_to_delete = [];

if (($handle = fopen($csvFile, "r")) !== FALSE) {
    $header = fgetcsv($handle, 1000, ";");
    $idIndex = array_search('id_product', $header);

    if ($idIndex === false) {
        die("Column 'id_product' not found in CSV.");
    }

    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
        $product_ids_to_delete[] = (int)$data[$idIndex];
    }

    fclose($handle);
}

// Check if there are IDs to delete
if (empty($product_ids_to_delete)) {
    die("No product IDs found to delete.");
}

// Convert the array of IDs to a comma-separated list for SQL
$id_list = implode(',', $product_ids_to_delete);

// Establish a connection to the PrestaShop database
$db = Db::getInstance();

// Delete products and associated data in bulk
try {
    // Delete from main product tables
    $tables = ['product', 'product_shop', 'product_lang', 'category_product', 'product_attribute', 'stock_available'];
    foreach ($tables as $table) {
        $sql = "DELETE FROM `" . _DB_PREFIX_ . "$table` WHERE `id_product` IN ($id_list)";
        $db->execute($sql);
    }

    // Optional: delete images from the server if you are limited by space
    foreach ($product_ids_to_delete as $product_id) {
        $product = new Product($product_id);
        $product->deleteImages();
    }

    echo "Products with specified IDs have been deleted successfully.";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}