如何通过输入序列号从数据库获取产品成本并实现销售系统UI功能

Hey there! Let's work through how to fetch product costs from your database when a serial number is entered, then calculate the total price automatically. First, let's fix the issues in your current code and build the proper flow:

First, let's address the problems in your existing script #

Right now, your code is treating serial numbers as integers and adding them directly—this won't work since serials are strings, and you need to pull actual cost data from your database. Also, you're nesting $(document).ready() inside click handlers, which is incorrect, and you're not handling dynamically added inputs properly.

Step 1: Build a Backend API to Fetch Product Costs #

You'll need a server-side endpoint that takes a serial number, queries your database, and returns the corresponding cost. Here's an example using PHP (adjust for your backend language like Node.js, Python, etc.):

// fetch-cost.php
<?php
// Replace with your database credentials
$dbHost = 'localhost';
$dbUser = 'your_username';
$dbPass = 'your_password';
$dbName = 'your_database';

// Connect to database
$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
if ($conn->connect_error) {
    echo json_encode(['cost' => 0, 'error' => 'DB connection failed']);
    exit;
}

// Get serial number from frontend
$serial = trim($_POST['serial'] ?? '');
$cost = 0;

if (!empty($serial)) {
    // Use prepared statements to prevent SQL injection
    $stmt = $conn->prepare("SELECT cost FROM products WHERE serial = ?");
    $stmt->bind_param("s", $serial);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($row = $result->fetch_assoc()) {
        $cost = $row['cost'];
    }
    $stmt->close();
}

// Return cost as JSON
echo json_encode(['cost' => $cost]);
$conn->close();
?>

Step 2: Rewrite the Frontend JavaScript #

We'll fix event handling, add AJAX calls to the backend, and track each product's cost to calculate the total:

$(document).ready(function() {
    const maxFields = 120;
    const wrapper = $(".input_fields_wrap");
    const addButton = $(".add_field_button");
    const removeButton = $(".remove_field_button");
    // Track costs for each serial input (uses unique ID per input)
    const productCosts = {};

    // Add new serial input
    addButton.click(function(e) {
        e.preventDefault();
        const currentFieldCount = wrapper.children("input[name=serial]").length;
        
        if (currentFieldCount < maxFields) {
            const inputId = `serial-${Date.now()}`;
            wrapper.append(`<input type="text" name="serial" class="form-control" data-id="${inputId}" />`);
            productCosts[inputId] = 0;
            updateTotal();
        }
    });

    // Remove last serial input
    removeButton.click(function(e) {
        e.preventDefault();
        const currentFieldCount = wrapper.children("input[name=serial]").length;
        
        if (currentFieldCount > 1) {
            const lastInput = wrapper.children("input[name=serial]").last();
            const inputId = lastInput.data("id");
            delete productCosts[inputId];
            lastInput.remove();
            updateTotal();
        }
    });

    // Handle serial number input changes (works for dynamic inputs via event delegation)
    wrapper.on("change keyup", "input[name=serial]", function() {
        const serial = $(this).val().trim();
        const inputId = $(this).data("id");

        if (serial) {
            // Send AJAX request to fetch cost
            $.ajax({
                url: "fetch-cost.php", // Match your backend endpoint path
                method: "POST",
                data: { serial: serial },
                dataType: "json",
                success: (response) => {
                    productCosts[inputId] = response.cost || 0;
                    updateTotal();
                },
                error: () => {
                    productCosts[inputId] = 0;
                    updateTotal();
                }
            });
        } else {
            productCosts[inputId] = 0;
            updateTotal();
        }
    });

    // Calculate and update the total price
    function updateTotal() {
        let total = 0;
        Object.values(productCosts).forEach(cost => {
            total += parseFloat(cost);
        });
        // Format total to 2 decimal places for currency
        $("input[name=total]").val(total.toFixed(2));
    }
});

Key Notes to Remember #

• Security: Always use prepared statements in your backend to prevent SQL injection (like the PHP example does). Never concatenate user input directly into SQL queries.
• User Experience: You can add loading spinners while fetching costs to let users know something's happening.
• Validation: Add checks for valid serial number formats (e.g., length, allowed characters) before sending requests to the backend.
• Backend Flexibility: If you're using a different backend language (like Node.js/Express, Python/Flask), the logic stays the same—receive the serial, query the DB, return a JSON cost.

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