如何将动态添加的表格行内容存入数据库？

Got it, let's figure out how to save those dynamic table rows to a database. Here's a straightforward, step-by-step plan that works with your existing code:

1. Frontend: Collect & Send Table Data #

First, we'll add a way to gather all rows from your table and send them to a backend endpoint via AJAX. We'll also add a dedicated button to trigger the save-to-database action.

Update Your HTML #

Add a "Save All to Database" button next to your existing "Add" button:

<div class="form-actions btnzone">
    <button type="button" class="btn btn-success savebtn" style="padding: 6px 12px;margin-left: 40%;" id="savebutton" ><i class="icon-check-sign" aria-hidden="false"></i>Add</button>
    <button type="button" class="btn btn-primary" style="padding: 6px 12px;margin-left: 10px;" id="saveToDbBtn">Save All to Database</button>
</div>

Add AJAX Logic to Your Script #

Add this function to collect table data and send it to the backend:

// Save all table rows to database
$("#saveToDbBtn").click(function() {
    // Collect data from every row in the table
    let tableRows = [];
    $("#pTable tbody tr").each(function() {
        const rowData = {
            category: $(this).find(".cat").text().trim(),
            display_name: $(this).find(".display").text().trim(),
            subcategory: $(this).find(".type").text().trim(),
            privilege: $(this).find("td:nth-child(4)").text().trim()
        };
        tableRows.push(rowData);
    });

    // Send data to backend via AJAX
    $.ajax({
        url: "save_entries.php", // Replace with your backend endpoint
        type: "POST",
        contentType: "application/json",
        data: JSON.stringify(tableRows),
        success: function(response) {
            alert("All entries saved successfully!");
            // Optional: Clear the table or refresh after save
        },
        error: function(xhr, status, error) {
            console.error("Save failed:", error);
            alert("Oops, something went wrong saving your data.");
        }
    });
});

2. Backend: Receive & Insert Data #

Next, we'll create a backend script to receive the data, validate it, and insert it into your database. We'll use PHP as an example (common for simple setups), but you can adapt this to Node.js, Python, etc.

Example Backend Script (save_entries.php) #

<?php
// Database connection details (replace with your own)
$host = "localhost";
$db_user = "your_db_username";
$db_pass = "your_db_password";
$db_name = "your_database_name";

// Connect to database
$conn = new mysqli($host, $db_user, $db_pass, $db_name);
if ($conn->connect_error) {
    die("Database connection failed: " . $conn->connect_error);
}

// Receive JSON data from frontend
$raw_data = file_get_contents("php://input");
$entries = json_decode($raw_data, true);

// Validate data (basic check for empty fields)
if (empty($entries)) {
    echo json_encode(["status" => "error", "message" => "No data received"]);
    exit;
}

// Use prepared statements to prevent SQL injection
$stmt = $conn->prepare("INSERT INTO category_entries (category, display_name, subcategory, privilege) VALUES (?, ?, ?, ?)");
$stmt->bind_param("ssss", $cat, $display, $subcat, $privilege);

// Insert each entry into the database
foreach ($entries as $entry) {
    $cat = $entry['category'];
    $display = $entry['display_name'];
    $subcat = $entry['subcategory'];
    $privilege = $entry['privilege'];
    
    $stmt->execute();
}

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

echo json_encode(["status" => "success", "message" => "All entries saved"]);
?>

3. Database Table Setup #

Create a table in your database to store the entries. Run this SQL query in your database management tool (phpMyAdmin, MySQL Workbench, etc.):

CREATE TABLE category_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(255) NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    subcategory VARCHAR(255) NOT NULL,
    privilege VARCHAR(255) NOT NULL
);

Bonus: Handle Edits (Update Existing Rows) #

Your current code supports editing rows, so let's extend the setup to update existing entries instead of re-inserting them.

Update Frontend Row Generation #

Add a hidden column to store the database ID of each row (when editing):

var new_row = "<tr id='row"+i+"' class='info'>" +
    "<td class='cat'>" + cat + "</td>" +
    "<td class='display'>" + display + "</td>" +
    "<td class='type'>" + subcat +"</td>" +
    "<td>"+order +"</td>" +
    "<td class='row-id' style='display:none;'>" + (currentRow ? $(currentRow).find(".row-id").text() : "") + "</td>" + // Hidden ID column
    "<td><span class='editrow'><a class='fa fa-edit' href='javascript: void(0);'>Edit</a></span></td>" +
    "<td><span class='deleterow'><a class='glyphicon glyphicon-trash' href=''>Delete</a></span></td>" +
"</tr>";

Update AJAX Data Collection #

Include the row ID when collecting data:

const rowData = {
    id: $(this).find(".row-id").text().trim(), // Add this line
    category: $(this).find(".cat").text().trim(),
    display_name: $(this).find(".display").text().trim(),
    subcategory: $(this).find(".type").text().trim(),
    privilege: $(this).find("td:nth-child(4)").text().trim()
};

Update Backend to Handle Updates #

Modify the backend script to check for an ID and run an UPDATE instead of INSERT:

foreach ($entries as $entry) {
    $cat = $entry['category'];
    $display = $entry['display_name'];
    $subcat = $entry['subcategory'];
    $privilege = $entry['privilege'];

    if (!empty($entry['id'])) {
        // Update existing entry
        $stmt = $conn->prepare("UPDATE category_entries SET category=?, display_name=?, subcategory=?, privilege=? WHERE id=?");
        $stmt->bind_param("ssssi", $cat, $display, $subcat, $privilege, $entry['id']);
    } else {
        // Insert new entry
        $stmt = $conn->prepare("INSERT INTO category_entries (category, display_name, subcategory, privilege) VALUES (?, ?, ?, ?)");
        $stmt->bind_param("ssss", $cat, $display, $subcat, $privilege);
    }
    $stmt->execute();
    $stmt->close();
}

Key Notes #

• Data Validation: Add more checks (e.g., max length, required fields) in both frontend and backend to ensure clean data.
• Error Handling: Extend the backend to catch insertion/update errors and return meaningful messages to the frontend.
• Security: Always use prepared statements (as shown) to prevent SQL injection. Avoid putting user input directly into SQL queries.

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