php - 数据库项目未显示在网页上

Question

我正在使用 PHP 脚本并通过 PDO 连接到数据库。该数据库包含两个表:第一个是 required_items，另一个是 donations。

required_items 包含列:id、name、required_amount

donations 包含列:id、name、email、donation_amount、 item_id

这是我正在使用的简单代码:

<?php

    $pageName = "/donations/index.php";
    $databaseHost = "localhost";
    $databaseName = "donations";
    $databaseUser = "root";
    $databasePassword = "pwd";

    //TODO Check, validate, sanitize your input...
    $name = $_POST['name'];
    $email = $_POST['email'];
    $donation_amount = $_POST['amount'];
    $item_id = $_POST['radioButtons'];

    try {
        $db = new PDO('mysql:host=' . $databaseHost . ';dbname=' . $databaseName . ';charset=utf8', $databaseUser, $databasePassword);
        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    } catch (PDOException $e) {
        echo "Exception: " . $e->getMessage(); //TODO better error handling
    }

    // Check to see if someone wants to donate something
    if(!empty($_REQUEST['donate']))
    {
        try {
            //Construct your query with placeholders
            $sql = "INSERT INTO donations (name, email, donation_amount, item_id) VALUES (?, ?, ?, ?)";
            //Prepare your query
            $query = $db->prepare($sql);

            //Execute it passing parameters
            $query->execute(array($name, $email, $donation_amount, $item_id));

            echo("Thank you for donating!\n<br>\n<br>");
    } catch (PDOException $e) {
        echo "Exception: " . $e->getMessage(); //TODO better error handling
    }
    }

    $request = "SELECT 
                required_items.id,
                required_items.name, 
                required_items.required_amount - donations.donation_amount AS Amount_Left,
                donations.item_id
                FROM required_items JOIN donations ON donations.item_id=required_items.id";

    $stmt = $db->query($request);
    $item_info = $stmt->fetch();

    // Round negative amounts to zero
    if($item_info['Amount_Left'] < 0){
        $item_info['Amount_Left'] = 0;
        }

?>
    <!--Print out the table tag and header-->
    <form name="donationForm" action="<?php $pageName ?>" method="POST">
    <fieldset>
    <table>
    <tr>
        <th>Item Name</th><th>Amount</th>
    </tr>
    <tr>
        <td><?php $item_info['name'] ?></td>
        <td><?php $item_info['Amount_Left'] ?></td>
        <td><input type="radio" name="radioButtons" value="<?php $item_info['item_id'] ?>"></input></td>
    </tr>
    </table>
    <div><label>Amount</label><input type="number" name="amount"></div>
    <div><label>Email</label><input type="email" name="email"></div>
    <div><label>Name</label><input type="text" name="name"></div>
    <div><input type="submit" name="donate" value="Donate"></div>
    </fieldset>
    </form>

有人可以帮我解决这个问题吗？我已经包含了整个脚本，因为我不确定到底出了什么问题。我已经尝试回显表中的信息，但它也不起作用。本质上，除了单选按钮之外，表格字段中什么也没有显示。

Answer 1

您正在为表添加别名，请尝试:

SELECT r.name AS Name ... FROM required_items AS r ...

PDO::query() 返回 PDOStatement 对象，因此您需要执行以下操作:

$amount_left = $db->query("SELECT required_items.name AS Name, required_items.required_amount - donations.donation_amount AS Amount_Left 
                        FROM required_items AS r JOIN donations AS d ON d.item_id=r.id")
                        ->fetch(PDO::FETCH_ASSOC);