使用Chart.js开发投票图表异常：数据滞后及显示错误求助

问题现象 #

本地开发的MST3K角色投票系统功能正常，部署到线上网站后出现两个Chart.js图表异常：

• Y轴刻度显示范围为0到1.0，步进为小数，不符合票数为整数的实际场景
• 图表数据始终滞后一次投票操作：例如提交Dr. Forrester的1票后，图表不显示该票数，需再次提交新投票才会展示上一次的投票结果

排查与解决过程 #

1. 初步怀疑数据库主键设置问题，移除主键后异常未消除
2. 修复Y轴刻度问题：修改Chart.js配置，在options.scales.y.ticks中添加stepSize:1，强制Y轴刻度以整数步进
3. 修正时区错误：调整php.ini中的date.timezone参数，解决本地与时区不一致问题，但数据滞后现象仍存在
4. 定位核心问题：数据库查询顺序错误——原代码在提交投票后，先执行了票数更新，但图表使用的$results是更新前查询的旧数据，导致图表展示滞后
5. 最终修复：将主页面PHP代码中$results = getMST3KCharacters();的调用移至incrementCharacterCount($selected_character);之后，确保查询的是更新后的最新投票数据

完整代码实现 #

主页面代码 #

<?php
    include 'includes/db_connection.php';
    include 'includes/functions.php';

    //Connect to db
    $conn = connectToDatabase();

    if(isset($_POST['submit-button'])) {
        $selected_character = $_POST['fav_character'];
        incrementCharacterCount($selected_character);
        $results = getMST3KCharacters();
    } else {
        $results = getMST3KCharacters();
    }

?>

<!doctype html>
<html lang="en">
    <head>
        <title>MST3K Character Poll</title>
        <link rel="stylesheet" href="css/style.css" type="text/css">
        <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
        
    </head>

    <body>
        <?php if(!isset($_POST['submit-button'])) : ?>
            <div class="main">
                <h1 class="orbitron-main">Who is your favorite MST3K character?</h1>
                <form method="post" name="mst3k_character">
                    <?php while ($row = mysqli_fetch_assoc($results)) : ?>
                        <input type="radio" id="<?php echo $row['name']; ?>" name="fav_character" value="<?php echo $row['name']; ?>">
                        <label for="<?php echo $row['name']; ?>"><?php echo $row['name']; ?></label><br>
                    <?php endwhile; ?>
                    <br><input type="submit" name="submit-button" value="Submit">
                </form>
            </div>
        <?php else : ?>
            <div class="main">
                <h1 class="message">Thank you for your submission!</h1>
                <h2>The results are:</h2>
                <?php
                    $sorted_results = resultsSortedByNum();

                    while ($row = mysqli_fetch_assoc($sorted_results)) {
                        $voteCount = $row['num'];
                        $voteLabel = ($voteCount == 1) ? "vote" : "votes";
                        echo $row['name'] . ": " . $voteCount . " " . $voteLabel . "<br>";
                    }
                ?>
                <div>
                    <canvas id="myChart"></canvas>
                </div>
            </div>
        <?php endif; ?>

        <?php
            $chartData = extractChartData($results);

            echo "<script>";
            echo "const characterNames = " . json_encode($chartData['characterNames']) . ";";
            echo "const voteCounts = " . json_encode($chartData['voteCounts']) . ";";
            echo "</script>";
        ?>
        <?php
            mysqli_close($conn);
        ?>
        <script src="js/script.js"></script>
    </body>
</html>

JavaScript代码（js/script.js） #

const ctx = document.getElementById('myChart');

new Chart(ctx, {
    type: 'bar',
    data: {
      labels: characterNames,
      datasets: [{
        label: '# of Votes',
        data: voteCounts,
        borderWidth: 1
      }]
    },
    options: {
      scales: {
        y: {
          beginAtZero: true,
          ticks: {
            stepSize: 1
          }
        }
      }
    }
});

工具函数代码（includes/functions.php） #

<?php
    function getMST3KCharacters() {
        $conn = connectToDatabase();

        //WRITE DB QUERY
        $sql = 'SELECT * FROM mst3k_characters;';

        //RUN DB QUERY
        $results = mysqli_query($conn, $sql);

        if (!$results) {
            die("Error in SQL query: " . mysqli_error($conn));
        }

        if (mysqli_num_rows($results) == 0) {
            die("No records found in the database.");
        }

        mysqli_close($conn);

        return $results;
    }

    function incrementCharacterCount($character_name) {
        $conn = connectToDatabase();

        $sql = "UPDATE mst3k_characters SET num = num + 1 WHERE name = ?";
        $stmt = mysqli_prepare($conn, $sql);

        mysqli_stmt_bind_param($stmt, "s", $character_name);
        
        mysqli_stmt_execute($stmt);

        mysqli_stmt_close($stmt);
    }

    function resultsSortedByNum() {
        $conn = connectToDatabase();

        $sql = "SELECT * FROM mst3k_characters ORDER BY num DESC";
        $result = mysqli_query($conn, $sql);

        return $result;
    }

    function extractChartData($results) {
        $characterNames = [];
        $voteCounts = [];

        while ($row = mysqli_fetch_assoc($results)) {
            $characterNames[] = $row['name'];
            $voteCounts[] = $row['num'];
        }

        return [
            'characterNames' => $characterNames,
            'voteCounts' => $voteCounts
        ];
    }
?>

数据库连接函数（includes/db_connection.php） #

function connectToDatabase() {
        $conn = mysqli_connect(HOST, USER, PASS, DB);

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

        return $conn;
    }

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