Jump to content
silvioscavone

Massive product deleting

Recommended Posts

Hello,

I ask help for an urgent problem:

I have to delete massively 100.000 products inside an id range.

I found a PHP script that should do the job in a past thread, but I’m not sure it works without any damage to the website and looks like it deletes all the products, not a specific range.

I have a good PHP background but I don’t have a deep knowledge of prestashop , so I don’t know how to do it properly.

If anyone can help, it would be appreciated.

Thank you very much in advance,

 

Silvio

 

 

 

 

Share this post


Link to post
Share on other sites

Your post raises many questions:

  - which script did you find?

 - is it PS 1.6 or 1.7? 1.7 has database constraints so it needs a different handling.

 - you write that your PHP is good but you don't mention your mysql knowledge. It doesn't seem hard to me to know whether a script deletes all products or a segment. And it doesn't seem hard to add a condition like "WHERE id_product>1000 AND id_product<2000" to every query..

Share this post


Link to post
Share on other sites

Hello,

thank you for your reply.

I found something on the following thread:

https://www.prestashop.com/forums/topic/609319-solved-delete-massive-products-from-database-10420-products/

I'm running PS 1.6

I have low MySQL knowledge but I understand simple queries.

You are definitely right, I think it won't be hard, just I need to know how to do it properly because I can't do any mistake.

I need something that does exactly what the BO "Massive action -> Delete" does when selecting products,

but I need it specifying a product id range.

 

Do you think the script in that thread could work and could I add the condition you mention somewhere ?

"WHERE id_product>1000 AND id_product<2000"

 

I thank you very much for your help,

 

Silvio

Share this post


Link to post
Share on other sites

Something like

$res = Db::getInstance()->executeS('SELECT `id_product` FROM `'._DB_PREFIX_.'product` WHERE id_product > 100 AND product < 200 ORDER BY `id_product` DESC LIMIT 100 ');

 

Note that the script handles 100 products at a time due to the server timeout. That will be quite a few page refreshes for your 100,000 products....

Edited by musicmaster (see edit history)

Share this post


Link to post
Share on other sites

Hi,

I uploaded the script on website root, but it stuck on “Starting to delete products...”

Looks like no product on that range has been deleted (I checked the first and the last).

 I can’t understand the reason.

Do you have any explanation?

Share this post


Link to post
Share on other sites

Well, how about make it in "proper" PHP way and create a simple module like. 
 

require_once(dirname(__FILE__).'/../../config/config.inc.php');

