PHP导入CSV时替换表头分类名为对应ID的实现方案求助

Let's break down what's wrong with your current script and fix it properly. The core issues are how you're fetching category data, building your mapping array, and handling the CSV header-to-ID replacement. Here's a revised implementation that will correctly map category names to IDs during import:

Key Issues in Your Original Script #

1. You're only fetching a single category: Your query uses WHERE category_id=:category_id, which limits you to one category—this won't work if your CSV has multiple category names in the header.
2. Inefficient lookup: The searchMultiArrays function loops through the array every time you need an ID, which is slow and unnecessary.
3. Index confusion: Unsetting CSV data keys and reassigning them can break the link between headers and their corresponding values.

Revised Working Code #

<?php
// First: Build a complete map of category names to their IDs (all enabled categories)
$categoryMap = [];
$header_qry = $con->prepare("SELECT category_name, category_id FROM category WHERE status = 1");
$header_qry->execute();
while ($row = $header_qry->fetch(PDO::FETCH_ASSOC)) {
    // Use category name as the key for instant lookup
    $categoryMap[$row['category_name']] = $row['category_id'];
}

// Read CSV header
$csvHeaders = fgetcsv($source);
// Filter out any empty header values
$csvHeaders = array_filter($csvHeaders);

// Validate all CSV headers exist in our category map
$missingCategories = array_diff($csvHeaders, array_keys($categoryMap));
if (!empty($missingCategories)) {
    $messageSession[] = [
        'type' => 'danger',
        'message' => 'Columns Mismatch: The following categories are missing or disabled - ' . implode(', ', $missingCategories)
    ];
    $_SESSION['alert_messages'] = $messageSession;
    header("location:cat_upload.php");
    exit; // Critical: Stop execution after redirect
}

// Process each CSV row
while ($csvData = fgetcsv($source)) {
    $processedRow = [];
    // Map each header name to its ID and assign the corresponding value
    foreach ($csvHeaders as $index => $categoryName) {
        $categoryId = $categoryMap[$categoryName];
        // Use ?? '' to handle cases where a CSV field is empty
        $processedRow[$categoryId] = $csvData[$index] ?? '';
    }

    // Encode to JSON and insert into batch table
    $cat_details = json_encode($processedRow, JSON_FORCE_OBJECT);
    $insert_json = $con->prepare("INSERT INTO batch_detail (batch_id, cat_details) VALUES (:batch_id, :cat_details)");
    $insert_json->bindParam(':batch_id', $batch_id);
    $insert_json->bindParam(':cat_details', $cat_details);
    $insert_json->execute();
}

// Success redirect
$messageSession[] = [
    'type' => 'success',
    'message' => 'Details Uploaded Successfully'
];
$_SESSION['alert_messages'] = $messageSession;
header("location:upload_summary.php");
exit;
?>

What This Fixes #

• Complete category mapping: We fetch all enabled categories once, creating an associative array where category names point directly to their IDs—no more looping to find matches.
• Clear validation: array_diff quickly identifies any headers that don't exist in your category table, giving specific feedback instead of a generic error.
• Clean data processing: We directly map each CSV value to its corresponding category ID using the header index, avoiding index confusion from unsetting keys.
• Safety first: Added exit after redirects to prevent leftover code from executing.

This approach ensures your batch table uses stable category IDs instead of volatile names, so you won't have to update batch data if category names change later.

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