基于给定前端代码：单按钮批量插入/更新动态表格数据到数据库

Great question! Let's break this down step by step and modify your existing code to meet all three requirements. We'll use frontend JavaScript (jQuery) for dynamic interactions and PHP for backend database operations (you can adapt the backend to your preferred language if needed).

1. Frontend Modifications #

First, let's tweak your frontend to add a display table for saved data, adjust input naming for batch handling, and add edit functionality.

Add a Display Table #

Add this section right after your input table to show saved records:

<div class="container mt-5">
  <h3>Saved Records</h3>
  <table id="displayTable" class="table table-striped">
    <thead>
      <tr>
        <th>SI. No</th>
        <th>Items / Products</th>
        <th>Qty Requested</th>
        <th>Quantity Procured</th>
        <th>Specification</th>
        <th>Actions</th>
      </tr>
    </thead>
    <tbody>
      <!-- Saved data will load here -->
    </tbody>
  </table>
</div>

Update the Input Form & Dynamic Rows #

Wrap your input table in a form, use array-style input names (for easy batch processing), and add a hidden id field to track existing records for editing:

<div class="container">
  <form id="dataForm">
    <table id="myTable" class="table order-list">
      <thead>
        <tr>
          <th width="5%">SI. No</th>
          <th width="45%">Items / Products</th>
          <th width="5%">Qty Requested</th>
          <th width="20%">Quantity Procured</th>
          <th width="10%">Specification</th>
          <th width="15%">Phone</th>
          <th width="10%">Actions</th>
        </tr>
      </thead>
      <tbody></tbody>
      <tfoot>
        <tr>
          <td colspan="6" style="text-align: left;">
            <input type="button" class="btn btn-lg btn-block" id="addrow" value="Add Row" />
          </td>
        </tr>
      </tfoot>
    </table>
    <div class="form-group text-center">
      <button type="submit" class="btn btn-warning">Save <i class="glyphicon glyphicon-send"></i></button>
    </div>
  </form>
</div>

Updated JavaScript for Dynamic Rows & AJAX #

Replace your existing script with this to handle row management, form submission, editing, and data fetching:

$(document).ready(function () {
    let counter = 1;
    let editingId = null; // Tracks the ID of the record being edited

    // Load saved data when the page loads
    loadSavedData();

    // Add new row to input table
    $("#addrow").on("click", function () {
        addNewRow();
    });

    // Delete row from input table
    $("table.order-list").on("click", ".ibtnDel", function (event) {
        $(this).closest("tr").remove();
        counter -= 1;
        // Re-number SI. No fields after deletion
        $("table.order-list tbody tr").each(function(index) {
            $(this).find('input[name="sno[]"]').val(index + 1);
        });
    });

    // Handle form submission (insert/update)
    $("#dataForm").on("submit", function(e) {
        e.preventDefault();
        const formData = $(this).serializeArray();
        const submitUrl = editingId ? `update.php?id=${editingId}` : "insert.php";

        $.ajax({
            url: submitUrl,
            type: "POST",
            data: formData,
            success: function(response) {
                const res = JSON.parse(response);
                if(res.success) {
                    alert("Data saved successfully!");
                    // Reset form and input table
                    $("#dataForm")[0].reset();
                    $("table.order-list tbody").empty();
                    counter = 1;
                    editingId = null;
                    // Refresh display table
                    loadSavedData();
                } else {
                    alert("Error saving data: " + res.message);
                }
            },
            error: function() {
                alert("Something went wrong with the request.");
            }
        });
    });

    // Helper function to add a new row (with optional edit data)
    function addNewRow(data = null) {
        const newRow = $("<tr>");
        const snoVal = data ? data.sno : counter;
        const itemsVal = data ? data.items : "";
        const qtyRVal = data ? data.qty_r : "";
        const qtyPVal = data ? data.qty_p : "";
        const specVal = data ? data.specification : "";
        const phoneVal = data ? data.phone : "";
        const idVal = data ? data.id : "";

        let cols = "";
        cols += `<td><input type="text" class="form-control" name="sno[]" value="${snoVal}" readonly/></td>`;
        cols += `<td><input type="text" class="form-control" name="items[]" value="${itemsVal}"/></td>`;
        cols += `<td><input type="text" class="form-control" name="qty_r[]" value="${qtyRVal}"/></td>`;
        cols += `<td><input type="text" class="form-control" name="qty_p[]" value="${qtyPVal}"/></td>`;
        cols += `<td><input type="text" class="form-control" name="specification[]" value="${specVal}"/></td>`;
        cols += `<td><input type="text" class="form-control" name="phone[]" value="${phoneVal}"/></td>`;
        cols += `<td><input type="hidden" name="id[]" value="${idVal}"/><input type="button" class="ibtnDel btn btn-md btn-danger" value="Delete"></td>`;
        
        newRow.append(cols);
        $("table.order-list tbody").append(newRow);
        counter++;
    }

    // Load saved data into the display table
    function loadSavedData() {
        $.ajax({
            url: "fetch.php",
            type: "GET",
            success: function(response) {
                const records = JSON.parse(response);
                const displayTbody = $("#displayTable tbody");
                displayTbody.empty();

                records.forEach(record => {
                    const row = `<tr>
                        <td>${record.sno}</td>
                        <td>${record.items}</td>
                        <td>${record.qty_r}</td>
                        <td>${record.qty_p}</td>
                        <td>${record.specification}</td>
                        <td>
                            <button class="btn btn-sm btn-primary edit-btn" data-id="${record.id}">Edit</button>
                        </td>
                    </tr>`;
                    displayTbody.append(row);
                });

                // Attach edit click event to each edit button
                $(".edit-btn").on("click", function() {
                    const recordId = $(this).data("id");
                    // Fetch the specific record for editing
                    $.ajax({
                        url: `fetch_single.php?id=${recordId}`,
                        type: "GET",
                        success: function(response) {
                            const record = JSON.parse(response);
                            // Clear input table and populate with edit data
                            $("table.order-list tbody").empty();
                            counter = 1;
                            editingId = recordId;
                            addNewRow(record);
                        }
                    });
                });
            }
        });
    }
});

