时间戳

首页 > 解决方案 > 使用 PHP 进行测验的 3 个选项显示问题

php - 使用 PHP 进行测验的 3 个选项显示问题

问题描述

所以我正在做一个项目，我有一个充满问题/选项的数据库。每个问题有 3 个选项（其中 1 个是对的）。
在用户开始测验之前，他选择了他想要回答的问题数。我使用选择表单来执行此操作。然后 PHP 将值从选择表单发送到测验页面。它在 SQL 查询中的使用位置：

SELECT *
FROM quiz_question
WHERE quiz_id = 1
ORDER BY RAND()
LIMIT $number

选择表格的$number价值代表
我可以显示问题，但不能显示所选问题的选项。
我的代码：

<div id="quiz">
  <?php
  $number = $_POST['number'];
  $sql = "SELECT *
    FROM quiz_question
    WHERE quiz_id = 1
    ORDER BY RAND()
    LIMIT $number";
  $result = mysqli_query($conn,$sql);
  if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
      echo "<p>";
      echo $row['question'];
      echo "</p>";
    }
  }
  ?>
</div>

我的数据库看起来像这样：

quiz

quiz_question

quiz_question_option

编辑： 我尝试使用INNER JOIN，但是当我使用时ORDER BY RAND()，LIMIT我无法为一个问题获得所有 3 个选项。

标签： phpmysqlsql

解决方案

抛开 SQL 注入问题（您应该使用准备好的语句），这里是如何使用辅助查询来获取测验的所有选项以进行渲染。

<div id="quiz">
  <?php

    // aggregate an array of options
    $option_results = mysqli_query($conn, "SELECT qo.* FROM quiz_question_option qo
        LEFT JOIN quiz_question q ON (qo.quiz_question_id = q.id)
        WHERE q.quiz_id = 1");
    $options = [];
    while ($option = mysqli_fetch_assoc($option_results)) {
        $options[$option['quiz_question_id']] = $option;
    }
    mysqli_free_result($option_results);

    $number = $_POST['number'];
    $sql = "SELECT * FROM quiz_question WHERE quiz_id = 1 ORDER BY RAND() LIMIT $number";
    $result = mysqli_query($conn,$sql);

    if (mysqli_num_rows($result) > 0)
    {
         while ($question = mysqli_fetch_assoc($result)) {
             echo "<p>";
             echo $question['question'];
             echo "</p>";
         }

         if (isset($options[$question['id']])) {
             echo "<ul>";
             foreach ($options[$question['id']] as $option) {
                 echo $option['quiz_option'];
             }
             echo "</ul>";
         }
    }
  ?>
</div>

现在，要转换为准备好的语句：

<div id="quiz">
  <?php
    $number = $_POST['number'] ?? 10000;
    $quiz_id = 1;

    // aggregate an array of all questions' options of a quiz
    $option_stmts = mysqli_prepare($conn, "SELECT qo.* FROM quiz_question_option qo
        LEFT JOIN quiz_question q ON (qo.quiz_question_id = q.id)
        WHERE q.quiz_id = ?");
    $option_stmts->bind_param('i', $quiz_id);
    $option_results = $option_stmts->execute();
    $options = [];
    while ($option = mysqli_fetch_assoc($option_results)) {
        $options[$option['quiz_question_id']] = $option;
    }
    mysqli_free_result($option_results);
    mysqli_stmt_close($option_stmts);

    // query for all the questions of a quiz
    $stmt = mysqli_prepare($conn, "SELECT * FROM quiz_question WHERE quiz_id = ? ORDER BY RAND() LIMIT ?");
    $stmt->bind_param('ii', $quiz_id, $number);
    $result = $stmt->execute();

    if (mysqli_num_rows($result) > 0)
    {
         while ($question = mysqli_fetch_assoc($result)) {
             echo "<p>";
             echo $question['question'];
             echo "</p>";
         }

         if (isset($options[$question['id']])) {
             echo "<ul>";
             foreach ($options[$question['id']] as $option) {
                 echo $option['quiz_option'];
             }
             echo "</ul>";
         }
    }
  ?>
</div>

推荐阅读