phpsql将select语句转换为db insert的变量

hs1rzwqc  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(282)

我目前正在创建一个测验生成器的过程中,我有一些数据库插入轻微的问题。我试图在代码中做的是在测验表中插入测验标题和描述,这将反过来创建测验id。我想插入这个测验id以及复选框中的类id值。如有任何建议,将不胜感激。
注意:我知道我应该使用prepare语句,而不是我现在使用的语句。我计划在我的主要功能正常工作后解决这个问题。

<form method="post" action="#">
                <p>
                    <label>Quiz Title: </label>
                    <input type="text" placeholder="Insert Quiz Title here" name="quizTitle" class="form-control" />
                </p>
                <p>
                    <label>Quiz Description: </label>
                    <input type="text" placeholder="Insert Quiz Description here" name="description" class="form-control"  />
                </p>

<?php

$showAllClasses = "SELECT * FROM class";
mysqli_query($mysqli, $showAllClasses) or die ('Error finding Classes');

$showClassesResult = mysqli_query($mysqli, $showAllClasses);

echo"<table border='1' cellpadding='10' align='center'>";

echo "<tr><th></th><th>Class ID</th><th>Class Name</th><th>Class 
Description</th></tr>";

 //while ($row = mysqli_fetch_assoc($result)){
  while ($row = $showClassesResult->fetch_object()){
  echo "<tr>";
  echo "<td><input type='checkbox' id='" .$row->classID . "' name='check_box[]' value='" .$row->classID . "'></td>";
  echo "<td>" .$row->classID . "</td>";
  echo "<td>" .$row->className . "</td>";
  echo "<td>" .$row->classDesc . "</td>";
  //echo "<td><button type='button' name='add' id='add' data-toggle='modal' data-target='#questionType' class='btn btn-success'>Edit Students</button></td>";
  echo "</tr>";

}

if (isset($_POST['submit'])) {
//Get POST variables
$quizTitle = '"' . $mysqli->real_escape_string($_POST['quizTitle']) . '"';
$description = '"' . $mysqli->real_escape_string($_POST['description']) . '"';
//echo $quizTitle;
//echo $description;

$getQuizIDQuery = "SELECT quizID FROM quiz ORDER BY quizID DESC LIMIT 1";

mysqli_query($mysqli, $getQuizIDQuery) or die ('Error getting Quiz ID');

$result = mysqli_query($mysqli, $getQuizIDQuery);
//$insertedQuizId = $mysqli->insert_id;

//Question query
$quizCreationQuery = "INSERT INTO quiz (quizTitle, description) VALUES($quizTitle, $description)";

foreach ($_POST['check_box'] as $classID) {

$ClassQuizQuery = "INSERT INTO quiz_class(classID, quizID) VALUES ('$classID', '$result')";

//$insert_ClassQuiz = $mysqli->query($ClassQuizQuery) or die($mysqli->error . __LINE__);

//Run Query
$insert_row = $mysqli->query($quizCreationQuery) or die($mysqli->error . __LINE__);

}
}
?>
    </table>
        <div align="center">
                    <input type="submit" name="submit" value="Submit" 
                    class="btn btn-info"/>
         </div>
            </form>
ppcbkaq5

ppcbkaq51#

下面是代码的一个工作示例的样子(带有注解中建议的一些更改)。我基本上保持了原始代码的原样,但更改了脚本中的位置和顺序。
将html和php分开是您应该始终追求的目标之一。一旦把所有的php代码放在一起,就删除重复的或不必要的内容(包括对最后插入的id的superflous select查询)。
我添加了一些注解以作额外解释。其余的大部分都没有动过

<?php

// first - all code that should always be executed,
// this will later be used in the html output.
$showAllClasses = "SELECT * FROM class";
$showClassesResult = $mysqli->query($showAllClasses) or die ('Error finding Classes');

// now the code that should only be executed on POST request
if (isset($_POST['submit'])) {

    // we only want things to happen if all queries are successful,
    // otherwise we end up with quizzes without class connections.
    $mysqli->begin_transaction();

    //Get POST variables
    $quizTitle = '"' . $mysqli->real_escape_string($_POST['quizTitle']) . '"';
    $description = '"' . $mysqli->real_escape_string($_POST['description']) . '"';

    //Question query
    $quizCreationQuery = "INSERT INTO quiz (quizTitle, description) VALUES($quizTitle, $description)";
    $mysqli->query($quizCreationQuery) or die($mysqli->error . __LINE__);

    // The $mysqli instance knows the last inserted id, so we can just use that.
    $insertedQuizId = $mysqli->insert_id;

    foreach ($_POST['check_box'] as $classID) {
        $ClassQuizQuery = "INSERT INTO quiz_class(classID, quizID) VALUES ('$classID', $insertedQuizId)";
        $mysqli->query($ClassQuizQuery) or die($mysqli->error . __LINE__);
    }

    // Everything should have worked so we can now commit the transaction
    $mysqli->commit();
}

// now that we are done with everything, we start with the html output.
// since we have done all the complicated stuff above, all we have to care
// about, is the html output and iterating over our classes to create the html table.
?>

<form method="post" action="#">
    <p>
        <label>Quiz Title: </label>
        <input type="text" placeholder="Insert Quiz Title here" name="quizTitle" class="form-control"/>
    </p>
    <p>
        <label>Quiz Description: </label>
        <input type="text" placeholder="Insert Quiz Description here" name="description" class="form-control"/>
    </p>

    <table border='1' cellpadding='10' align='center'>
        <tr><th></th><th>Class ID</th><th>Class Name</th><th>Class Description</th></tr>
        <?php while ($row = $showClassesResult->fetch_object()): ?>
            <tr>
            <td><input type='checkbox' id='<?= $row->classID ?>' name='check_box[]' value='<?= $row->classID ?>'></td>
            <td><?= $row->classID ?></td>
            <td><?= $row->className ?></td>
            <td><?= $row->classDesc ?></td>
            <td><button type='button' name='add' id='add' data-toggle='modal' data-target='#questionType' class='btn btn-success'>Edit Students</button></td>
            </tr>
        <?php endwhile ?>
    </table>
    <div align="center">
        <input type="submit" name="submit" value="Submit" class="btn btn-info"/>
    </div>
</form>

相关问题