Inserting values into a relational database

Question

I'm trying to insert some data into a relational database (MySQL using InnoDB engine) with the tables and columns below via PHP.

• Quiz(id, name)
• Questions(quiz_id, id, name)
• Answers(question_id, id, name)

The primary key ids are all auto-increment.

This will allow me to search for a particular quiz, then get the questions belonging to that quiz and (in turn) the multiple choice possible answers belonging to those questions. Each question will have at least two possible answers.

I'm creating an interface that will allow users to create a new quiz. I've seen examples of how you can use transactions and LAST_INSERT_ID() to ensure that the primary keys match up, but cannot get this to work when there are multiple inserts that are dependent on the previous 'parent' (the possible answers must be linked to the correct question).

i.e. The process will be:

1. Insert new quiz

2. Use the quiz id to insert a question

3. Use the question id to insert all possible answers to that question

4. Repeat steps 2 & 3 until all questions and answers have been entered, then commit the transaction

I've attempted to outline this in PHP. The questions and answers are provided via POST as strings, separated by commas. I use explode() to convert these into arrays, and then assigned variables to each value in the array. I've started using for loops, which I think will work in principle for the questions, but not the answers.

$dbc = @mysqli_connect($host, $user, $password, $db) or die ('Could not connect to MySQL: ' . mysqli_connect_error());


$qN= ($_POST['quizName']); 
$quizName = mysqli_real_escape_string($qN);


$qu = ($_POST['question']);  
$question = mysqli_real_escape_string($qu);

$questionArray = explode(',', $question);
$numberQuestions = count($questionArray);

$i = 1;

foreach ($questionArray as $variable)
{ 
  ${'q_'.$i} = $variable;     
  ++$i;
}   

$an = ($_POST['answer']); 
$answer = mysqli_real_escape_string($an);

$answerArray = explode(',', $answer);
$numberAnswers = count($answerArray);

$j = 1;

foreach ($answerArray as $variable)
{ 
  ${'a_'.$j} = $variable;     
  ++$j;
}

//turn off AUTOCOMMIT, then run the required queries
$dbc->autocommit(FALSE);

$dbc->query("INSERT INTO Quiz(name) 
VALUES
(".$quizName.")");

$quizID = $dbc->insert_id;

$n = 1;

for ($x=0; $x<$numberQuestions; $x++)
{
  $dbc->query("INSERT INTO Question (quiz_id, question_name) 
  VALUES
  (".$quizID.",".${'q_'.$n}.")");

  $questionID = $dbc->insert_id;
  echo $questionID;
  ++$n;

  $m = 1;

  for ($y=0; $y<$numberAnswers; $y++)
  {
    $dbc->query("INSERT INTO Answer(question_id, name) 
    VALUES
    (".$questionID.",".${'a_'.$m}.")");

    ++$m;
  }
}
// commit transaction
$dbc->commit();
// close connection
$dbc->close();

I'd really appreciate any help you can give - please let me know if you need any more information or any clarifications.

Answer 1

$dbc->query("INSERT INTO Question (quiz_id, question_name) 
  VALUES
  (".$surveyID.",".${'q_'.$n}.")");

Should be changed to:

$dbc->query("INSERT INTO Question (quiz_id, question_name) 
  VALUES
  (".$quizID.",".${'q_'.$n}.")");

as earlier in the code, you do:

$quizID = $dbc->insert_id;

in fact, $surveyID, isn't found anywhere else in your code.