php - 单个用户的更新数据库/所有用户的数据库更新

Question

我有一个使用 mysqli 和 php 制作的小程序，人们必须回答来自数据库表的问题。我在一个数据库中有两个表。一种是用于问题和答案，另一种是记录用户在表单中输入的内容(一旦有人点击“开始测验”，用户 ID 就会通过 auto_increment 生成)。

我一切正常，但唯一的问题是，一旦用户回答了问题，第一个表(有一个名为“已解决”tinyint(1) 的列)会根据该人是否解决了该问题从 0 更改为 1或不)更新所有用户的已解决列。

我想让它只针对特定的 id 进行更新，但我不知道该怎么做。这是我的 php 的以下代码。

    <?php require("../require/exponents_database_connection.php"); ?>
<center><div style="overflow:hidden; border:solid; border-color:#39AEB9; display:inline-block; width: 500px; height:auto;">
<div style="vertical-align: middle; padding:25px;">

  <?php 



        // 3. Use returned data



              $exponents = "SELECT * FROM q_exponents WHERE solved = 0 ORDER BY id ASC LIMIT 1 ";
              $result_exponents = mysqli_query($connection, $exponents);
              $question = mysqli_fetch_assoc($result_exponents);


              $getinfo = "SELECT * FROM q_exponents";
              $result_getinfo = mysqli_query($connection, $getinfo);

              $totsolved = "SELECT * FROM q_exponents WHERE solved=1";
              $result_totsolved = mysqli_query($connection, $totsolved);

              $totalsolved=mysqli_num_rows($result_totsolved);            
              $totalquestions=mysqli_num_rows($result_getinfo);




  ?> 



              <?php if(!isset($_POST['Submit']) || isset($_POST['Reset'])) { ?>


              <div style="display: inline-block;">
                <p><b> <?php echo "<p style='font-family:sans-serif;'>".$question["question"]."</p>"; ?> </b></p>
                <?php $question_id = (int) $question['id']; ?>
              <div style="float:left;">
                <form action="" method="post">
                <div>            
                  <input type="radio" name="response" value="<?php echo $question["a1"]?>" checked><?php echo "<p style='display:inline; font-family:sans-serif;'>".$question["a1"]."</p>"?>
                  <input type="radio" name="response" value="<?php echo $question["a2"]?>" style="margin-left:50px;" ><?php echo "<p style='display:inline; font-family:sans-serif;'>".$question["a2"]."</p>"?>       
                  <input type="radio" name="response" value="<?php echo $question["a3"]?>" style="margin-left:50px;" ><?php echo "<p style='display:inline; font-family:sans-serif;'>".$question["a3"]."</p>"?>        
                  <input type="radio" name="response" value="<?php echo $question["a4"]?>" style="margin-left:50px;" ><?php echo "<p style='display:inline; font-family:sans-serif;'>".$question["a4"]."</p>"?>
                  <input type="hidden" name="question_id" value="<?php echo $question["id"] ?>" />
                </div>  
                </div>
                  <div class="empty_block"> </div> <br/>
                  <br><input type="Submit" name="Submit" value="Submit" style="width:150px; height: auto;">
                  <div class="empty_block"> </div> 
                </form>
                </div>



                <?php } elseif(isset($_POST['Submit'])) { ?>

                    <?php 
                      $previous_question_id = (int) $_POST['question_id']; //calls for previous id before randomising

                      $exponents = "SELECT * FROM q_exponents WHERE id = $previous_question_id";
                      $result_exponents=mysqli_query($connection, $exponents);
                      $question=mysqli_fetch_array($result_exponents); //fetches database randomised data where id = previous id


                      $response=$_POST['response'];
                      $a = "a".$previous_question_id;


                      $current_id=mysqli_query($connection, "SELECT AUTO_INCREMENT FROM information_schema.tables WHERE TABLE_SCHEMA = 'exponents' AND TABLE_NAME = 'a_exponents'")->fetch_object()->AUTO_INCREMENT;

                      $answers="UPDATE a_exponents SET $a = $response WHERE id = $current_id - 1";
                      $record_answer=mysqli_query($connection, $answers);

                      $check_answer = "SELECT * FROM a_exponents";
                      $result_check_answer = mysqli_query($connection, $check_answer);
                      $score_result = mysqli_fetch_assoc($result_check_answer);

                      $totsolved = "SELECT * FROM q_exponents WHERE solved=1";
                      $result_totsolved = mysqli_query($connection, $totsolved);
                      $totalsolved=mysqli_num_rows($result_totsolved); //counts how many we have solved


                        if($response == $question['correct']){
                          echo "<p style='font-family:sans-serif; color:#83E046;'><b>CORRECT</b></p>"; ?>
                          <p><b> <?php echo "<p style='display: inline; font-family:sans-serif;'>".$question["question"]."</p>";?>

                          <p style='display: inline; font-family:sans-serif;'>, x = </p>

                          <?php echo "<p style='display: inline; font-family:sans-serif;'>".$question["correct"]."</p>";?></b></p>

                          <?php
                          $solved = "UPDATE q_exponents SET solved = 1 WHERE id = $previous_question_id";
                          mysqli_query($connection, $solved); //updates database so that those with previous id are marked as solved
                          $totalsolved = $totalsolved + 1;?>




                      <?php


                      } else {

                            $solved = "UPDATE q_exponents SET solved =0 WHERE id=$previous_question_id"; 
                            mysqli_query($connection, $solved); 


                        echo "<p style='font-family:sans-serif; color:#E04646;'><b>INCORRECT</b></p>"; ?>
                        <p style='display: inline; font-family: sans-serif;'>Your response was: </p>
                        <?php
                        echo $response;
                        ?>
                          <p><b> <?php echo "<p style='display:inline; font-family:sans-serif;'>".$question["question"]."</p>";?>
                          <p style='display: inline; font-family:sans-serif;'>, x = </p>
                          <?php echo "<p style='display:inline; font-family:sans-serif;'>".$question["correct"]."</p>"; ?></b></p>



                      <?php }?>





                        <?php if($totalsolved==$totalquestions){?>

                          <br><form action="" method="post"><input type="submit" name="" value="One More Time" onclick="<?php $solved = "UPDATE q_exponents SET solved = 0"; mysqli_query($connection,$solved);?>"></form>

                        <?php } elseif ($response !== $question['correct']) { ?>
                          <br><form action="" method="get"><input type="submit" name="next" value="Try Again"/></form>

                        <?php } else { ?>

                          <br><form action="" method="get"><input type="submit" name="next" value="Next Question"/></form>

                        <?php }              

                        } ?>





