PHP与PostgreSQL实现下拉菜单联动对应行饼图的代码整合需求

Got it, let's merge your two code snippets to create a working dropdown that updates the pie chart whenever you select a different location. Here's the full integrated solution, plus breakdowns of the key changes:

Full Integrated Code #

<!DOCTYPE html>
<html lang="en">
<head>
    <title>SDG Pie Chart Selector</title>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-15" />
</head>
<body>
    <?php
    // Reusable database connection function to avoid code duplication
    function getDbConnection() {
        $host = 'localhost';
        $port = '5432';
        $database = 'sustainable';
        $user = 'postgres';
        $password = 'postgres';
        $connectString = "host=$host port=$port dbname=$database user=$user password=$password";
        $link = pg_connect($connectString);
        
        if (!$link) {
            die('Error: Could not connect: ' . pg_last_error());
        }
        return $link;
    }

    // Connect to database
    $link = getDbConnection();

    // Handle dropdown form submission
    $selectedId = isset($_GET['id']) ? (int)$_GET['id'] : null;
    ?>

    <!-- Dropdown Form - submits to current page with selected ID -->
    <form method="get" action="">
        <select name="id" onchange="this.form.submit()">
            <option value="">Select a Location</option>
            <?php
            $query = 'select id, location from sustainable_development';
            $result = pg_query($link, $query);
            
            while ($row = pg_fetch_assoc($result)) {
                $selected = ($selectedId == $row['id']) ? 'selected' : '';
                echo "<option value='{$row['id']}' {$selected}>{$row['location']}</option>";
            }
            pg_free_result($result);
            ?>
        </select>
    </form>

    <br>

    <?php
    if ($selectedId) {
        // Load LibChart (adjust path to match your setup)
        include "C:/xampp/htdocs/tools/libchart/libchart/classes/libchart.php";
        
        // Fetch data for selected location
        $query = "select sdg_4, sdg_5 from sustainable_development where id = $selectedId";
        $result = pg_query($link, $query);
        
        if (pg_num_rows($result) > 0) {
            $row = pg_fetch_assoc($result);
            
            // Create pie chart
            $chart = new PieChart(500, 300);
            $dataSet = new XYDataSet();
            $dataSet->addPoint(new Point("SDG 4 Score", $row['sdg_4']));
            $dataSet->addPoint(new Point("SDG 5 Score", $row['sdg_5']));
            
            $chart->setDataSet($dataSet);
            $chart->setTitle("SDG Scores for Selected Location");
            $chart->render("pie_chart.png");
            
            // Display the chart
            echo "<img alt='SDG Pie Chart' src='pie_chart.png' style='border: 1px solid gray;'/>";
        } else {
            echo "<p>No data found for the selected location.</p>";
        }
        pg_free_result($result);
    } else {
        echo "<p>Please select a location from the dropdown above to view its pie chart.</p>";
    }

    // Close database connection
    pg_close($link);
    ?>
</body>
</html>

Key Improvements & Explanations #

• Reusable Database Connection: I wrapped the connection logic in a function so we don't repeat code for the dropdown and chart queries.
• Form Submission: The dropdown is wrapped in a form that submits to the same page when an option is selected (via onchange="this.form.submit()"). This passes the selected id as a GET parameter.
• Dynamic Chart Generation: When an ID is selected, we fetch only the sdg_4 and sdg_5 values for that specific row instead of grabbing the first row every time.
• Selected State Persistence: The dropdown keeps the selected option highlighted after submission, which improves user experience.
• Error Handling: Added checks for missing data or unselected options to show friendly messages instead of broken charts.
• Cleaner Chart Titles: The chart title now reflects that it's showing data for the selected location, not a fixed title.

Important Notes #

• Make sure the path to libchart.php is correct for your server setup. The current path is for XAMPP on Windows, adjust it if you're using a different environment.
• Ensure your web server has write permissions for the directory where pie_chart.png is saved (it needs to generate the image file).
• Double-check that your sustainable_development table has the id, location, sdg_4, and sdg_5 columns as expected.

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