2. Backend Setup (PHP Examples) #

Create these PHP files to handle database operations. First, make sure you have a database connection set up.

1. Database Connection (db.php) #

<?php
$servername = "localhost";
$username = "your_db_username";
$password = "your_db_password";
$dbname = "your_db_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

2. Batch Insert (insert.php) #

Handles inserting multiple rows at once:

<?php
include 'db.php';
header('Content-Type: application/json');

$sno = $_POST['sno'];
$items = $_POST['items'];
$qty_r = $_POST['qty_r'];
$qty_p = $_POST['qty_p'];
$specification = $_POST['specification'];
$phone = $_POST['phone'];

$success = true;
$message = "";

// Prepare insert statement
$stmt = $conn->prepare("INSERT INTO product_data (sno, items, qty_r, qty_p, specification, phone) VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param("isssss", $sno_val, $items_val, $qty_r_val, $qty_p_val, $spec_val, $phone_val);

// Loop through each row and insert
foreach($sno as $index => $val) {
    $sno_val = $sno[$index];
    $items_val = $items[$index];
    $qty_r_val = $qty_r[$index];
    $qty_p_val = $qty_p[$index];
    $spec_val = $specification[$index];
    $phone_val = $phone[$index];

    if(!$stmt->execute()) {
        $success = false;
        $message = $stmt->error;
        break;
    }
}

$stmt->close();
$conn->close();

echo json_encode([
    'success' => $success,
    'message' => $success ? "All records inserted successfully" : $message
]);
?>

3. Update Record (update.php) #

Handles updating an existing record:

<?php
include 'db.php';
header('Content-Type: application/json');

$id = $_GET['id'];
$sno = $_POST['sno'][0];
$items = $_POST['items'][0];
$qty_r = $_POST['qty_r'][0];
$qty_p = $_POST['qty_p'][0];
$specification = $_POST['specification'][0];
$phone = $_POST['phone'][0];

$stmt = $conn->prepare("UPDATE product_data SET sno=?, items=?, qty_r=?, qty_p=?, specification=?, phone=? WHERE id=?");
$stmt->bind_param("isssssi", $sno, $items, $qty_r, $qty_p, $specification, $phone, $id);

if($stmt->execute()) {
    $response = ['success' => true, 'message' => "Record updated successfully"];
} else {
    $response = ['success' => false, 'message' => $stmt->error];
}

$stmt->close();
$conn->close();

echo json_encode($response);
?>

4. Fetch All Records (fetch.php) #

Retrieves all saved records for the display table:

<?php
include 'db.php';
header('Content-Type: application/json');

$result = $conn->query("SELECT * FROM product_data");
$records = [];

while($row = $result->fetch_assoc()) {
    $records[] = $row;
}

$conn->close();
echo json_encode($records);
?>

5. Fetch Single Record (fetch_single.php) #

Gets a specific record for editing:

<?php
include 'db.php';
header('Content-Type: application/json');

$id = $_GET['id'];
$result = $conn->query("SELECT * FROM product_data WHERE id=$id");
$record = $result->fetch_assoc();

$conn->close();
echo json_encode($record);
?>

3. Database Table Structure #

Create this table in your database to store the data:

CREATE TABLE product_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sno INT NOT NULL,
    items VARCHAR(255) NOT NULL,
    qty_r VARCHAR(50),
    qty_p VARCHAR(50),
    specification TEXT,
    phone VARCHAR(50)
);

Key Features Explained #

• Batch Input Handling: Using array-style names like items[] lets us collect all rows as arrays in the backend, making bulk insertion straightforward.
• AJAX Submission: No page reloads during save/edit operations for a smoother user experience.
• Edit Flow: Clicking "Edit" on a saved record fetches its data, populates the input table, and tracks the record ID to trigger an update instead of an insert.
• Dynamic Refresh: The display table automatically updates after every save to show the latest data.

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