如何通过Ajax结合数据库实现ComboBox动态取值及年份联动加载学期

Alright, let's build out this dynamic dependent dropdown system where we populate a year dropdown from the database first, then load corresponding semesters when a year is selected. I'll expand on your existing code and fill in the missing pieces to make it work end-to-end.

Step 1: Frontend HTML Structure #

First, we need the two dropdowns in our HTML. The year dropdown will be populated on page load, and the semester dropdown will update based on the selected year.

<!-- Year Dropdown -->
<label for="year">Select Year:</label>
<select id="year" onchange="getSubject()">
  <!-- Options will be populated via AJAX on page load -->
  <option value="">-- Select Year --</option>
</select>

<!-- Semester Dropdown -->
<label for="subject">Select Semester:</label>
<select id="subject">
  <option value="">-- Select Year First --</option>
</select>

Step 2: Updated JavaScript Code #

I noticed a small tweak needed in your existing function: you're grabbing the value from an element with ID semester, but we need the selected year's value (ID year). Here's the corrected JS, plus code to populate the year dropdown when the page loads:

<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script type="text/javascript">
// Populate year dropdown on page load
$(document).ready(function() {
  $.ajax({
    type: "GET",
    url: "get_years.php", // Separate script to fetch years from DB
    success: function(result) {
      $("#year").html(result);
    }
  });
});

// Load semesters based on selected year
function getSubject() {
  var yr = document.getElementById("year").value;
  console.log("Selected Year: " + yr);
  
  // Clear semester dropdown if no year is selected
  if (yr === "") {
    $("#subject").html('<option value="">-- Select Year First --</option>');
    return;
  }

  $.ajax({
    type: "GET",
    url: "dropdown.php",
    data: { yr: yr }, // Cleaner object notation for request data
    success: function(result) {
      $("#subject").html(result);
    },
    error: function() {
      $("#subject").html('<option value="">Error loading semesters</option>');
    }
  });
}
</script>

Step 3: Backend PHP Scripts #

We need two PHP scripts to handle database queries: one to fetch all available years, and another to fetch semesters linked to the selected year.

get_years.php (Fetch all years from database)

<?php
// Update with your database credentials
$conn = mysqli_connect("localhost", "your_username", "your_password", "your_database");

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

// Query to get distinct years (adjust table/column names to match your schema)
$sql = "SELECT DISTINCT year FROM academic_terms ORDER BY year DESC";
$result = mysqli_query($conn, $sql);

// Generate dropdown options
echo '<option value="">-- Select Year --</option>';
while ($row = mysqli_fetch_assoc($result)) {
  echo '<option value="' . $row['year'] . '">' . $row['year'] . '</option>';
}

mysqli_close($conn);
?>

dropdown.php (Fetch semesters for selected year)

<?php
// Update with your database credentials
$conn = mysqli_connect("localhost", "your_username", "your_password", "your_database");

if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

// Get selected year from AJAX request
$year = $_GET['yr'];

// Query to get semesters linked to the selected year (adjust table/column names)
$sql = "SELECT semester_id, semester_name FROM academic_terms WHERE year = '$year' ORDER BY semester_id";
$result = mysqli_query($conn, $sql);

// Generate semester options
echo '<option value="">-- Select Semester --</option>';
while ($row = mysqli_fetch_assoc($result)) {
  echo '<option value="' . $row['semester_id'] . '">' . $row['semester_name'] . '</option>';
}

mysqli_close($conn);
?>

Key Notes to Make This Work: #

• Database Schema: Ensure your database table has columns for year and semester_name (adjust the PHP query columns to match your actual table structure).
• Error Handling: Added basic error handling in the AJAX call to show a user-friendly message if the semester load fails.
• User Experience: The year dropdown populates automatically on page load, so users don't have to trigger any action to see available years.
• Cleaner Data Passing: Using object notation ({ yr: yr }) instead of string concatenation makes the AJAX request data more readable and less prone to syntax errors.

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