技术求助：从选定年份检索数据及修改AJAX下拉选年份加载JSON代码

Hey there! Let's get your year-based data retrieval and AJAX chart working smoothly. Here's a step-by-step breakdown:

1. Add a Year Selection Dropdown to Your HTML #

First, you'll need a dropdown menu so users can pick a year. Add this to your page:

<select id="yearSelector">
  <option value="">Select a Year</option>
  <option value="2022">2022</option>
  <option value="2023">2023</option>
  <option value="2024">2024</option>
  <!-- Add more years as needed for your dataset -->
</select>
<div id="chartBar"></div> <!-- This is where your chart will render -->

2. Modify Your AJAX & Chart Code #

I've updated your JavaScript to handle the dropdown selection, pass the year to your backend, and refresh the chart with the correct data. Notice the key fixes and additions:

$(document).ready(function () {
  // Initialize the chart (show all years by default, or empty if preferred)
  getGraph();

  // Listen for when the user selects a new year
  $("#yearSelector").on("change", function() {
    const selectedYear = $(this).val();
    getGraph(selectedYear); // Update the chart with the chosen year
  });

  function getGraph(year = null) {
    // Build the request URL, adding the year as a query parameter if selected
    let requestUrl = "bar_graph_query.php";
    if (year) {
      // Use encodeURIComponent to safely pass the year in the URL
      requestUrl += `?year=${encodeURIComponent(year)}`;
    }

    $.getJSON(requestUrl, function (chartData) {
      const chart = new CanvasJS.Chart("chartBar", {
        animationEnabled: true,
        theme: "light2",
        title: {
          // Update the title to reflect the selected year
          text: `Summary for ${year ? year : "All Years"}`
        },
        axisY: {
          title: "Your Metric Label" // Customize this to match your data (e.g., "Sales", "Users")
        },
        data: [{
          type: "bar",
          dataPoints: chartData // Assuming your backend returns a CanvasJS-compatible dataPoints array
        }]
      });

      chart.render(); // Don't forget this! It's what draws the chart on the page
    });
  }
});

3. Update Your Backend (bar_graph_query.php) #

Your PHP script needs to accept the year parameter and return the corresponding JSON data. Make sure to use prepared statements to avoid SQL injection:

<?php
// Connect to your database (replace with your DB credentials)
$dbConnection = new mysqli("localhost", "username", "password", "database_name");

// Check for connection errors
if ($dbConnection->connect_error) {
  die("Connection failed: " . $dbConnection->connect_error);
}

// Get the selected year from the URL (default to null if not provided)
$selectedYear = $_GET['year'] ?? null;

// Prepare the database query
if ($selectedYear) {
  // Query for a specific year (use prepared statement to prevent SQL injection)
  $query = $dbConnection->prepare("SELECT category, value FROM your_table WHERE year = ?");
  $query->bind_param("s", $selectedYear); // "s" denotes string type; adjust if your year is an integer ("i")
} else {
  // Query for all years if no year is selected
  $query = $dbConnection->prepare("SELECT category, value FROM your_table");
}

$query->execute();
$result = $query->get_result();

// Format data into CanvasJS-friendly dataPoints
$dataPoints = [];
while ($row = $result->fetch_assoc()) {
  $dataPoints[] = [
    "label" => $row['category'], // Replace with your category column name
    "y" => (int)$row['value']    // Replace with your value column name (cast to number if needed)
  ];
}

// Return JSON data to the frontend
header('Content-Type: application/json');
echo json_encode($dataPoints);

// Clean up database connections
$query->close();
$dbConnection->close();
?>

Key Notes to Remember: #

• SQL Injection Prevention: Always use prepared statements when passing user input (like the selected year) to your database queries.
• Chart Rendering: The chart.render() call is critical—your original code was missing this, so the chart wouldn't display even if data loaded.
• Data Format: Ensure your PHP script returns an array of objects with label and y properties (CanvasJS's required format for bar charts).

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