PHP PDO 更新多条记录而不是一条记录

Question

我正在为大学作业制作一个基本的轮询脚本，但不知何故我无法更新一行 SQL。相反，所有行都是更新。

所以这里我们得到了脚本的第一部分，它从选定的投票 ID 中提取所有关联的记录。让我们调用此脚本 show.php

 <?php

     $pid = (int)$_GET['pid'];

     $query = " 
            SELECT 
                 Player_ID,
                 Player_Name
            FROM players WHERE Team_ID = :pid
        "; 

    $query_params = array( 
            ':pid' => $pid, 
    ); 

    try 
    { 
        $stmt = $db->prepare($query); 
        $result = $stmt->execute($query_params); 
    } 
    catch(PDOException $ex) 
    { 
        die("Failed to run query: " . $ex->getMessage()); 
    } 

    $rows = $stmt->fetchAll();

    ?>

    <div class="container contain well">

    <h3>Please select player</h3>

    <form action="vote.php" method="post">
    <input type="hidden" name="pid" value="<?php echo $_GET['pid']; ?>" />
    <?php foreach($rows as $row): ?>
          <input type="radio" name="player" value="<?php echo $row['Player_ID']; ?>" id="<?php echo $row['Player_ID']; ?>"><label for="<?php echo $row['Player_ID']; ?>"><?php echo $row['Player_Name']; ?></label><br>
    <?php endforeach; ?> 
    <input type="submit" value="Vote &raquo;" class="btn btn-primary" />
    </form>

    </div>

现在用户选择他希望为脚本投票的玩家发送 POST 请求让我们说 vote.php

    <?php
         require("database.obj.php");

            $pid = (int)$_POST['pid'];

            $query = " 
                UPDATE players 
                SET 
                    Player_Votes = Player_Votes+1; 
                WHERE
                    ( Player_ID = :player )
            "; 

            $query_params = array( 
                    ':player' => $_POST['player']
            ); 

            try 
            { 
                $stmt = $db->prepare($query); 
                $result = $stmt->execute($query_params); 
            } 
            catch(PDOException $ex) 
            { 
                die("Failed to run query: " . $ex->getMessage()); 
            } 

            header("Location: results.php?pid=$pid");
            die("Redirecting to results.php?pid=$pid");

最后是显示结果的 results.php 页面

     $pid = (int)$_GET['pid'];

     $query = " 
            SELECT 
                 Player_ID,
                 Player_Name,
                 Player_Votes
            FROM players WHERE Team_ID = :pid
        "; 

    $query_params = array( 
            ':pid' => $pid, 
    ); 

    try 
    { 
        $stmt = $db->prepare($query); 
        $result = $stmt->execute($query_params); 
    } 
    catch(PDOException $ex) 
    { 
        die("Failed to run query: " . $ex->getMessage()); 
    } 

    $rows = $stmt->fetchAll();
    ?>

    <div class="container contain well">

    <h3>Player results</h3>

    <?php foreach($rows as $row): ?>
          <p><?php echo $row['Player_Name']; ?>
          <div class="progress">
              <div class="progress-bar" role="progressbar" aria-valuenow="<?php echo $row['Player_Votes']; ?>" aria-valuemin="0" aria-valuemax="100" style="width:<?php echo $row['Player_Votes']; ?>%;">
              <?php echo $row['Player_Votes']; ?> Votes
              </div>
          </div>
    <?php endforeach; ?> 

    </div>

问题出在 show.php 或 vote.php(vote.php 处理 UPDATE 查询)，但我不确定问题出在哪里。正如我所说，数据被插入到 Players_Votes 中，但它是为与 Team_ID = 1 相关联的所有 5 名球员插入的

有人可以帮助我吗？提前致谢。

Answer 1

在 where 子句之前的更新语句中有一个分号 (;)。这会将您的语句分成两个语句 - 一个 update 没有 where 子句，它更新所有记录(如您所述)，以及一个以 where 开头的语句 这应该会失败，因为它不是有效的 SQL。

要修复它，您应该删除该分号:

$query = " 
            UPDATE players 
            SET 
                Player_Votes = Player_Votes+1 -- Semicolon removed here
            WHERE
                ( Player_ID = :player )
        ";