wc_products_remove_broken_attachments.php

<?php

require_once __DIR__ . '/init.php';

main(); // Run task

function main() {
    // 1. Get all products
    $products = getProducts();
    
    // 2. For each product remove attachments if file is missing
    foreach ($products as $product) {
        removeImageAttachentsIfFilesMissing($product);
        //exit("\nFIRST PRODUCT IMAGES CLEANED\n");
    }
}

function removeImageAttachentsIfFilesMissing($product) {
    echo "Product " . $product['ID'] . "...\n";
    $images = getProductImages($product['ID']);
    foreach ($images as $image) {
        $filePath = wp_upload_dir()['basedir'] . '/' . get_post_meta($image['ID'], '_wp_attached_file', true);
        $fileExists = file_exists($filePath);
        echo " - Attachment " . $image['ID'] . " file " . $filePath . "...\n";
        if ($fileExists == true) {
            echo "  -- File exists. SKIPPED.\n";
            continue;
        }
        echo "  -- File missing. Removing attachment...\n";
        wp_delete_post($image['ID'], true);
    }
}

function getProducts() {
    $products = db()->table('wp_posts')
            ->where('post_type', '=', 'product')
            //->limit(1)
            ->orderBY('ID','ASC')
            ->select();
    return $products;
}

function getProductImages($productId) {
    $images = db()->table('wp_posts')
            ->where('post_parent', ' = ', $productId)
            ->where('post_type', ' = ', 'attachment')
            ->where('post_mime_type', 'LIKE', 'image%')
            ->select();
    return $images;
}