PHP投票系统无法从Database展示结果问题求助

问题背景 #

• 初始问题：提交投票后页面完全空白，添加数据库连接常量定义后未解决。
• 当前状态：补全遗漏的includes文件后，结果页面可显示，但无法按投票占比从高到低展示各选项的投票结果。

核心问题分析 #

1. 缺失数据库连接函数：代码中addToDataBase和getResults函数均调用了connectToDB()，但未定义该函数，导致数据库连接失败，无法获取投票数据。
2. 未处理空投票数据：当暂无投票时，总投票数为0，会触发除以0的运算错误。
3. 变量未定义风险：addToDataBase函数中若查询不到对应选项，$key变量未定义，会导致UPDATE语句执行失败。

修复步骤与代码 #

1. 补充数据库连接函数 #

在全局常量定义后添加connectToDB()函数，确保函数内可正常建立数据库连接：

define("HOST", "****");
define("USER", "****");
define("PASS", "****");
define("BASE", "****");

// 新增数据库连接函数
function connectToDB(){
    $conn = mysqli_connect(HOST, USER, PASS, BASE);
    if (!$conn) {
        die("数据库连接失败: " . mysqli_connect_error());
    }
    return $conn;
}

$conn = mysqli_connect(HOST, USER, PASS, BASE);

2. 优化结果查询与排序逻辑 #

直接在SQL查询中按投票数降序排序（比PHP层面排序更高效），同时处理空投票场景：

function getResults($DBName){
    $conn = connectToDB();

    // 按投票数从高到低查询数据
    $sql = "SELECT * FROM $DBName ORDER BY votes DESC;";
    $results = mysqli_query($conn, $sql);

    // 获取总投票数
    $sql = "SELECT SUM(votes) as total FROM $DBName;";
    $totalResult = mysqli_query($conn,$sql);
    $total = mysqli_fetch_assoc($totalResult)['total'];

    $content = '<h1 class="result-text">Results</h1>';
    
    // 处理无投票数据的情况
    if($total == 0){
        $content .= "<h2>暂无投票数据</h2>";
        mysqli_close($conn);
        return $content;
    }

    // 生成结果内容
    while($row = mysqli_fetch_array($results, MYSQLI_ASSOC)){
        $name = $row['name'];
        $percentage = round($row['votes']/$total * 100);
        $content .= "<h2>". ucwords($name) ." has $percentage% of the votes</h2>";
    }

    mysqli_close($conn);
    return $content;
}

3. 修复投票入库的变量未定义问题 #

添加查询结果为空的处理逻辑，避免$key未定义导致的SQL错误：

function addToDataBase($data, $DBName){
    $data = strtolower($data);
    $conn = connectToDB();
    $sql = "SELECT * FROM $DBName WHERE name='$data';";
    $results = mysqli_query($conn, $sql);

    if(mysqli_num_rows($results) != 0){
        $key = mysqli_fetch_array($results, MYSQLI_ASSOC)['id'];
        $sql = "UPDATE $DBName SET votes = votes + 1 WHERE id=$key;";
        mysqli_query($conn, $sql);
    } else {
        // 若选项不存在则新增
        $sql = "INSERT INTO $DBName (name, votes) VALUES ('$data', 1);";
        mysqli_query($conn, $sql);
    }
    mysqli_close($conn);
}

完整修复后代码 #

<?php
    include 'includes/dp.php';

    function createPage(){
        if(!isset($_POST['food'])){
            echo createQuestionare();
        } else {
            addToDataBase($_POST['food'], 'poll');
            displayResults();
        }
    }

    define("HOST", "****");
    define("USER", "****");
    define("PASS", "****");
    define("BASE", "****");

    function connectToDB(){
        $conn = mysqli_connect(HOST, USER, PASS, BASE);
        if (!$conn) {
            die("数据库连接失败: " . mysqli_connect_error());
        }
        return $conn;
    }

    $conn = mysqli_connect(HOST, USER, PASS, BASE);

    function createQuestionare(){
        $content = "";
        $content .= "<div class='main'>";
        $content .= "<h1 class='title'>Food Poll</h1>";
        $content .= "<form action='.' method='post'>";
        $content .= createQuestion();
        $content .= "<input type='submit'>";
        $content .= "</form>";
        $content .= "</div>";
        return $content;
    }

    function createQuestion(){
        $arr = ["Pizza", "Burger", "Salad", "Pasta"];
        $content = "";
        $content .= "<h1 class='question-text'>Which food is most satisfying?</h1>";
        foreach($arr as $subject){
            $content .= "<input type='radio' id='$subject' value='$subject' name='food'>";
            $content .= "<label for='$subject'>$subject</label><br>";
        }
        return $content;
    }

    function addToDataBase($data, $DBName){
        $data = strtolower($data);
        $conn = connectToDB();
        $sql = "SELECT * FROM $DBName WHERE name='$data';";
        $results = mysqli_query($conn, $sql);

        if(mysqli_num_rows($results) != 0){
            $key = mysqli_fetch_array($results, MYSQLI_ASSOC)['id'];
            $sql = "UPDATE $DBName SET votes = votes + 1 WHERE id=$key;";
            mysqli_query($conn, $sql);
        } else {
            $sql = "INSERT INTO $DBName (name, votes) VALUES ('$data', 1);";
            mysqli_query($conn, $sql);
        }
        mysqli_close($conn);
    }

    function displayResults(){
        $arr = ['poll'];
        $content = '<div class="main">';
        $content .= "<h1 class='title'>Thank You!</h1>";
        foreach($arr as $DBName){
            $content .= '<div class="result-container">';
            $content .= getResults($DBName);
            $content .= '</div>';
        }
        $content .= '</div>';
        echo $content;
    }

    function getResults($DBName){
        $conn = connectToDB();
        $sql = "SELECT * FROM $DBName ORDER BY votes DESC;";
        $results = mysqli_query($conn, $sql);

        $sql = "SELECT SUM(votes) as total FROM $DBName;";
        $totalResult = mysqli_query($conn,$sql);
        $total = mysqli_fetch_assoc($totalResult)['total'];

        $content = '<h1 class="result-text">Results</h1>';
        
        if($total == 0){
            $content .= "<h2>暂无投票数据</h2>";
            mysqli_close($conn);
            return $content;
        }

        while($row = mysqli_fetch_array($results, MYSQLI_ASSOC)){
            $name = $row['name'];
            $percentage = round($row['votes']/$total * 100);
            $content .= "<h2>". ucwords($name) ." has $percentage% of the votes</h2>";
        }

        mysqli_close($conn);
        return $content;
    }
?>
<!DOCTYPE html>
<html lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="utf-8" />
    <title>Poll</title>
    <link rel="stylesheet" href="css/style.css">
</head>
<body>
<?php 
    createPage();
?>
</body>
</html>

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