如何通过表格网格表单在PHP MySQL中批量插入非空数据？

Got it, let's tackle this problem step by step. You want a grid-based form to batch insert records into your database, but only save rows that have valid (non-empty) values—ignoring completely empty rows. Here's how to pull this off, covering both the frontend HTML and backend logic (I'll use PHP as an example since it's common for this use case, but you can adapt it to your language of choice):

First, let's finish and optimize your HTML form. We'll use array-named input fields so the backend can easily process batch data, plus add a handy button to dynamically add new rows:

<form method="POST" action="process_insert.php">
  <table border="1" cellpadding="8">
    <thead>
      <tr>
        <th>Kode Barang</th>
        <th>Nama</th>
        <th>Quantity</th>
        <th>Satuan</th>
        <th>Harga</th>
        <th>Jumlah</th>
      </tr>
    </thead>
    <tbody>
      <!-- Initial row -->
      <tr>
        <td><input type="text" name="kode_barang[]" placeholder="Kode barang"></td>
        <td><input type="text" name="nama[]" placeholder="Nama barang"></td>
        <td><input type="number" step="0.01" name="quantity[]" placeholder="Qty"></td>
        <td><input type="number" step="0.01" name="satuan[]" placeholder="Satuan"></td>
        <td><input type="number" step="0.01" name="harga[]" placeholder="Harga"></td>
        <td><input type="number" step="0.01" name="jumlah[]" placeholder="Jumlah"></td>
      </tr>
      <!-- Dynamic row adder -->
      <tr>
        <td colspan="6">
          <button type="button" onclick="addNewRow()">Add New Row</button>
        </td>
      </tr>
    </tbody>
  </table>
  <br>
  <button type="submit">Save Valid Records</button>
</form>

<script>
function addNewRow() {
  const tbody = document.querySelector('table tbody');
  const newRow = document.createElement('tr');
  newRow.innerHTML = `
    <td><input type="text" name="kode_barang[]" placeholder="Kode barang"></td>
    <td><input type="text" name="nama[]" placeholder="Nama barang"></td>
    <td><input type="number" step="0.01" name="quantity[]" placeholder="Qty"></td>
    <td><input type="number" step="0.01" name="satuan[]" placeholder="Satuan"></td>
    <td><input type="number" step="0.01" name="harga[]" placeholder="Harga"></td>
    <td><input type="number" step="0.01" name="jumlah[]" placeholder="Jumlah"></td>
  `;
  // Insert before the "add row" button row
  tbody.insertBefore(newRow, tbody.lastElementChild);
}
</script>

• The name="field[]" syntax lets the backend receive all values as arrays, making batch processing straightforward.
• Using type="number" with step="0.01" ensures users can only input valid decimal values for numeric fields.

Next, we'll write the backend code to validate rows, skip empty ones, and insert valid records safely (using prepared statements to prevent SQL injection):

<?php
// Database connection (update with your credentials)
$dbHost = "localhost";
$dbUser = "your_username";
$dbPass = "your_password";
$dbName = "your_database";

$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

if ($_SERVER["REQUEST_METHOD"] === "POST") {
  // Retrieve all form data as arrays (fallback to empty arrays if not present)
  $kodeBarang = $_POST['kode_barang'] ?? [];
  $nama = $_POST['nama'] ?? [];
  $quantity = $_POST['quantity'] ?? [];
  $satuan = $_POST['satuan'] ?? [];
  $harga = $_POST['harga'] ?? [];
  $jumlah = $_POST['jumlah'] ?? [];

  // Prepare SQL statement (prevents SQL injection)
  $stmt = $conn->prepare("INSERT INTO your_table_name (kode_barang, nama, quantity, satuan, harga, jumlah) VALUES (?, ?, ?, ?, ?, ?)");
  // Bind parameters: "ssdddd" = string, string, double, double, double, double
  $stmt->bind_param("ssdddd", $kb, $nm, $qty, $sat, $hrg, $jml);

  $insertedRows = 0;
  $totalRows = count($kodeBarang);

  // Loop through each row of data
  for ($i = 0; $i < $totalRows; $i++) {
    // Trim whitespace from all values
    $kb = trim($kodeBarang[$i] ?? '');
    $nm = trim($nama[$i] ?? '');
    $qty = trim($quantity[$i] ?? '');
    $sat = trim($satuan[$i] ?? '');
    $hrg = trim($harga[$i] ?? '');
    $jml = trim($jumlah[$i] ?? '');

    // Skip completely empty rows
    if (empty($kb) && empty($nm) && empty($qty) && empty($sat) && empty($hrg) && empty($jml)) {
      continue;
    }

    // Convert empty numeric fields to NULL (adjust if your DB requires 0 instead)
    $qty = $qty === '' ? null : (float)$qty;
    $sat = $sat === '' ? null : (float)$sat;
    $hrg = $hrg === '' ? null : (float)$hrg;
    $jml = $jml === '' ? null : (float)$jml;

    // Execute the insert
    if ($stmt->execute()) {
      $insertedRows++;
    } else {
      echo "Error inserting row " . ($i + 1) . ": " . $stmt->error . "<br>";
    }
  }

  echo "Successfully inserted $insertedRows out of $totalRows valid rows.";

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

Key Points: #

• Prepared Statements: Never concatenate user input directly into SQL—this protects against SQL injection attacks.
• Row Validation: We check if a row is completely empty before skipping it. You can add stricter rules (e.g., require kode_barang and nama to be non-empty) by adjusting the condition.
• Data Handling: Empty numeric fields are converted to NULL (change this to 0 if your database doesn't allow NULL values for those columns).

• Client-Side Validation: Add JavaScript to check rows before submission (e.g., highlight missing required fields) to reduce unnecessary backend requests.
• Other Languages: If you're using Python (Django/Flask), Node.js, or Java, the core logic stays the same: receive array data, loop through each index, validate rows, and execute safe bulk inserts.
• Bulk Inserts: For very large batches, you can optimize by building a single bulk insert query instead of executing one per row (but keep prepared statement practices in mind).

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