foreign keys are not being created

Question

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

Problem:

The problem with the mysqli code is that it is not able to insert the correct QuestionId value. It keeps displaying 0 for QuestionId in the Answer Table. So can somebody fix this in order to be able to be able to display the correct QuestionId?

It has to be done the SELECT query provided at top. I have to use that in mysqli.

Here are the db tables:

Question Table

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

Answer Table at moment:

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

What Answer Table should look like:

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

Below is the code:

 $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();
}

?>

Answer 1

You need to retrieve the last value of the sequence used as the autoincrementing id in Question directly after the INSERT - do this using the LAST_INSERT_ID() SQL function. You can then use this value as a parameter when you insert into Answer.

This is an article on how this may be done.

You can also restructure your code by eliminating the query for the question id in the insert-answer loop. Instead, when you insert the questions, fill an associative array with the QuestionId for each QuestionNo. While looping over the answers, use the associative array to quickly retrieve the QuestionId. Memory should not be a concert as you currently have all questions and answers in the HTTP request.

The core of the code will then look like:

// 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;
        }
    }
}

NOTE: I'm not a PHP programmer, and I haven't tested this, so there may be syntax errors. Sorry :(