csv文件上传到数据库并检查是否有重复行

<?php
/**
 * Plugin Name: Optimized Multi CSV Upload with Image Processing
 * Description: Allows users to upload multiple CSV files with image handling, process them using AJAX, and display data on frontend with duplicate prevention
 * Version: 1.4
 * Author: Your Name
 */

// ... (previous code remains the same)

class Multi_CSV_Upload_Process_Display {
    // ... (previous methods remain the same)

    private function insert_csv_data($file_path) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'csv_data';

        if (($handle = fopen($file_path, "r")) !== FALSE) {
            $header = fgetcsv($handle, 1000, ",");
            $image_column_index = array_search('image', array_map('strtolower', $header));
            
            // Determine which columns to use as unique identifiers
            $unique_columns = $this->get_unique_columns($header);
            
            // Prepare the SQL statement
            $placeholders = array_fill(0, count($header), '%s');
            $sql = $wpdb->prepare(
                "INSERT INTO $table_name (" . implode(',', $header) . ") 
                 VALUES (" . implode(',', $placeholders) . ")
                 ON DUPLICATE KEY UPDATE " . 
                 implode(', ', array_map(function($col) { return "$col = VALUES($col)"; }, $header))
            );

            // Start transaction for better performance with multiple inserts
            $wpdb->query('START TRANSACTION');

            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                if (count($header) === count($data)) {
                    // Process image if image column exists
                    if ($image_column_index !== false) {
                        $image_url = $data[$image_column_index];
                        $uploaded_image_url = $this->process_image($image_url);
                        if ($uploaded_image_url) {
                            $data[$image_column_index] = $uploaded_image_url;
                        }
                    }

                    // Execute the prepared statement
                    $wpdb->query($wpdb->prepare($sql, $data));
                }
            }

            // Commit the transaction
            $wpdb->query('COMMIT');

            fclose($handle);
        }
    }

    private function get_unique_columns($header) {
        // Define which columns should be used to determine uniqueness
        // This could be configurable in the plugin settings
        $potential_unique_columns = ['id', 'email', 'unique_identifier'];
        return array_intersect($potential_unique_columns, $header);
    }

    public static function activate() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'csv_data';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            unique_identifier VARCHAR(255) NOT NULL,
            column1 varchar(255) NOT NULL,
            column2 varchar(255) NOT NULL,
            column3 varchar(255) NOT NULL,
            image varchar(255),
            PRIMARY KEY  (id),
            UNIQUE KEY unique_identifier (unique_identifier)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }
}

$multi_csv_upload = new Multi_CSV_Upload_Process_Display();
register_activation_hook(__FILE__, array('Multi_CSV_Upload_Process_Display', 'activate'));