从mysql内连接显示值的最佳方法

Question

I haven't been coding for a while, and i started a new project lately. In that project, i need to make a very simple inner join to associate values from 2 tables:

table questions:

 id  |  question  |  order
 1   |  how?      |  1
 2   |  what?     |  2
 3   |  when?     |  3

table answers:

 id_question  |  answer    |  order
 1            |  this way  |  1
 1            |  that way  |  2
 2            |  this      |  1
 2            |  that      |  2
 3            |  now       |  1
 3            |  later     |  2

How can I correctly get the questions and related answers, and display them, ordered by order?

I did this:

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

which result in this:

 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

DISPLAYING RESULTS:

$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>';
}

which display:

<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>

it all works out, but it doesn't feel right.

First, I have the answers ordered by "luck", without specifying it in the request.

And then, the code feels too "complicated" for what it is.

I feel there is a better and cleaner way to do this kind of work.

Answer 1

You can simply use the aorder to order explicitly:

SELECT ...
FROM ...
INNER JOIN ...
ORDER BY
    qorder ASC,
    aorder ASC

Your code to display is alright. It could be improved by using PDOStatement::fetchAll() and foreach, but this is personal taste/preference.

$polls = $qa->fetchAll();
foreach($polls as $poll) {
    // output question and answers
}