PHP+AJAX下拉框联动异常：班级选项重复、无法加载对应所有Section

Let's break down and fix the two main issues you're facing: duplicate class entries in the dropdown, and not being able to load all sections for a selected class at once.

1. Fix Duplicate Class Dropdown Options #

The root cause here is that your original query pulls every row from teacher_subject—so if a teacher has multiple sections for the same class, you get duplicate class options. We'll modify the query to fetch unique class IDs first, then map them to their corresponding class names.

Updated HTML/PHP for the class dropdown:

<select class="form-control form-control-sm mr-2" name="class" id="class" onChange="getsection(this.value);">
    <option value="">Select</option>
    <?php 
    // Fetch unique class IDs assigned to the teacher
    $sql_cls = "SELECT DISTINCT t_class_id FROM $tb_teacher_subject WHERE school_id='$school_id' AND t_id='$sessid' AND status='active'"; 
    $res_cls = mysqli_query($conn, $sql_cls); 
    if($res_cls && mysqli_num_rows($res_cls) > 0){ 
        while($rows_cls = mysqli_fetch_assoc($res_cls)){ 
            $class_id = $rows_cls['t_class_id'];
            // Retrieve the class name from the class table
            $sql_clse = "SELECT t_class FROM $tb_class WHERE school_id='$school_id' AND id='$class_id'"; 
            $res_clse = mysqli_query($conn, $sql_clse); 
            $class_name = mysqli_fetch_assoc($res_clse)['t_class'];
    ?>
    <option value="<?php echo $class_id; ?>">
        <?php echo $class_name; ?>
    </option>
    <?php } } ?>
</select>
<select name="section" id="section-list" class="form-control form-control-sm mr-2">
    <option value="">Select</option>
</select>

2. Modify AJAX & PHP to Load All Sections for a Selected Class #

Previously, you were passing a single teacher_subject row ID to the backend, which only returned one section. Now we'll pass just the class ID, and fetch all sections linked to that teacher and class combination.

Updated JavaScript #

function getsection(class_id){
    $.ajax({
        url: "model/results/result_edit.php?getsection",
        data: { class_id: class_id },
        type: "POST",
        success: function(data){
            console.log("Loaded sections successfully");
            $("#section-list").html(data);
        },
        error: function(){
            console.log("Error loading sections");
        }
    });
}

Updated PHP (in result_edit.php) #

<?php 
if(isset($_GET['getsection'])){
    $cls = $_POST['class_id'];
    // Fetch all sections assigned to the teacher for the selected class
    $sql_section = "SELECT t_section FROM $tb_teacher_subject WHERE school_id='$school_id' AND t_id='$sessid' AND t_class_id='$cls' AND status='active'";
    $res_section = mysqli_query($conn, $sql_section);
    
    $output = '<option value="">Select</option>';
    if($res_section && mysqli_num_rows($res_section) > 0){
        while($row_sec = mysqli_fetch_assoc($res_section)){
            $sec_id = $row_sec['t_section'];
            // Retrieve the section name from the section table
            $sql_sec_name = "SELECT t_section FROM $tb_section WHERE school_id='$school_id' AND id='$sec_id'";
            $res_sec_name = mysqli_query($conn, $sql_sec_name);
            $sec_name = mysqli_fetch_assoc($res_sec_name)['t_section'];
            
            $output .= "<option value='$sec_id'>$sec_name</option>";
        }
    }
    echo $output;
}
?>

Key Improvements #

• Eliminated Duplicates: Using DISTINCT t_class_id ensures each class appears only once in the dropdown.
• Load All Sections: The backend now fetches every section the teacher is responsible for in the selected class, instead of just one.
• Simplified Data Flow: We removed the unnecessary teacher_subject ID from the AJAX request, since we can uniquely identify the teacher via $sessid and school ID.

Quick Security Note #

For production environments, consider using mysqli prepared statements to prevent SQL injection. Here's a quick example for fetching classes:

$stmt = $conn->prepare("SELECT DISTINCT t_class_id FROM $tb_teacher_subject WHERE school_id=? AND t_id=? AND status='active'");
$stmt->bind_param("ss", $school_id, $sessid);
$stmt->execute();
$result = $stmt->get_result();

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