我有一个调查应用程序,正在处理结果页面。对于每个问题,页面应显示提供的不同答案以及每个答案提供的次数。
有些问题可以接受多个答案。例如,假设以下是这样一个问题:
- 你感觉怎么样?
这意味着任何组合Good
, Bad
and/or Okay
被允许作为答案。
假设调查的回复如下:
- 调查一:
Good
, Bad
and Okay
- 调查2:
Good
, Bad
and Okay
- 调查3:
Good
and Bad
- 调查四:
Good
and Bad
- 调查5:
Good
- 调查 6:
Good
- 调查7:
Good
以下是预期结果:
- 你感觉怎么样?
- Good = 7
- Bad = 4
- Okay = 2
我的代码提供了
- 你感觉怎么样?
- Good = 3
- Bad = 2
- Good = 4
- Okay = 1
- Bad = 2
- Okay = 1
此示例的数据库条目如下所示:
questionNum |
question |
answer1 |
answer2 |
answer3 |
... |
1 |
How are you feeling? |
Good |
Bad |
Okay |
|
1 |
How are you feeling? |
Good |
Bad |
Okay |
|
1 |
How are you feeling? |
Good |
Bad |
|
|
1 |
How are you feeling? |
Good |
Bad |
|
|
1 |
How are you feeling? |
Good |
|
|
|
1 |
How are you feeling? |
Good |
|
|
|
1 |
How are you feeling? |
Good |
|
|
|
这是我的代码:
my $queryQuery = "SELECT questionNum, question, answer1, answer2, answer3, answer4, answer5, answer6, answer7, answer8, answer9, answer10, COUNT(*) FROM results WHERE title = ? GROUP BY answer1, answer2, answer3, answer4, answer5, answer6, answer7, answer8, answer9, answer10 ORDER BY questionNum";
my $sthm = $dbh->prepare($queryQuery);
$sthm->execute($marathon);
my $prev_question;
while(my($questNumber, $quest, $ans1, $ans2, $ans3, $ans4, $ans5, $ans6, $ans7, $ans8, $ans9, $ans10, $count) = $sthm->fetchrow_array){
print qq{<tr><td> $questNumber. $quest \n </td></tr>} unless $quest eq $prev_question; # the trailing conditional is to get rid of the duplicate questions that print out.
print qq{<tr><td> $ans1 = $count </td></tr>} unless $ans1 eq "";
print qq{<tr><td> $ans2 = $count </td></tr>} unless $ans2 eq "";
print qq{<tr><td> $ans3 = $count </td></tr>} unless $ans3 eq "";
print qq{<tr><td> $ans4 = $count </td></tr>} unless $ans4 eq "";
print qq{<tr><td> $ans5 = $count </td></tr>} unless $ans5 eq "";
print qq{<tr><td> $ans6 = $count </td></tr>} unless $ans6 eq "";
print qq{<tr><td> $ans7 = $count </td></tr>} unless $ans7 eq "";
print qq{<tr><td> $ans8 = $count </td></tr>} unless $ans8 eq "";
print qq{<tr><td> $ans9 = $count </td></tr>} unless $ans9 eq "";
print qq{<tr><td> $ans10 = $count </td></tr>} unless $ans10 eq "";
$prev_question = $quest;
}
在这里,我循环查询以打印出所有不重复的问题,并打印问题的答案(只要它们不为空)。这是我搜索上述输出的位置 1. 你感觉怎么样?好= 7,差= 4,好= 2。
相反,我将答案分为几组,因为用户使用复选框而不是单选按钮为单个问题选择了多个答案。因此,在数据库内部,即使答案已放入自己的列中,它们也是相互连接的,因为它们是由用户选择问题选项的同时输入的。