基于jQuery Ajax与PHP实现动态增删联动Select Box及MySQL入库

阿华AIGC实验室

2026-5-21

Got it, let's build this dynamic form system with linked select boxes, add/remove functionality, and AJAX-powered MySQL submission. I'll break this down into manageable steps so you can follow along easily.

1. Database Setup

First, we need three tables to support this system: one for categories, one for products linked to categories, and one to store the submitted form data. Here's the SQL to create them:

-- Categories table (stores product categories)
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE
);

-- Products table (linked to categories via foreign key)
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

-- Submission storage table (saves user's selections)
CREATE TABLE form_submissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL, -- Example additional field
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

Populate the categories and products tables with test data to test the functionality.

2. Frontend HTML Structure

We'll create a container for repeatable form groups, each containing the linked selects, an additional field (like quantity), and a delete button. We'll also add an "Add More" button to create new groups:

<!DOCTYPE html>
<html>
<head>
    <title>Dynamic Product Selection Form</title>
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <style>
        .form-group {
            margin: 15px 0;
            padding: 12px;
            border: 1px solid #e0e0e0;
            border-radius: 6px;
            display: flex;
            gap: 10px;
            align-items: center;
        }
        .delete-btn {
            background: #dc3545;
            color: white;
            border: none;
            padding: 6px 12px;
            border-radius: 4px;
            cursor: pointer;
        }
        .action-btn {
            background: #007bff;
            color: white;
            border: none;
            padding: 8px 16px;
            border-radius: 4px;
            cursor: pointer;
            margin-right: 10px;
        }
        #message {
            margin-top: 15px;
            padding: 10px;
            border-radius: 4px;
        }
    </style>
</head>
<body>
    <h2>Select Products</h2>
    <div id="form-container">
        <!-- Initial form group -->
        <div class="form-group">
            <label>Category:</label>
            <select class="category-select" required>
                <option value="">Select Category</option>
            </select>

            <label>Product:</label>
            <select class="product-select" required>
                <option value="">Select Product</option>
            </select>

            <label>Quantity:</label>
            <input type="number" class="quantity-input" min="1" required>

            <button class="delete-btn">Delete</button>
        </div>
    </div>
    <button id="add-more" class="action-btn">Add More</button>
    <button id="submit-form" class="action-btn">Submit</button>

    <div id="message"></div>
</body>
</html>

3. jQuery & AJAX Logic

Now let's add the JavaScript to handle select box linking, dynamic add/remove, form validation, and submission. We'll use event delegation to ensure dynamically added elements work correctly:

$(document).ready(function() {
    // Populate initial category select on page load
    $.ajax({
        url: 'get_categories.php',
        type: 'GET',
        dataType: 'json',
        success: function(response) {
            if (response.success) {
                $.each(response.categories, function(index, category) {
                    $('.category-select').append(`<option value="${category.id}">${category.name}</option>`);
                });
            }
        }
    });

    // Link category select to product select (works for dynamic groups too)
    $(document).on('change', '.category-select', function() {
        const categoryId = $(this).val();
        const productSelect = $(this).siblings('.product-select');
        
        productSelect.html('<option value="">Loading...</option>');
        
        if (categoryId) {
            $.ajax({
                url: 'get_products.php',
                type: 'GET',
                data: {category_id: categoryId},
                dataType: 'json',
                success: function(response) {
                    productSelect.html('<option value="">Select Product</option>');
                    if (response.success) {
                        $.each(response.products, function(index, product) {
                            productSelect.append(`<option value="${product.id}">${product.name}</option>`);
                        });
                    } else {
                        productSelect.html('<option value="">No products found</option>');
                    }
                },
                error: function() {
                    productSelect.html('<option value="">Error loading products</option>');
                }
            });
        } else {
            productSelect.html('<option value="">Select Category First</option>');
        }
    });

    // Add new form group
    $('#add-more').click(function() {
        const newGroup = $('.form-group:first').clone();
        // Reset all values in the new group
        newGroup.find('.category-select').val('');
        newGroup.find('.product-select').html('<option value="">Select Product</option>');
        newGroup.find('.quantity-input').val('');
        // Append to container
        $('#form-container').append(newGroup);
    });

    // Delete form group (ensure at least one group remains)
    $(document).on('click', '.delete-btn', function() {
        if ($('.form-group').length > 1) {
            $(this).closest('.form-group').remove();
        } else {
            $('#message').html('<p style="color: white; background: #dc3545;">You need at least one selection group!</p>');
            setTimeout(() => $('#message').empty(), 3000);
        }
    });

    // Submit form via AJAX with validation
    $('#submit-form').click(function() {
        const formData = [];
        let isValid = true;

        // Validate each form group
        $('.form-group').each(function() {
            const categoryId = $(this).find('.category-select').val();
            const productId = $(this).find('.product-select').val();
            const quantity = $(this).find('.quantity-input').val();

            if (!categoryId || !productId || !quantity || quantity < 1) {
                isValid = false;
                $(this).css('border-color', '#dc3545');
            } else {
                $(this).css('border-color', '#e0e0e0');
                formData.push({
                    category_id: categoryId,
                    product_id: productId,
                    quantity: quantity
                });
            }
        });

        if (!isValid) {
            $('#message').html('<p style="color: white; background: #dc3545;">Please fill all required fields correctly!</p>');
            setTimeout(() => $('#message').empty(), 3000);
            return;
        }

        // Send data to PHP
        $.ajax({
            url: 'submit_data.php',
            type: 'POST',
            data: JSON.stringify(formData),
            contentType: 'application/json',
            dataType: 'json',
            success: function(response) {
                if (response.success) {
                    $('#message').html('<p style="color: white; background: #28a745;">Data submitted successfully!</p>');
                    // Reset form to initial state
                    $('#form-container').html($('.form-group:first').clone());
                    $('.form-group:first').find('.category-select').val('');
                    $('.form-group:first').find('.product-select').html('<option value="">Select Product</option>');
                    $('.form-group:first').find('.quantity-input').val('');
                } else {
                    $('#message').html(`<p style="color: white; background: #dc3545;">Error: ${response.message}</p>`);
                }
            },
            error: function() {
                $('#message').html('<p style="color: white; background: #dc3545;">Failed to submit data. Please try again.</p>');
            }
        });
    });
});

