PHP实现多批次生产表单数据批量插入MySQL数据库求助

Hey there! The issue here is that your PHP code isn't looping through the array of batch data being sent from your dynamic form. Right now you’re trying to plug entire array variables directly into your SQL query—PHP just converts those arrays to the string "Array" when you do that, which is why only the first batch (or invalid data) is being inserted. On top of that, your current code has a critical SQL injection risk since you’re concatenating user input directly into your query. Let’s fix both issues together.

Corrected PHP Code (With Looping & Safe Prepared Statements) #

<?php
// First, validate required POST data exists
if (!isset($_POST['productionDate'], $_POST['operator'], $_POST['line'], $_POST['disc'], $_POST['plastic'], $_POST['color'], $_POST['colorUsed'], $_POST['plasticUsed'], $_POST['regrind'], $_POST['firsts'], $_POST['seconds'], $_POST['colorChange'])) {
    die("Error: Missing required form data");
}

// Extract form data
$productionDate = $_POST['productionDate'];
$operator = $_POST['operator'];
$line = $_POST['line'];
// Batch data are arrays - rename variables for clarity
$discs = $_POST['disc'];
$plastics = $_POST['plastic'];
$colors = $_POST['color'];
$colorUseds = $_POST['colorUsed'];
$plasticUseds = $_POST['plasticUsed'];
$regrinds = $_POST['regrind'];
$firsts = $_POST['firsts'];
$seconds = $_POST['seconds'];
$colorChanges = $_POST['colorChange'];

// Verify all batch arrays have the same length (no missing entries)
$totalBatches = count($discs);
$batchArrays = [$plastics, $colors, $colorUseds, $plasticUseds, $regrinds, $firsts, $seconds, $colorChanges];
foreach ($batchArrays as $array) {
    if (count($array) !== $totalBatches) {
        die("Error: Mismatched batch data - some fields are incomplete");
    }
}

// Database connection
$host = "localhost";
$dbusername = "username";
$dbpassword = "password";
$dbname = "testDB";

$conn = new mysqli($host, $dbusername, $dbpassword, $dbname);
if (mysqli_connect_error()) {
    die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}

// Prepare SQL statement (prevents SQL injection)
$stmt = $conn->prepare("INSERT INTO PRODUCTION_ENTRY (PRODUCTION_DATE, OPERATOR, PRODUCTION_LINE, DISC, PLASTIC, COLOR, COLOR_USED_GRAMS, PLASTIC_USED_LBS, REGRIND_LBS, FIRSTS, SECONDS, COLOR_CHANGE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
if (!$stmt) {
    die("Prepare failed: " . $conn->error);
}

// Bind parameters (adjust type codes to match your DB column types)
// s = string, i = integer, d = decimal; update if your columns use different types
$stmt->bind_param("sssssiiiiii", $productionDate, $operator, $line, $currentDisc, $currentPlastic, $currentColor, $currentColorUsed, $currentPlasticUsed, $currentRegrind, $currentFirsts, $currentSeconds, $currentColorChange);

// Loop through each batch and insert records
$successfulInserts = 0;
for ($i = 0; $i < $totalBatches; $i++) {
    // Assign values for the current batch
    $currentDisc = $discs[$i];
    $currentPlastic = $plastics[$i];
    $currentColor = $colors[$i];
    // Cast numeric inputs to correct types
    $currentColorUsed = (int)$colorUseds[$i];
    $currentPlasticUsed = (int)$plasticUseds[$i];
    $currentRegrind = (int)$regrinds[$i];
    $currentFirsts = (int)$firsts[$i];
    $currentSeconds = (int)$seconds[$i];
    $currentColorChange = (int)$colorChanges[$i];

    // Execute insert for this batch
    if ($stmt->execute()) {
        $successfulInserts++;
    } else {
        echo "Failed to insert batch " . ($i + 1) . ": " . $stmt->error . "<br>";
    }
}

// Output result
echo "$successfulInserts out of $totalBatches batches were submitted successfully!";

// Clean up resources
$stmt->close();
$conn->close();
?>

Key Improvements Explained: #

• Batch Looping: We count the number of batches from the discs array, then loop through each index to process every row the operator added dynamically.
• Prepared Statements: This completely eliminates SQL injection risks by separating the SQL structure from user input. We bind values to placeholders instead of concatenating raw input.
• Data Validation: We check that all required data exists and that all batch arrays are the same length (so you don’t end up with partial or missing records).
• Type Casting: Numeric form inputs are cast to integers to ensure we’re inserting the correct data type into your database columns.

Quick Notes: #

• Adjust the parameter type codes in bind_param if your database columns use different types (e.g., use d instead of i for decimal values like weights).
• Confirm your database user has INSERT permissions for the PRODUCTION_ENTRY table.

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