$products_to_delete = Db::getInstance()->executeS('
    SOME QUERY TO products_id You want to delete
');

foreach ($products_to_delete as $product_id) {
    $p = new Product($product_id);
    echo 'Deleted '.$p->name.' with ID:'.$product_id.' <br>';

    $p->delete();
}

move this to some module folder and run it from browser. 

Be sure to check $products_to_delete with ddd($produts_to_delete) to see if You query contains proper ids.

It should work after few adjustments but I didn't test it. Maybe it will want to $p->save() after delete. I didn't test it. 

Share this post


Link to post
Share on other sites

@hakery2 - I know your code is not meant to be exhaustive, but when I tried it, the call to the config.inc.php file prevents any of the remaining code to run. Instead it just loads the shop's front page. 

Am I doing something wrong?

I am on 1.6.x and would just like to load the product class as you've done.

I've put that bit of code in a module folder, and am running it domain.com/modules/delprod/delprod.php

Any help appreciated!

Share this post


Link to post
Share on other sites

Thanks hakeryk2

It was because in the url I needed to reference the shop ID, so:

domain.com/modules/delprod/delprod.php?id_shop=1

otherwise it runs something in the config.

Share this post


Link to post
Share on other sites

I didn't know that You have multistore ;) It was created for non-multistore but I am glad that it works for You.

Share this post


Link to post
Share on other sites

Hello

Same question but I´d like to delete images, combinations etc... as well.

Admin panel takes a long time to delete ONE product.  I can´t think how long time to delete more than a thousand...

Any solution ?

 

Thanks for help

Share this post


Link to post
Share on other sites

rule of thumb, do not delete anything, simply disable

unless the process uses native PS product delete class then you will probably/most likely create issues, for example orders placed on product you delete.

be warned, deleting data via mysql can easily destroy your shop, maybe not visibly as first then later you will start to see unexplainable anomalies, then it's to late.

  • Like 1

Share this post


Link to post
Share on other sites

Yes, I know.

They are already disabled but I'd like to reduce sql tables to improve general perfomance.

Thanks anyway

Share this post


Link to post
Share on other sites
On 7/19/2019 at 2:14 PM, renzogr said:

Hello

Same question but I´d like to delete images, combinations etc... as well.

Admin panel takes a long time to delete ONE product.  I can´t think how long time to delete more than a thousand...

Any solution ?

 

Thanks for help

Hi,

If   you need  to delete   multiple  product  images  at   a time  you can use this   module:

 

  • Like 1

Share this post


Link to post
Share on other sites

to improve mysql, get faster hosting and if possible nvme ssd...it go zoom freaking zoom lol

Share this post


Link to post
Share on other sites

hanks for your post Patron.

 

My host is very fast...  But I'd like to clean sql files deleting old products and images.

Share this post


Link to post
Share on other sites
9 hours ago, renzogr said:

hanks for your post Patron.

 

My host is very fast...  But I'd like to clean sql files deleting old products and images.

You can open make a simple PHP

$i=1;
for ($i =1; $i<=1000; $i++) {
            $id_product = $i;
            $pd = new Product($id_product);
            $pd->delete();
            unset($pd);
        }

This script will remove all product ID from 1-> 1000, also it will remove all SQL related to this product, images, combination, specify price...

Share this post


Link to post
Share on other sites

Thank you TDSOFT.

How would this PHP look to delete inactive products instead of IDs?

Great job.

 

Thanks again

 

Share this post


Link to post
Share on other sites

You can also use the script repeatedly until all products have been removed.
Set up:
1. max_count per batch deleted files
2 time_out Timeout of the script reload

<?php

  ini_set('display_errors', 1);
  ini_set('display_startup_errors', 1);
  error_reporting(E_ALL);

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

  $max_count = 1;
  $products_to_delete = Db::getInstance()->executeS('SELECT * FROM '._DB_PREFIX_.'product ORDER BY id_product ASC LIMIT '.$max_count);
  $time_out = 30;


  if (!empty($products_to_delete)) { 
           header("Refresh:$time_out");
         
              foreach ($products_to_delete as $product_id) {
                    
                    $p = new Product($product_id['id_product']);
                    
                    if ($p->delete()) {
                       echo 'Deleted '.$product_id['id_product'].' <br>';
                    }              
            }
            
            
  }  else { Exit; }

 

Share this post


Link to post
Share on other sites

If you want to remove only inactive products, just type in SQL query:

active = 0

Db::getInstance()->executeS('SELECT * FROM '._DB_PREFIX_.'product WHERE active = 0 ORDER BY id_product ASC LIMIT '.$max_count);

 

Share this post


Link to post
Share on other sites

Thanks again, but I'd like to remove all info related to them.

 

Thanks 

Share this post


Link to post
Share on other sites
On 2/28/2020 at 12:15 AM, renzogr said:

Thank you TDSOFT.

How would this PHP look to delete inactive products instead of IDs?

Great job.

 

Thanks again

 

$i=1;
for ($i =1; $i<=1000; $i++) {
            $id_product = $i;
            $pd = new Product($id_product);
            if ($pd->active == 0) {
            	$pd->delete();
 			}
            unset($pd);
        }

 

Share this post


Link to post
Share on other sites

What does all related information mean?


The delete () function is the same as you would click the Delete button in the product list in the administration.
So delete () removes everything you added to the product.

My script can remove all products according to the condition batchwise without restarting.
E.g. your server has a limit of max_execution_time = 60, so you can't remove 1000 products, including images.
A maximum of 10 - 50 products can be removed in 60 seconds depending on the number of product images.
So you change $ max_count = 1 to $ max_count = 25 in my script.
Next, you change $ time_out = 30 to $ time_out = 60.
The script runs itself repeatedly after 60 seconds until all products are removed.
So you run the script only once and go out for a walk.
First, it would be good to find out the parameters of your web host and how many products the script is able to remove in a given time period.
Etc.
If you want to remove only inactive products, you will change the SQL query to the one I sent in the second post.

Share this post


Link to post
Share on other sites

Hi D. Tengler

 

I meaning that all data related to that (inactive) product should be deleted: combinations, prices, images...  everything.

 

 

Share this post


Link to post
Share on other sites

Hi.

And I write to you repeatedly that the delete () function removes everything.
You haven't started deleting yet! When you run it, you will see that everything is removed.
Make a backup of the database, and then run the script.
Check that everything is removed.
If not, you have a database backup that you can restore.
Write when you run the script and finish the action.
If you find something left in the database from the removed products, write here what you found.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

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