我有一段时间没有编码了,最近我开始了一个新项目。在该项目中,我需要进行一个非常简单的内部连接,以关联来自两个表的值:
表格问题:
id | question | order
1 | how? | 1
2 | what? | 2
3 | when? | 3
表格答案:
id_question | answer | order
1 | this way | 1
1 | that way | 2
2 | this | 1
2 | that | 2
3 | now | 1
3 | later | 2
如何正确获取问题和相关答案,并按顺序进行显示?
我做到了:
SELECT id, question, Q.order as qorder, id_question, answer, A.order as aorder FROM questions as Q INNER JOIN answers as A ON Q.id = A.id_question ORDER BY qorder
结果是:
id | question | qorder | id_question | answer | aorder
1 | how? | 1 | 1 | this way | 1
1 | how? | 1 | 1 | that way | 2
2 | what? | 2 | 2 | this | 1
2 | what? | 2 | 2 | that | 2
3 | when? | 3 | 3 | now | 1
3 | when? | 3 | 3 | later | 2
显示结果:
$same_id = -1;
while ( $poll = $qa -> fetch() ) {
if ($poll['id'] == $same_id ) {
echo '<li>'.$poll['answer'].'</li>';
}
else {
if ( $poll['id'] == $same_id+1 ) { echo '</ul>'; }
echo '<ul>'.$poll['question'];
echo '<li>'.$poll['answer'].'</li>';
$same_id = $poll['id'];
}
echo '</ul>';
}
哪个显示器:
<ul>How?
<li>this way</li>
<li>that way</li>
</ul>
<ul>What?
<li>this</li>
<li>that</li>
</ul>
<ul>When?
<li>now</li>
<li>later</li>
</ul>
一切顺利,但感觉不对
首先,我有按"幸运"排序的答案,但没有在请求中指定。
然后,代码感觉太"复杂"了。
我觉得有一种更好更干净的方法来做这种工作。
您可以简单地使用aorder
显式订购:
SELECT ...
FROM ...
INNER JOIN ...
ORDER BY
qorder ASC,
aorder ASC
要显示的代码正常。使用PDOStatement::fetchAll()
和foreach
可以改善它,但这是个人的品味/偏好。
$polls = $qa->fetchAll();
foreach($polls as $poll) {
// output question and answers
}