Kayb Kayb - 3 months ago 10
MySQL Question

How to insert static and dynamic data as same (multiple)rows into MySQL database from a PHP form in one submission

****NOTE**: I have asked this question on this site and have been suggested a solution, the logic of which I do not get, and am still unable to implement it. This is therefore an edited version of the initial question to make it more concise and perhaps clearer so I could hopefully get more responses. I have also changed the title to what I hope will be more meaningful.**

I have started learning PHP along with MySQL(complete newbie), and I have created a database with questions and options tables as part of a quiz app project to help me learn . I thought it would be a good learning experience to populate the database by submitting the questions and options through a PHP processed form.

The problem I am having in a nutshell is this,I have the following form:

[dataentry form]

http://i.stack.imgur.com/3S0gu.png
Question section of form(variable rows) with single field constant metadata

<div class="questinfo">
<div class="questmetadata">

<select name="questfield[0][subject_id]">
<option>BA</option>
<option>MY</option>
<option>PA</option>
<option>BB</option>
<option>CC</option>
<option>UA</option>
<option>HM</option>
<option>CG</option>

</select>
<input name="questfield[0][chapter_id]">

<input name="questfield[0][topic_id]">
<input name="questfield[0][is_active]">
<select name="questfield[0][type]">
<option>P</option>
<option>L</option>
</select>

<select name="questfield[0][level]">
<option>1<option>
<option>2 II<option>
<option>3<option>
</select>

</div>

<div class="questrow">
<input name="questfield[1][questid]">
<input name="questfield[1][numero]">
<textarea name="questfield[1][question]"></textarea>
</div>

<div class="questfield">
<input name="questfield[2][id]">
<input name="questfield[2][numero]">
<input name= "questfield[2][is_active]">
<textarea name="questfield[2][question]"></textarea>
</div>


<div class="questrow">
<input name="questfield[3][questid]">
<input name="questfield[3][numero]">
<textarea name="questfield[3][question]"></textarea>
</div>


Options section of form; variable option fields with unique corresponding metadata fields.

<div class="optionfield">
<input type="text" name ="optionfield[0][optid]">
<input type="text" name ="optionfield[0][choice]">
<input type="text" name ="optionfield[0][question_numero]">
<input type="checkbox" name ="optionfield[0][is_answer]">

</div>

<div class="optionfield">
<input type="text" name ="optionfield[1][optid]">
<input type="text" name ="optionfield[1][choice]">
<input type="text" name ="optionfield[1][question_numero]">
<input type="checkbox" name ="optionfield[1][is_answer]">
</div>


<div class="optionfield">
<input type="text" name ="optionfield[2][optid]">
<input type="text" name ="optionfield[2][choice]">
<input type="text" name ="optionfield[2][question_numero]">
<input type="checkbox" name ="optionfield[2][is_answer]">
</div>


<div class="optionfield">
<input type="text" name ="optionfield[3][optid]">
<input type="text" name ="optionfield[3][choice]">
<input type="text" name ="optionfield[3][question_numero]">
<input type="checkbox" name ="optionfield[3][is_answer]">
</div>


This is the corresponding PHP; only the first row is entered into the question table but the options table has all four rows.(because unlike for the questions section of the form, all the column entries in the options table by necessity must correspond to a distinct field in the form)

$optsql=" INSERT INTO

options


SET
optid=:optid,
question_numero=:question_numero,
choice=:choice,
is_answer=:is_answer,
answer_explanation=:answer_explanation";

$questsql=" INSERT INTO questions

SET
questid=:questid,
numero=:numero,
question=:question,
subject_id=:subject_id,
chapter_id=:chapter_id,
topic_id=:topic_id,
type=:type,
level=:level,
is_active=:is_active

";


$optstmt=$clsdb->prepare($optsql);
$queststmt=$clsdb->prepare($questsql);



