使用外键将mysql数据打印到EJS文档 [英] Printing mysql data to EJS document with foreign keys

问题描述

链接到github项目

我正在尝试从mysql获取测验问题的答案，并使用EJS模板在node / express应用程序中将其打印为表单。我不知道如何为相应的问题打印正确的答案。

I'm trying to get answers to quiz questions from mysql and print to a form using EJS templating in node/express application. I can't figure out how to print the correct answers to the corresponding question.

我希望数据在浏览器中看起来像这样：

I would like the data to look like this in the browser:

问题1＃id2

• 答案1＃id6


• answer 2＃id7


• answer 3＃id8

问题2＃id3

• answer 1＃id9


• answer 2＃id10


• answer 3＃id11

数据看起来像这样：

loadQuizes;
[ { quizId: 2,
    quizName: 'Bergskedjor',
    dateCreated: '2017-03-03T15:14:02.000Z',
    dateFinished: '2017-03-02T23:00:00.000Z',
    times: 2,
    score: 20 } ]

quizQuestions;
[ { questionId: 2,
    question: 'Vilket ├ñr v├ñrldens h├Âgsta berg?',
    questionQuizid: 2 },
  { questionId: 3,
    question: 'Vilket ├ñr v├ñrldens tredje h├Âgsta berg?',
    questionQuizid: 2 } ]

answers;
[ { answerId: 6,
    answer: 'Question 1 Answer 1',
    correct: 0,
    answerQuestionid: 2 },
  { answerId: 7,
    answer: 'Question 1 Answer 2',
    correct: 1,
    answerQuestionid: 2 },
  { answerId: 8,
    answer: 'Question 1 Answer 3',
    correct: 0,
    answerQuestionid: 2 },

  { answerId: 9,
    answer: 'Question 2 Answer 1',
    correct: 0,
    answerQuestionid: 3 },
  { answerId: 10,
    answer: 'Question 2 Answer 2',
    correct: 0,
    answerQuestionid: 3 },
  { answerId: 11,
    answer: 'Question 2 Answer 3',
    correct: 1,
    answerQuestionid: 3 } ]

从表到达：

mysql> SELECT * FROM quiz;
+--------+------------------------+---------------------+--------------+-------+-------+
| quizId | quizName               | dateCreated         | dateFinished | times | score |
+--------+------------------------+---------------------+--------------+-------+-------+
|      1 | Solution to everything | 2017-03-03 16:14:02 | 2017-03-03   |     2 |    20 |
|      2 | Bergskedjor            | 2017-03-03 16:14:02 | 2017-03-03   |     2 |    20 |
+--------+------------------------+---------------------+--------------+-------+-------+

mysql> SELECT * FROM question;
+------------+-------------------------------------------+----------------+
| questionId | question                                  | questionQuizid |
+------------+-------------------------------------------+----------------+
|          1 | What color is the Sky?                    |              1 |
|          2 | Vilket ├ñr v├ñrldens h├Âgsta berg?        |              2 |
|          3 | Vilket ├ñr v├ñrldens tredje h├Âgsta berg? |              2 |
+------------+-------------------------------------------+----------------+

mysql> SELECT * FROM answers;
+----------+---------------------+---------+------------------+
| answerId | answer              | correct | answerQuestionid |
+----------+---------------------+---------+------------------+
|        1 | Red                 |       0 |                1 |
|        2 | Green               |       0 |                1 |
|        3 | Blue                |       1 |                1 |
|        4 | Pink                |       0 |                1 |
|        5 | Red                 |       0 |                1 |
|        6 | Question 1 Answer 1 |       0 |                2 |
|        7 | Question 1 Answer 2 |       1 |                2 |
|        8 | Question 1 Answer 3 |       0 |                2 |
|        9 | Question 2 Answer 1 |       0 |                3 |
|       10 | Question 2 Answer 2 |       0 |                3 |
|       11 | Question 2 Answer 3 |       1 |                3 |
+----------+---------------------+---------+------------------+

这是我的解决方案到目前为止：

This is my solutions so far:

<h1>
        <% loadQuizes.forEach(function (quizes) { %>
            <h2><%= quizes.quizName %></h2>
        <% }) %>
    </h1>
    <form action="/takequiz" method="POST">
        <% quizQuestions.forEach(function (questions) { %>
            <div class="well well-sm">
                <h3><%= questions.question %></h3>
                    <% answers.forEach(function (ans) { %>
                    <%= ans.answer %><br>
                    <% }) %>
            </div> <!-- well -->
        <% }) %>
    </form>

但这给了我：

Question 1 #id2

 - answer 1 #id6
 - answer 2 #id7
 - answer 3 #id8
 - answer 1 #id9
 - answer 2 #id10 
 - answer 3 #id11

Question 2 #id3

 - answer 1 #id6
 - answer 2 #id7
 - answer 3 #id8
 - answer 1 #id9
 - answer 2 #id10 
 - answer 3 #id11

推荐答案

据我所知，您正在从答案中选择所有内容满足两个问题的表：

As far as I can tell, you're selecting everything from the answers table that satisfy both questions:

SELECT * FROM答案在哪里answerQuestionid IN（SELECT QuestionId从问题在哪里where问Quizid =？ ）;

您可能应该尝试在视图逻辑中添加条件：

You probably should try adding a condition in your view logic:

<% quizQuestions.forEach(function (question) { %>
  <div class="well well-sm">
    <h3><%= question.question %></h3>
    <% answers.forEach(function (ans) { %>
      <% if (ans.answerQuestionid === question.questionId) { %>
        <%= ans.answer %><br>
      <% } %>
    <% }) %>
  </div> <!-- well -->
<% }) %>

这篇关于使用外键将mysql数据打印到EJS文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！