Insert multiple rows into a MySQL database from a table

dave Published at Dev

Dave

I retrieve data from databased based on 4 dropdown criteria.Display them on a table and add one column for scores of each student. I want to populate another table with all the record displayed alongside the scores in each row.I have tried all I know, look at scenerios from Stackoverflow but seems not to work. I need help pls.

<form id="form1" name="form1" method="POST">
  <fieldset id="Result">
  <legend>Result Capture</legend>
    <table width="1110" border="0">
      <tr>
         <td width="236">Session:<span id="spryselect1">
          <label for="SessionAss"></label>
          <?php
          include 'Connections/connection2.php';
          $res3= mysqli_query($connection,"SELECT * FROM schsession ");
          ?>
          <select name="SessionAssigned" id="SessionAssigned" class="sess" />
          <option>Select</option>
          <?php while($row3=mysqli_fetch_array($res3)){?>
          <option value="<?php echo $row3["session"];?>"><?php echo $row3["session"];?></option>
          <?php };?>
          </select></td>

        <td width="231">Term:<span id="spryselect4">
          <label for="Term"></label>
          <?php
          include 'Connections/connection2.php';
          $res4= mysqli_query($connection,"SELECT * FROM term_tab ");
          ?>
          <select name="Term" id="Term" class="term" />
          <option>Select</option>
          <?php while($row4=mysqli_fetch_array($res4)){?>
          <option value="<?php echo $row4["name"];?>"><?php echo $row4["name"];?></option>
          <?php };?>
          </select></td>

        <td width="219">Class  <span id="spryselect1">
          <label for="class"></label>
          <?php
          include 'Connections/connection2.php';
          $res= mysqli_query($connection,"SELECT * FROM class_tab ORDER BY name");
          ?>

        <select name="class" id="class" class="class">
        <option>Select</option>
          <?php while($row2=mysqli_fetch_array($res)){?>
          <option value="<?php echo $row2["name"];?>"><?php echo $row2["name"];?></option>
          <?php };?>
        </select>
         </span></td>
       <td width="220" class="sub" id="sub" name="sub">Subject:</td>

      <td width="182"><input type="submit" name="RetBtn" id="RetBtn" value="Retrieve Data" /></td>
       </tr>

    </table></fieldset>
   </form>
  <form id="form2" name="form2" method="post" action="">
  <fieldset><legend>Result Computation</legend>
  <table width="840" border="0">
  <tr>
    <td width="82" height="18">SN</td>
    <td width="245">Admission No</td>
    <td width="143">Surname</td>
    <td width="87">Firstname</td>
    <td width="87">Class</td>
    <td width="87">Session</td>
    <td width="87">Term</td>
    <td width="69">Subject</td>
    <td width="98">CA(Score)</td>
  </tr>
  <?php
  if(!isset($_POST["RetBtn"])){
     $class="";$sess="";$term="";$sub="";
  }else{
    $class=$_POST["class"];
    $sess=$_POST["SessionAssigned"];
    $term=$_POST["Term"];
    $sub= $_POST["sub"]; 
    $get = "SELECT * FROM stdsubtrans_tab,admission_tab WHERE  stdsubtrans_tab.AdmNo=admission_tab.AdmNo AND stdsubtrans_tab.class='$class'
            AND stdsubtrans_tab.CSession='$sess' AND stdsubtrans_tab.Term='$term'"; 
    $confirm = mysqli_query($connection,$get);
    $c=0;
    while($fetch=mysqli_fetch_array($confirm)){
        $c++;
    echo "<tr>";
    echo "<td>".$c."</td>";
    echo "<td>".$fetch["AdmNo"]."</td>";
    echo "<td>".$fetch["Surname"]."</td>";
    echo "<td>".$fetch["Firstname"]."</td>";
    echo "<td>".$fetch["class"]."</td>";
    echo "<td>".$fetch["CSession"]."</td>";
    echo  "<td>".$fetch["Term"]."</td>";
    echo "<td>".$sub."</td>";
    echo  '<td><input type="text" name="score"></td>';
     echo"</tr>";    

    //if score supplied , then click to save to database
   if(isset($_POST["saveBtn"])){
    $score= $_POST["score"];
  $insert = "INSERT INTO result_tab(CSession,Term,Class,AdmNo,subject,score)VALUES ('".$fetch["CSession"]."','".$fetch["Term"]."',
                '".$fetch["class"]."','".$fetch["AdmNo"]."','".$sub."','".$score."') ";
 $succ = mysqli_query($connection,$insert); 

     }

  }
  }


  ?>


   <tr>
        <td><input type="submit" name="saveBtn" id="saveBtn" value="Save Score" /></td><td> <input type="reset" name="cancel" id="cancel" value="Cancel" /></td>
      </tr>
</table>

  </fieldset>
  </form>

Dave

I have found an answer to the question: Just declare the name field as an array and insert them into the DB using foreach loop.

while($fetch=mysqli_fetch_array($confirm)){
        $c++;
    echo "<tr>";
    echo "<td>".$c."</td>";
    echo"<td><input type='text' name='admNo[]' value='".$fetch["AdmNo"]."'></td>";
    echo"<td><input type='text' name='sname[]' value='".$fetch["Surname"]."'></td>";
    echo"<td><input type='text' name='fname[]' value='".$fetch["Firstname"]."'></td>";
    echo"<td><input type='text' name='class[]' value='".$fetch["class"]."'></td>";
    echo"<td><input type='text' name='SessionAssigned[]' value='".$fetch["CSession"]."'></td>";
    echo"<td><input type='text' name='Term[]' value='".$fetch["Term"]."'></td>";
    echo "<td><input type='text' name='sub[]' value='".$sub."'</td>";
    echo "<td><input type='text' name='score[]'></td>";
    echo "</tr>";   
    }
  //if score is  supplied , then click to save to database
     }
   if(isset($_POST["saveBtn"])){

       foreach($_POST["admNo"] as $rec=> $value){
         $cl = $_POST["class"][$rec];
         $term = $_POST["Term"][$rec];
         $ad = $_POST["admNo"][$rec];
         $Csess = $_POST["SessionAssigned"][$rec];
         $sub = $_POST["sub"][$rec];
         $sc = $_POST["score"][$rec];  

  $insert = "INSERT INTO result_tab(CSession,Term,Class,AdmNo,subject,score)VALUES('$Csess','$term',
                '$cl','$ad','$sub','$sc')";
 $succ = mysqli_query($connection,$insert); 
       }

     }

Collected from the Internet

Please contact [email protected] to delete if infringement.