在PHP中转换代码从MySQL到PDO的问题 - issue with converting code from MySQL to PDO in PHP

我有一段代码(用于)通过表条目来查找对特定问题的投票数。

表如下:

<>之前


  USER   |   answer_id  |  poll_id  |
------------------------------------
usename        5             1
user2          4             1
user3          5             1

之前

等等。基本上每次一个用户投票，一个单独的行和answer_id列保存他们投票的问题号。

在我将代码从sql更新为PDO语句之后，查找投票数的代码停止工作，并且只查找最近的投票，因此只有一次投票返回。下面是我更新的代码:


// Get Votes
$q = 'SELECT * FROM votes WHERE poll_id = :poll_id';
$params = array(':poll_id' => 1);
$stmt = $pdo->prepare($q);
$stmt->execute($params);
while ($row = $stmt->fetch()) {
$votes = array(); //the array that will be containing all votes ( we add them as we retrieve them in a while loop )
$total_votes = 0;
$answer_id = $row['answer_id'];
$votes[$answer_id] = (int)$votes[$answer_id]+1; //add 1 vote for this particulair answer
$total_votes++;
}
// End Get votes

这里是它开始处理这些结果的地方


// Start Get answers
$q = 'SELECT * FROM answers WHERE poll_id = :poll_id';
$params = array(':poll_id' => 1);
$stmt = $pdo->prepare($q);
$stmt->execute($params);
while ($row = $stmt->fetch()) { //loop through all answers this poll has
$id = $row['id']; //the id of the answer -> the amount of votes for each answer we stored in $votes[id_of_answer] so for this id it would be $votes[$id]
$width = round((int)$votes[$id]/$total_votes*99+1); //100% of votes
echo ''.$row['answer'].' 
('.(int)$votes[$id].' vote'.((int)$votes[$id] != 1 ? "s":"").')
   ';
}

下面是MySQL的原始代码:


$get_votes = mysql_query("SELECT * FROM votes WHERE poll_id = '$id' "); //select all votes to this poll
$votes = array(); //the array that will be containing all votes ( we add them as we retrieve them in a while loop )
$total_votes = 0;
while($vote = mysql_fetch_assoc($get_votes)) { //retrieve them
$answer_id = $vote['answer_id'];
$votes[$answer_id] = (int)$votes[$answer_id]+1; //add 1 vote for this particular answer
$total_votes++;
}
//now loop through the answers and get their corresponding amount of votes from $votes[id_of_answer]
$get_answers = mysql_query("SELECT * FROM answers WHERE poll_id = '$id' ");
while($answer = mysql_fetch_assoc($get_answers)) { //loop through all answers this poll has
$id = $answer['id']; //the id of the answer -> the amount of votes for each answer we stored in $votes[id_of_answer] so for this id it would be $votes[$id]
$width = round((int)$votes[$id]/$total_votes*99+1); // 100% of votes
echo ''.$answer['answer'].' 
('.(int)$votes[$id].' vote'.((int)$votes[$id] != 1 ? "s":"").')
   ';
}

Can anyone tell me where I'm going wrong? its the //Get Votes part im struggling on working out what ive done. As far as i can see, theres nothing unjust.

You are overwriting your array and count in the loop:

while ($row = $stmt->fetch()) {
  $votes = array(); //the array that will be containing all votes ( we add them as we retrieve them in a while loop )
  $total_votes = 0;
  $answer_id = $row['answer_id'];
  $votes[$answer_id] = (int)$votes[$answer_id]+1; //add 1 vote for this particulair answer
  $total_votes++;
}

应:

$votes = array(); //the array that will be containing all votes ( we add them as we retrieve them in a while loop )
$total_votes = 0;
while ($row = $stmt->fetch()) {
  $answer_id = $row['answer_id'];
  $votes[$answer_id] = (int)$votes[$answer_id]+1; //add 1 vote for this particulair answer
  $total_votes++;
}

检查bindparam和fetchall而不是fetch

/* Execute a prepared statement by binding PHP variables */
$poll_id = 1;
$sth = $dbh->prepare('SELECT * FROM votes WHERE poll_id = :poll_id');
$sth->bindParam(':poll_id', $poll_id, PDO::PARAM_INT);
$sth->execute();
/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:'n");
$result = $sth->fetchAll();
print_r($result);