</div>
</div></center>



<?php 
// 4. Release returned data
 mysqli_free_result($result_exponents);

?>


<?php
require("../require/close_connection.php");
?>



<?php 
// 2. Perform database query
  //collects data from database by a resource
// Test if there was a query error

  if (!$result_exponents) {
    die("Database query failed.");
  }

?>

这些是我的表格:1.问题表(q_exponents)

+----+-----------------------------------------------------------------------------------+-----+----+-----+-----------+---------+--------+
| id | question                                                                          | a1  | a2 | a3  | a4        | correct | solved |
+----+-----------------------------------------------------------------------------------+-----+----+-----+-----------+---------+--------+
|  1 | 2<sup>4</sup> &times; 2<sup>3</sup> = 2<sup>x</sup>                               | 7   | 4  | 12  | 10        | 7       |      1 |
|  2 | 2<sup>6</sup> &times; 2<sup>x</sup> = 2<sup>8</sup>                               | 5   | 2  | 1.5 | 3         | 2       |      1 |
|  3 | <sup>2<sup>12</sup></sup> &frasl; <sub>2<sup>4</sup></sub> = 2<sup>x</sup>        | 15  | 5  | 8   | 6         | 8       |      1 |
|  4 | <sup>2<sup>x</sup></sup> &frasl; <sub>2<sup>5</sup></sub> = 2<sup>3</sup>         | 3   | 48 | 7   | 8         | 8       |      1 |
|  5 | 2<sup>x</sup> &times; 2<sup>2</sup> = 2<sup>5</sup>                               | 3   | 1  | 2.5 | 10        | 3       |      1 |
|  6 | 5<sup>x</sup> &times; 5<sup>7</sup> = 5<sup>25</sup>                              | 1   | 32 | 18  | 175       | 18      |      1 |
|  7 | <sup>103<sup>14</sup></sup> &frasl; <sub>103<sup>15</sup></sub> = 103<sup>x</sup> | -1  | 29 | 1   | No Answer | -1      |      0 |
|  8 | <sup>35<sup>17</sup></sup> &frasl; <sub>35<sup>x</sup></sub> = 35<sup>34</sup>    | 41  | 2  | 0.5 | -17       | -17     |      0 |
|  9 | 103<sup>x</sup> &times; 103<sup>-1</sup> = 103<sup>89</sup>                       | 88  | 89 | 90  | 91        | 90      |      0 |
| 10 | 65<sup>-x</sup> &times; 65<sup>1</sup> = 65<sup>9</sup>                           | -10 | 10 | 8   | -8        | -8      |      0 |
| 11 | <sup>3<sup>3x</sup></sup> &frasl; <sub>3<sup>2x</sup></sub> = 3<sup>4</sup>       | 2   | 3  | 4   | 5         | 4       |      0 |
| 12 | 4<sup>3x</sup> &times; 4<sup>2x</sup> = 4<sup>20</sup>                            | 2   | 3  | 4   | 5         | 4       |      0 |
+----+-----------------------------------------------------------------------------------+-----+----+-----+-----------+---------+--------+

答案表 (a_exponents)

a_exponents

Answer 1

您为答案表提供的链接已损坏。

你有用户表吗？您必须有一个。由于无法看到您的答案表是什么样子，我只能建议它看起来像这样:

+--------+-------------+----------------------------+
|user_id | question_id | answer                     |
+--------+-------------+----------------------------+
|   1    |      1      | their answer               |
|   1    |      2      | their next answer          |
+--------+-------------+----------------------------+

您还需要使用 session 处理(在用户回答问题时跟踪用户，但这超出了本文的范围。