how do i insert data in 2 tables which are linked by foreign key?

[GauravK]

I checked out this question on stackoverflow but all the responses required direct SQL statements to be executed. I was hoping that F3 would have a cleaner/easier way to do this instead.

Here's the situation. I have 3 tables: quizzes, questions, and choices. One quiz has 10 questions, each question can have multiple choices. They are linked by foreign keys. Here's the rough schematic:

CHOICES

------------

id

question_id_fk

text

QUESTIONS

----------------

id

quiz_id_fk

text

correctanswer 

QUIZZES

------------

id

name

description

I want to create a page where an admin can create a new quiz, add 10 questions (structurally my app requires that each quiz has exactly 10 questions), and add multiple choices for each question ALL ON THE SAME FORM if possible. 

I would really like to do this all on one big page rather than have one form to create the quiz, then get the quiz_id and use that on another form to create the 10 questions, and then go to yet another form where the question_id are pulled up and I update the multiple choices one question at a time. However, I cannot seem to figure out how to get and set the foreign key @id values correctly. That is, I don't know how to build the app such that when a user creates a new quiz, the auto-numerated "id" field from the QUIZ table is used to fill in the "quiz_id_fk" in the QUESTIONS table before the quiz is saved to the database.  And correspondingly, how do I get the question "id" to be used as a foreign key "question_id_fk" in the CHOICES table. 

Thanks!

[ikkez]

Haven't you answered the question yourself already? First create the Quiz, then the Questions, and then the Choices.

When you use the mapper, it's going to be easy to achieve. Here some pseudo code:

$quiz = new \DB\SQL\Mapper($db,'quizzes');
...
$quiz->save();

$question = new \DB\SQL\Mapper($db,'questions');
$choice = new \DB\SQL\Mapper($db,'choices');
foreach($q_data as $q) {
  $question->reset();
  ...
  $question->quiz_id_fk = $quiz->_id;
  $question->save();

   foreach($c_data as $c) {
       $choice->reset();
       ...
       $choice->question_id_fk = $question->_id;
       $choice->save();
   }

}

[GauravK]

Thanks! I did not think of that solution (to get the record "id" straight after processing the save() and assigning it to the next record's foreign key). Just to confirm : what does it mean to use the underscore before the id? ie when you say $quiz->_id how does F3 interpret the _id ? Is it a protected keyword, or can the underscore be applied to any field? Sorry if this is a basic php question not an F3 question

[ved]

From the official documentation at https://fatfreeframework.com/3.6/sql-mapper:

To get the ID of the last inserted row or the last value from a sequence object, you must use the reserved $key value = '_id':

$lastInsertedID = $mapper->get('_id'); // get the ID of the last inserted row or the last value from a sequence object

[GauravK]

Thanks so much for the help - I managed to make the form work to the point where it is taking in all the data (quiz + questions) but am having trouble with the step where I have to 'parse' the POST values to save the right data into the right fields. [I haven't implemented CHOICES table yet until I solve this basic challenge first]

So here's what I have:

function save($f3){    
  $newquiz = new Quizzes ($this->db);
  $newquiz->copyfrom('POST', function($val) {
    return array_intersect_key($val, array_flip(array('name','description')));
  });
  $newquiz->save();
 // this code above works properly because the field names in my form are just 'name' and 'description' which are unique

  $question = new Questions ($this->db);
  for ($i=1; $i<11; $i++) {
  $question->reset();
  $question->quiz_id_fk = $newquiz->_id;
  $question->copyfrom('POST', function($val) {
     return array_intersect_key($val, array('text'=>'question'.$i.'_text', 'correctanswer'=>'question'.$i.'_correctanswer'));
    });
// this part fails because my form has 10 inputs for text, byline, and correctanswer. in the form, each field is named 'question1_text', 'question1_correctanswer' / 'question2_text', and so on, where the fields use a named using a simple loop with {{@i}}. However, when I try to parse the POST values, I'm not sure how to extract the value from 'question1_text' and save it into just 'text' in the database record, and to extract the value from 'question1_correctanswer' from POST and save it into just 'correctanswer' for the database.
    $question->save();
 }

 $f3->reroute('/admin');
 }

I'm certain that the highlighted line is wrong, but can't quite figure out the right way to do this. I've commented the code above to show where I'm getting stuck. Array_flip isn't the right approach in this case, because what i'm trying to do is actually rename the field name that the POST form is submitted with. From my understanding, I cannot redesign the form to use 'text' and 'correctanswer' because I have 10 different questions but every question uses the same field names - so I need to differentiate each question's text and answer in the POST array by naming them 'question1_', 'question2_', etc.

I read this in the user guide, which implies that my form needs to have the exact same field name as the record - but in my case I am looping to insert 10 different records all at once, so I don't know how to resolve the duplicated field name issue: "So, when a Web form is submitted (assuming the HTML name attribute is set to userID), the contents of that input field is transferred to $_POST['userID'], duplicated by F3 in its POST.userID variable, and saved to the mapped field $user->userID in the database. The process becomes very simple if they all have identically-named elements. Consistency in array keys, i.e. template token names, framework variable names and field names is key :)"

Any advice?