Delete Product Duplicates from OpenCart DB

csl_opencart_delete_product_duplicates.php

<?php
// Load OpenCart framework
require_once('config.php');
require_once(DIR_SYSTEM . 'startup.php');

// Start OpenCart Registry
$registry = new Registry();
$loader = new Loader($registry);
$registry->set('load', $loader);
$config = new Config();
$registry->set('config', $config);

// Load the database
$db = new DB(DB_DRIVER, DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE, DB_PORT);

// Step 1: Find duplicate products by 'model'
$query = $db->query("
    SELECT product_id, model 
    FROM `" . DB_PREFIX . "product` 
    WHERE model IN (
        SELECT model 
        FROM `" . DB_PREFIX . "product` 
        GROUP BY model 
        HAVING COUNT(*) > 1
    ) 
    ORDER BY model, product_id
");

// Initialize an array to track models and keep one product per model
$kept_products = array();

// Step 2: Loop through the products and delete duplicates
foreach ($query->rows as $row) {
    $product_id = (int)$row['product_id'];
    $model = $row['model'];

    // If we haven't seen this model yet, keep this product
    if (!isset($kept_products[$model])) {
        $kept_products[$model] = $product_id;
    } else {
        // Step 3: Delete the product and all associated data
        deleteProduct($db, $product_id);
    }
}

// Function to delete a product and all related data
function deleteProduct($db, $product_id) {
    // Delete from product table
    $db->query("DELETE FROM `" . DB_PREFIX . "product` WHERE product_id = '" . (int)$product_id . "'");

    // Delete associated data from related tables
    $db->query("DELETE FROM `" . DB_PREFIX . "product_description` WHERE product_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "product_to_category` WHERE product_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "product_to_store` WHERE product_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "product_attribute` WHERE product_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "product_option` WHERE product_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "product_option_value` WHERE product_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "product_discount` WHERE product_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "product_special` WHERE product_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "product_image` WHERE product_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "product_reward` WHERE product_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "product_related` WHERE product_id = '" . (int)$product_id . "' OR related_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "product_to_layout` WHERE product_id = '" . (int)$product_id . "'");
    $db->query("DELETE FROM `" . DB_PREFIX . "review` WHERE product_id = '" . (int)$product_id . "'");

    // If your OpenCart store has additional product-related tables, delete those records here

    echo "Deleted duplicate product with ID: " . $product_id . "\n";
}

echo "Duplicate products removed, only unique products by model remain.";