Question:

Want to perform a Select Query below (must be this query):

SELECT QuestionId FROM Question WHERE (QuestionNo = ? AND SessionId = ?)

In order to be able to find the QuestionId's in the Question table and store it in the Answer Table for all the answers so that we can determine which answers belong to which question

问题:

mysqli 代码的问题在于它无法插入正确的 QuestionId 值。它一直在 Answer 表中为 QuestionId 显示 0。那么有人可以解决此问题以便能够显示正确的 QuestionId 吗？

必须完成顶部提供的 SELECT 查询。我必须在 mysqli 中使用它。

这是数据库表:

问题表

QuestionId (auto)  SessionId  QuestionNo
4                  2          1
5                  2          2
6                  2          3

此刻的答案表:

AnswerId (auto)  QuestionId  Answer
7                0           A
8                0           C
9                0           A
10               0           B
11               0           True

答案表应该是什么样的:

AnswerId (auto)  QuestionId  Answer
7                4           A
8                4           C
9                5           A
10               5           B
11               6           True

代码如下:

 $questionsql = "INSERT INTO Question (SessionId, QuestionNo) 
    VALUES (?, ?)";
if (!$insert = $mysqli->prepare($questionsql)) {
    // Handle errors with prepare operation here
    echo __LINE__.': '.$mysqli->error;
}

 $answersql = "INSERT INTO Answer (QuestionId, Answer) 
    VALUES (?, ?)";
if (!$insertanswer = $mysqli->prepare($answersql)) {
    // Handle errors with prepare operation here
    echo __LINE__.': '.$mysqli->error;
}



//make sure both prepared statements succeeded before proceeding
if( $insert && $insertanswer)
{
    $sessid =  $_SESSION['id'] . ($_SESSION['initial_count'] > 1 ? $_SESSION['sessionCount'] : '');
    $c = count($_POST['numQuestion']);

    for($i = 0;  $i < $c; $i++ )
    {


            $insert->bind_param("ii", $sessionid, $_POST['numQuestion'][$i]);

            $insert->execute();

            if ($insert->errno) 
            {
                // Handle query error here
                echo __LINE__.': '.$insert->error;
                break 1;
            }
}

        $results = $_POST['value'];
        foreach($results as $id => $value) 
        {
            $answer = $value;

            $lastID = $id;

            $questionidquery = "SELECT QuestionId FROM Question WHERE (QuestionNo = ? AND SessionId = ?)";

        if (!$questionidstmt = $mysqli->prepare($questionidquery)) {
        // Handle errors with prepare operation here
          echo __LINE__.': '.$mysqli->error;
        }


        // Bind parameter for statement
        $questionidstmt->bind_param("ii", $lastID, $sessionId);

        // Execute the statement
        $questionidstmt->execute();

                    if ($questionidstmt->errno) 
                    {
                        // Handle query error here
                        echo __LINE__.': '.$questionidstmt->error;
                        break 2;
                    }

        // This is what matters. With MySQLi you have to bind result fields to
        // variables before calling fetch()
        $questionidstmt->bind_result($quesid);

        // This populates $optionid
        $questionidstmt->fetch(); 

        $questionidstmt->close(); 



            foreach($value as $answer) 
            {
                $insertanswer->bind_param("is", $quesid, $answer);

                $insertanswer->execute();

                if ($insertanswer->errno) {
                    // Handle query error here
                    echo __LINE__.': '.$insertanswer->error;
                    break 3;
                }
            }
        }


    //close your statements at the end


    $insertanswer->close();
    $insert->close();
}

?>

最佳答案

您需要直接在 INSERT 之后检索用作自动递增 ID 的序列的最后一个值 - 使用 LAST_INSERT_ID() SQL 函数执行此操作。然后，您可以在插入 Answer 时将此值用作参数。

This is an article关于如何做到这一点。

您还可以通过消除插入答案循环中对问题 ID 的查询来重构代码。相反，当您插入问题时，使用每个 QuestionNo 的 QuestionId 填充一个关联数组。在遍历答案时，使用关联数组快速检索 QuestionId。内存不应该是一场音乐会，因为您目前在 HTTP 请求中有所有问题和答案。

代码的核心将如下所示:

// AA: Declare an empty associative array for QuestionNo -> QuestionID
$question_ids = array()

for($i = 0;  $i < $c; $i++ )
{
        // AA: Extract the QuestionNo for multiple use
        $questionNo = $_POST['numQuestion'][$i];

        $insert->bind_param("ii", $sessionid, $questionNo);

        $insert->execute();

        if ($insert->errno)
        {
            // Handle query error here
            echo __LINE__.': '.$insert->error;
            break 1;
        }

        // AA: Retrieve the questionId from MySQL
        $questionId = mysql_insert_id();

        // AA: Add a key-value pair (QuestionNo, QuestionId) to $question_ids
        $question_ids[$questionNo] = $questionId;
}

$results = $_POST['value'];
foreach($results as $id => $value)
{
    $answer = $value;

    // AA: Look up the QuestionId for the question number
    $quesid = $question_ids[$id];

    foreach($value as $answer)
    {
        $insertanswer->bind_param("is", $quesid, $answer);

        $insertanswer->execute();

        if ($insertanswer->errno) {
            // Handle query error here
            echo __LINE__.': '.$insertanswer->error;
            break 3;
        }
    }
}

注意:我不是PHP程序员，也没有测试过这个，所以可能存在语法错误。对不起:(

关于mysql - 没有创建外键，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14173241/