我想在我的投票系统中获得我的问题答案的百分比。代码如下所示：
# DATABASE (count)
$c_answers = sql("SELECT COUNT(*)
FROM votes
WHERE id_question = '1'
",
Array(), 1);
# DATABASE (fetch)
$answers = "SELECT *
FROM answers
WHERE id_question = '1'
ORDER BY answer ASC
";
foreach($sql->query($answers) AS $answer) {
# DATABASE (fetch)
$percent = sql("SELECT *
FROM votes
WHERE id_question = '1'
AND id_answer = '".(int)$answer['id']."'
",
Array(), 0);
echo '<div class="answerswer">';
echo '<div class="answerswer-procent">';
echo ($answer['id_answer'] + $percent['id_answer']) / $c_answers;
echo '</div>';
echo '<a href="javascript:void(0)" id="vote" data="'.(int)$answer['id'].'">';
echo $answer['answer'];
echo '</a>';
echo '</div>';
}
sql是一个包含PDO数据库结构所需的所有内容的函数。votes和answers的数据库如下所示：
CREATE TABLE IF NOT EXISTS `votes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_question` int(11) DEFAULT NULL,
`id_answer` int(11) DEFAULT NULL,
`datetime_voted` datetime NOT NULL,
`datetime_changed` datetime NOT NULL,
`ipaddress` text NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE IF NOT EXISTS `answers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_question` int(11) DEFAULT NULL,
`answer` varchar(50) NOT NULL,
`datetime_added` datetime NOT NULL,
`datetime_edited` datetime NOT NULL,
`ipaddress` text NOT NULL,
PRIMARY KEY (`id`)
)
我votes只有一个数据，answers有 4 个答案，它显示的不是100，而是2.我做错了什么？
我不确定 PHP，但您可以从单个查询返回所有这些：
select
a.id,
a.answer,
100 * a.vote_count / t.total_count as percent
from (
select
a.id,
a.answer,
count(*) vote_count
from
answers a
left outer join
votes v
on a.id_question = v.id_question and
a.id = v.id_answer
where
a.id_question = 1
group by
a.id,
a.answer
) a
cross join (
select
count(*) total_count
from
votes
where
id_question = 1
) t
order by
a.id;
示例 SQLFiddle
在我看来，您正在对 id 本身进行算术运算。在第三个查询中，您可能希望使用 count again 。
我会
事先加载特定问题的所有相关记录，以避免将查询置于循环中。这是它的样子..
$votes = array('questionId' => 'rightAnswerId');
$answers = array ( 'questionId' => 'answer');
foreach ($answers as $questionId=>$answer){
if (isset($votes[$questionId]) && $answer == $votes[$questionId]) {
$result[] = $answer;
}
}
$finalResult = count($result) * 100 / count($answers);