PHP实现联动下拉框：无需提交表单动态加载关联数据

Hey there! Let's walk through how to implement this dynamic category dropdown based on your project selection. You've already got the first dropdown for open projects sorted, so we just need to add the AJAX layer to make the magic happen without form submission.

Step 1: Update Your HTML Structure #

First, make sure your two dropdowns are properly set up. Here's how your existing project dropdown can sit alongside the empty category dropdown:

<!-- Your existing open projects dropdown (you already implemented this part) -->
<select id="projectSelect">
    <option value="">Select a project</option>
    <?php
    // Your existing code to load open projects
    $statement = $conexion->prepare("SELECT c_project_id, d_name FROM project WHERE d_status = 'Open'");
    $statement->execute();
    while ($project = $statement->fetch(PDO::FETCH_ASSOC)) {
        echo "<option value='{$project['c_project_id']}'>{$project['d_name']}</option>";
    }
    ?>
</select>

<!-- Category dropdown (starts empty except for a placeholder) -->
<select id="categorySelect">
    <option value="">First select a project</option>
</select>

Step 2: Frontend JavaScript Logic #

We'll use vanilla JavaScript to listen for changes on the project dropdown, send an AJAX request to fetch matching categories, and populate the second dropdown. This avoids needing jQuery, though I'll note a jQuery alternative at the end.

// Grab references to both dropdowns
const projectSelect = document.getElementById('projectSelect');
const categorySelect = document.getElementById('categorySelect');

// Listen for when the user selects a project
projectSelect.addEventListener('change', function() {
    const selectedProjectId = this.value;

    // If no project is selected, reset the category dropdown
    if (!selectedProjectId) {
        categorySelect.innerHTML = '<option value="">First select a project</option>';
        return;
    }

    // Show a loading state so the user knows something's happening
    categorySelect.innerHTML = '<option value="">Loading categories...</option>';

    // Create and send the AJAX request
    const xhr = new XMLHttpRequest();
    // Point to your backend PHP script (we'll write this next)
    xhr.open('GET', 'get_categories.php?id=' + encodeURIComponent(selectedProjectId), true);

    xhr.onload = function() {
        if (this.status === 200) {
            try {
                // Parse the JSON response from the backend
                const categories = JSON.parse(this.responseText);

                // Build the new options for the category dropdown
                let categoryOptions = '<option value="">Select a category</option>';
                categories.forEach(category => {
                    categoryOptions += `<option value="${category.c_category_id}">${category.d_name}</option>`;
                });

                // Update the category dropdown with the new options
                categorySelect.innerHTML = categoryOptions;
            } catch (error) {
                // Handle JSON parsing errors
                categorySelect.innerHTML = '<option value="">Failed to load categories</option>';
                console.error('Error parsing category data:', error);
            }
        } else {
            // Handle HTTP errors (e.g., 404, 500)
            categorySelect.innerHTML = '<option value="">Failed to load categories</option>';
            console.error('Request failed with status:', this.status);
        }
    };

    // Handle network errors
    xhr.onerror = function() {
        categorySelect.innerHTML = '<option value="">Failed to load categories</option>';
        console.error('Network request failed');
    };

    xhr.send();
});

jQuery Alternative (if you're using it) #

If your project already uses jQuery, this code is a bit shorter:

$('#projectSelect').on('change', function() {
    const projectId = $(this).val();
    
    if (!projectId) {
        $('#categorySelect').html('<option value="">First select a project</option>');
        return;
    }
    
    $('#categorySelect').html('<option value="">Loading categories...</option>');
    
    $.get('get_categories.php', { id: projectId })
        .done(function(categories) {
            let options = '<option value="">Select a category</option>';
            $.each(categories, function(index, category) {
                options += `<option value="${category.c_category_id}">${category.d_name}</option>`;
            });
            $('#categorySelect').html(options);
        })
        .fail(function() {
            $('#categorySelect').html('<option value="">Failed to load categories</option>');
        });
});

Step 3: Backend PHP Handler (get_categories.php) #

This script will receive the project ID, run your provided SQL query, and return the categories as JSON. Make sure to reuse your existing database connection setup (don't duplicate connection code—consider including a common DB connection file if you have one).

<?php
// Reuse your existing database connection (adjust credentials as needed)
$conexion = new PDO('mysql:host=your_host;dbname=your_database;charset=utf8', 'your_username', 'your_password');
$conexion->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Check if we received a valid project ID
if (!isset($_GET['id']) || empty($_GET['id'])) {
    echo json_encode([]);
    exit;
}

$projectId = $_GET['id'];

try {
    // Execute your prepared SQL statement (great job using prepared statements to prevent SQL injection!)
    $statement2 = $conexion->prepare("SELECT c_category_id, d_name FROM category WHERE c_project_id = :id");
    // Bind the ID as an integer (adjust to PDO::PARAM_STR if your IDs are strings)
    $statement2->bindParam(':id', $projectId, PDO::PARAM_INT);
    $statement2->execute();

    // Fetch all categories as an associative array
    $categories = $statement2->fetchAll(PDO::FETCH_ASSOC);

    // Return the data as JSON
    header('Content-Type: application/json');
    echo json_encode($categories);
} catch (PDOException $e) {
    // Log the error (don't expose it to users!)
    error_log('Error loading categories: ' . $e->getMessage());
    // Return an empty array to avoid breaking the frontend
    echo json_encode([]);
}
?>

Key Notes & Best Practices #

• SQL Injection Protection: You're already using prepared statements and parameter binding—keep doing this! Never directly concatenate user input into SQL queries.
• Data Type Safety: If c_project_id is an integer, using PDO::PARAM_INT when binding the parameter adds an extra layer of safety. If your IDs are strings, switch to PDO::PARAM_STR.
• User Experience: Adding a loading state keeps users informed, and handling errors gracefully prevents confusion.
• Database Connection: Avoid duplicating connection code—include a shared DB connection file in both your main script and get_categories.php.
• Empty State Handling: If a project has no categories, the backend returns an empty array. You can update the frontend to show <option value="">No categories for this project</option> instead of the default placeholder in that case.

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