foreach($_POST['optionfield'] as $optinput){

$optdata= array(

'optid'=>$optinput['optid'],
'question_numero'=>$optinput['question_numero'],
'choice'=>$optinput['choice'],
'answer_explanation'=>$optinput['answer_explanation'],
'is_answer'=>$optinput['is_answer']);

$optstmt->execute($optdata);


}

foreach($_POST['questfield'] as $questinput){

$questdata= array(

'questid'=>$questinput['questid'],
'numero'=>$questinput['numero'],
'question'=>$questinput['question'],
'subject_id'=>$questinput['subject_id'],
'chapter_id'=>$questinput['chapter_id'],
'topic_id'=>$questinput['topic_id'],
'type'=>$questinput['type'],
'level'=>$questinput['level'],
'is_active'=>$questinput['is_active']);

$queststmt->execute($questdata);

}


The form is in two sections: the questions and corresponding options respond to one submit button but insert data into different(option and question)tables in the database. In the above image, I have four rows of fields in the questions section of the form(please forgive the styling or lack thereof). The first row is for the metadata, and the three rows beneath that(each ending with the textbox) represent three separate question rows to be entered in the database.The metadata row contains data that I want to fill respective columns in the three question rows once submitted to the database.With my current PHP code, only one question row inserts along with the appropriate metada. What I want is the following:

[desire results]

http://i.stack.imgur.com/9LpJP.png

But with the metadata filled out(instead of the zeroes).The metadata should vary based on what is selected in the form for various submissions but be the same for any number of rows inserted at the same time with a given submission.

Based on the above code, a user named Martin Law suggested the following solution:

foreach($_POST['questfield'] as $key => $value){
//static vars(metadata)
$questdata[$key] = $value;
foreach($_POST['optionfield'] as $key => $value){
//changing vars(answers)
$questdata[$key] = $value;
//Write to db
$stmt->execute($query);
}


}

However, the above code returns a notice error:"arrow to string conversion", which I found out was because $questdata which I passed into the execute function is a multidimensional array. Moreover, I do not understand the logic of this code.I had separate insert queries and prepared statements for question and options, but in the above code only one statement and query($query ) is implied.What should the "$query" be? Also in the code,there is a nested foreach loop passing the "optionfield" array and assigning the elements to the $questdata, how does that fit into the solution to the problem which primarily concern the questions table? Would anyone please elaborate on the logic of Martin's solution and how I can implement it successfully based on the data provided, or offer an original solution? Thank you.

Answer

Consider breaking up the metadata from the questfield array as they remain static for the form. And in the insert query, re-use those scalar $_POST fields along with the questfield values that are dynamic (questid, numero, question):

HTML

<div class="questmetadata">
  <select name="subject_id">
      <option>BA</option>
      <option>MY</option>
      <option>PA</option>
      <option>BB</option>
      <option>CC</option>
      <option>UA</option>
      <option>HM</option>
      <option>CG</option>
  </select>

  <input name="chapter_id">

  <input name="topic_id">
  <input name="is_active">

  <select name="type">
      <option>P</option>
      <option>L</option>
  </select>

  <select name="level">
      <option>1<option>
      <option>2 II<option>
      <option>3<option>
  </select>
</div>

With change be sure to adjust indices for questfield:

<div class="questrow">
   <input  name="questfield[0][questid]">
   <input  name="questfield[0][numero]">
   <textarea  name="questfield[0][question]"></textarea>
</div>

PHP

...
foreach($_POST['questfield'] as $questinput){

    $questdata= array(
        'questid'=>$questinput['questid'],         // DYNAMIC FIELDS
        'numero'=>$questinput['numero'],
        'question'=>$questinput['question'],
        'subject_id'=>$_POST['subject_id'],        // STATIC FIELDS
        'chapter_id'=>$_POST['chapter_id'],
        'topic_id'=>$_POST['topic_id'],
        'type'=>$_POST['type'],
        'level'=>$_POST['level'],
        'is_active'=>$_POST['is_active']
    );

    $queststmt->execute($questdata);
}
Comments