duplication of products in prestashop backend and front

Question

I have imported 16 000 products, and now I have 57 000 products. Can any one help me please to delete all duplicated products as you see in picture. Product with different id and same reference.

Image backoffice:

Image front:

Answer 1

You can try something like that, you can test it in development environment, you should create a php file in your root project.

require_once('config/config.inc.php');
require_once('init.php');

$query = "select id_product,reference from " . _DB_PREFIX_ . "product where active=1";
$res = Db::getInstance()->ExecuteS($query);

foreach($res as $prod){
    $query = "select id_product from " . _DB_PREFIX_ . "product where  reference=$prod['reference']";
    $res = Db::getInstance()->ExecuteS($query);
    $count = count($res);
    if($count){
        foreach ($res as $key => $p) {
            if (--$count <= 0) { 
            // to not delete the last occurrence for a given reference
               break;
            }
            $id_product = $p['id_product'];
            $product = new Product((int)$id_product);
            if($product->delete())
               echo 'product '.$id_product.' is deleted';
        }
    
    }
}