WooCommerce批量导入商品图片后媒体库及产品页不显示问题求助

你的代码存在几个关键问题，导致图片无法被WordPress媒体系统正确识别，进而在后台和产品页面显示异常：

问题分析与修复点： #

1. 未初始化变量就用于查询
   代码中在检查现有附件时，$imageURL还未定义就传入查询，导致首次循环时查询条件为空，可能引发重复插入或逻辑错误。

2. 缺少附件必需的元数据
   WordPress附件除了在wp_posts表中记录，还需要在wp_postmeta中保存_wp_attached_file、_wp_attachment_metadata等关键字段，手动插入posts记录后未处理这些元数据，媒体库无法识别图片。

3. 冗余的base64编解码
   对数据库中已有的imgData先编码再解码属于多余操作，可能导致图片数据损坏。

4. 重复加载核心函数
   在foreach循环内重复加载image.php、file.php、media.php，既影响效率也可能引发冲突，应该移到循环外。

5. 无效的attachment_id获取
   刚上传图片就调用attachment_url_to_postid，此时还未插入posts记录，返回的ID无效，属于多余操作。

修复后的完整代码： #

// Define batch size and other variables
$batch_size = 200;
$total_products = $wpdb->get_var("SELECT COUNT(*) FROM products_images");
$total_batches = ceil($total_products / $batch_size);

// Load WordPress media handling functions ONCE, outside loops
require_once(ABSPATH . 'wp-admin/includes/image.php');
require_once(ABSPATH . 'wp-admin/includes/file.php');
require_once(ABSPATH . 'wp-admin/includes/media.php');

// Loop through batches
for ($batch_number = 1; $batch_number <= $total_batches; $batch_number++) {
    $offset = ($batch_number - 1) * $batch_size;
    $woo_products_images = $wpdb->get_results("SELECT * FROM `products_images` WHERE strPartNumber='BA212120' LIMIT $offset, $batch_size");

    foreach ($woo_products_images as $woo_products_image) {
        $imageID = $woo_products_image->Image_ID;
        $strPartNumber = $woo_products_image->strPartNumber;
        // 直接使用原始图片数据，去掉冗余的base64编解码
        $imageData = $woo_products_image->imgData;

        $image_filename = $strPartNumber . '_' . $imageID . '.jpg';
        // 生成预期的图片URL用于查询现有附件
        $upload_dir = wp_upload_dir();
        $imageURL = $upload_dir['url'] . '/' . $image_filename;
        
        // Check if the attachment already exists based on the image filename (更可靠，避免guid变化问题)
        $existing_attachment = $wpdb->get_row($wpdb->prepare(
            "SELECT p.ID FROM $wpdb->posts p 
            JOIN $wpdb->postmeta pm ON p.ID = pm.post_id 
            WHERE pm.meta_key = '_wp_attached_file' AND pm.meta_value = %s 
            AND p.post_type = 'attachment'",
            $image_filename
        ));

        if (!$existing_attachment) {
            // Image doesn't exist, insert it as a new attachment
            $imageFile = wp_upload_bits($image_filename, null, $imageData);
            if (!$imageFile['error']) {
                $imageURL = $imageFile['url'];
                echo "Image URL after upload: $imageURL<br>";

                // Set the post date and post date GMT
                $current_date = current_time('mysql');
                $insert_data = array(
                    'post_author' => 4,
                    'post_title' => $strPartNumber . ' ' . $imageID,
                    'post_status' => 'inherit',
                    'post_name' => sanitize_title($strPartNumber . '_' . $imageID),
                    'post_parent' => 0,
                    'guid' => $imageURL,
                    'post_type' => 'attachment',
                    'post_mime_type' => 'image/jpeg',
                    'post_date' => $current_date,
                    'post_date_gmt' => get_gmt_from_date($current_date)
                );

                $wpdb->insert($wpdb->posts, $insert_data);
                $attachment_id = $wpdb->insert_id;

                // 新增：添加附件必需的元数据
                update_post_meta($attachment_id, '_wp_attached_file', $image_filename);
                update_post_meta($attachment_id, '_wp_attachment_metadata', array());

                // Generate image metadata (thumbnails)
                $metadata = wp_generate_attachment_metadata($attachment_id, $imageFile['file']);
                wp_update_attachment_metadata($attachment_id, $metadata);
            } else {
                echo "Error uploading image: " . $imageFile['error'];
            }
        } else {
            // Image already exists, use existing attachment ID
            $attachment_id = $existing_attachment->ID;
            $imageURL = wp_get_attachment_url($attachment_id);
            echo "Image URL already exists: $imageURL<br>";
        }

        // Link image to product
        $productId = $wpdb->get_var($wpdb->prepare("SELECT post_id FROM $wpdb->postmeta WHERE meta_key='_sku' AND meta_value='%s' LIMIT 1", $strPartNumber));

        if ($productId) {
            // Set the image as the featured image for the product
            set_post_thumbnail($productId, $attachment_id);
            echo "Image (ID: $attachment_id) linked to product with SKU: $strPartNumber - product ID: $productId<br>";
        } else {
            echo "Product not found with SKU: $strPartNumber<br>";
        }
    }
}

额外说明： #

• 改用_wp_attached_file字段查询现有附件，比依赖guid更可靠，因为WordPress的guid可能会在站点迁移等场景下变化。
• 新增sanitize_title处理附件的post_name，避免特殊字符导致的URL或数据库问题。
• 手动添加_wp_attached_file元数据，确保WordPress能正确定位服务器上的图片文件。

内容的提问来源于stack exchange，提问作者Gamo SA