php - 从 mysql inner join 显示值的最佳方法

Question

我已经有一段时间没有编码了，最近我开始了一个新项目。在那个项目中，我需要做一个非常简单的内部连接来关联 2 个表中的值:

表格问题:

 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>

一切顺利，但感觉不对。

首先，我有按“运气”排序的答案，但没有在请求中指定。

然后，代码感觉太“复杂”了。

我觉得有一种更好、更简洁的方法来完成这种工作。

Answer 1

您可以简单地使用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
}