4. Backend PHP Handlers

We need three PHP files to handle category/product fetching and form submission. All use PDO for secure database interactions.

get_categories.php

Fetches all categories from the database:

<?php
// Database configuration (update with your credentials)
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_db_user';
$password = 'your_db_password';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->query("SELECT id, name FROM categories ORDER BY name");
    $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode([
        'success' => true,
        'categories' => $categories
    ]);
} catch(PDOException $e) {
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}
?>

get_products.php

Fetches products based on the selected category ID:

<?php
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_db_user';
$password = 'your_db_password';

// Validate incoming category ID
if (!isset($_GET['category_id']) || !is_numeric($_GET['category_id'])) {
    echo json_encode([
        'success' => false,
        'message' => 'Invalid category ID'
    ]);
    exit;
}

$categoryId = $_GET['category_id'];

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->prepare("SELECT id, name FROM products WHERE category_id = ? ORDER BY name");
    $stmt->execute([$categoryId]);
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode([
        'success' => true,
        'products' => $products
    ]);
} catch(PDOException $e) {
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}
?>

submit_data.php

Validates and inserts submitted data into the database:

<?php
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_db_user';
$password = 'your_db_password';

// Get JSON data from request
$data = json_decode(file_get_contents('php://input'), true);

if (!$data || !is_array($data)) {
    echo json_encode([
        'success' => false,
        'message' => 'Invalid data format'
    ]);
    exit;
}

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Use transaction to ensure all inserts succeed or fail together
    $pdo->beginTransaction();

    $stmt = $pdo->prepare("INSERT INTO form_submissions (category_id, product_id, quantity) VALUES (?, ?, ?)");

    foreach ($data as $item) {
        // Validate each item's data
        if (!isset($item['category_id'], $item['product_id'], $item['quantity']) || 
            !is_numeric($item['category_id']) || !is_numeric($item['product_id']) || 
            !is_numeric($item['quantity']) || $item['quantity'] < 1) {
            throw new Exception('Invalid selection data');
        }

        $stmt->execute([
            $item['category_id'],
            $item['product_id'],
            $item['quantity']
        ]);
    }

    $pdo->commit();

    echo json_encode([
        'success' => true,
        'message' => 'All selections saved successfully'
    ]);
} catch(PDOException $e) {
    $pdo->rollBack();
    echo json_encode([
        'success' => false,
        'message' => 'Database error: ' . $e->getMessage()
    ]);
} catch(Exception $e) {
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}
?>

Key Tips for Production

Update credentials: Replace the database configuration variables in all PHP files with your actual database details.
Enhance security: Add CSRF protection to the form submission to prevent cross-site request forgery.
Logging: Implement error logging for PHP to track issues in production.
Styling: Customize the CSS to match your project's design system.

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