使用php通过单个提交按钮插入多行数据

Question

How do I retrieve data from a SQL table, modify the data and store it in another database table with multiple rows & columns and with single submit button I want insert every rows at a time I don't know how to get that hidden value and work properly with that

<?php
 include"connect_database.php";
 if(isset($_POST['submit'])) {
         $amt = $_POST['total'];
         if($amt > 0) {
                $qry = "INSERT INTO attendance(rollno, name, year, attendance, reason) VALUES "; // Split the mysql_query
                for($i=1; $i<=$amt; $i++) {
                        $qry .= "('".$_POST["rollno$i"]."', '".$_POST["name$i"]."', '".$_POST["year$i"]."', '".$_POST["attendance$i"]."', '".$_POST["reason$i"]."' ),"; // loop the mysql_query values to avoid more server loding time
                }
                $qry    = substr($qry, 0, strlen($qry)-2);
                $insert = mysqli_query($dbcon, $qry); // Execute the mysql_query
         }
 // Redirect for each cases
         if($insert) {
                $msg = '<script type="text/javascript">alert("added");</script>';
         }
         else {
                $msg = '<script type="text/javascript">alert("Server Error, Kindly Try Again");</script>';
         }

};

if (isset($_POST['select']))
{
  $sql = "SELECT * FROM data WHERE year='" . $_POST['yr'] . "'";
$myData = mysqli_query($dbcon, $sql);
$num = mysqli_num_rows($myData);


  echo "<table border=1>
  <tr>
  <th>Rollno</th>
  <th>Name</th>
  <th>Year</th>
  <th>Attendance</th>
  <th>reason</th>
  </tr>";

 for ($i=0; $i <$num; $i++)
{
$record = mysqli_fetch_array($myData);
echo "<form action=smanage.php method=post>";
  echo "<tr>";
  echo "<td>" . "<input type=text name=rollno$i value=" . $record['rollno'] . " </td>";
  echo "<td>" . "<input type=text name=name$i value=" . $record['name'] . " </td>";
  echo "<td>" . "<input type=text name=year$i  value=" . $record['year']  . " </td>";
 echo  "<td> "."<select name=attendance$i >
      <option value=Present >present</option>
      <option value=Absent >Absent</option>
  </select>"."</td>";
  echo "<td>". "<textarea cols=15 rows=2 name=reason$i  placeholder=Enter reason ...></textarea>" . "</td>" ;
  echo "<td>" . "<input type=hidden name=total value=" . $i-1 . "</td>";
  echo "</tr>";


}

echo"</table>";

echo "<input type=submit name=submit value=save class=Button3>";

echo "</form>";




 };


mysqli_close($dbcon);


?>

Answer 1

    <?php
     include"connect_database.php";
     if(isset($_POST['submit'])) {
             $amt = $_POST['total'];
     $rollnos= $_POST['rollno'];
             if($amt > 0) {
                    $qry = "INSERT INTO attendance(rollno, name, year, attendance, reason) VALUES "; // Split the mysql_query
    $i=0;
                    foreach($rollnos as $rollno) {
                            $qry .= "('".$rollno."', '".$_POST["name"][$i]."', '".$_POST["year"][$i]."', '".$_POST["attendance"][$i]."', '".$_POST["reason"][$i]."' ),"; // loop the mysql_query values to avoid more server loding time
$i=$i+1;
                    }
                    $qry    = substr($qry, 0, strlen($qry)-2);
                    $insert = mysqli_query($dbcon, $qry); // Execute the mysql_query
             }
     // Redirect for each cases
             if($insert) {
                    $msg = '<script type="text/javascript">alert("added");</script>';
             }
             else {
                    $msg = '<script type="text/javascript">alert("Server Error, Kindly Try Again");</script>';
             }

    };

    if (isset($_POST['select']))
    {
      $sql = "SELECT * FROM data WHERE year='" . $_POST['yr'] . "'";
    $myData = mysqli_query($dbcon, $sql);
    $num = mysqli_num_rows($myData);


      echo "<table border=1>
      <tr>
      <th>Rollno</th>
      <th>Name</th>
      <th>Year</th>
      <th>Attendance</th>
      <th>reason</th>
      </tr>";

     for ($i=0; $i <$num; $i++)
    {
    $record = mysqli_fetch_array($myData);
    echo "<form action=smanage.php method=post>";
      echo "<tr>";
      echo "<td>" . "<input type='text' name='rollno[]' value='" . $record['rollno'] . "'> </td>";
      echo "<td>" . "<input type='text' name='name[]' value='" . $record['name'] . "'> </td>";
      echo "<td>" . "<input type='text' name='year[]'  value='" . $record['year']  . "'> </td>";
     echo  "<td> "."<select name='attendance[]' >
          <option value='Present' >present</option>
          <option value='Absent' >Absent</option>
      </select>"."</td>";
      echo "<td>". "<textarea cols='15' rows='2' name='reason[]'  placeholder='Enter reason ...'></textarea>" . "</td>" ;
      echo "<td></td>";
      echo "</tr>";


    }
    echo   "<input type='hidden' name='total' value='" . $i-1 . "'>";
    echo"</table>";

    echo "<input type='submit' name='submit' value='save' class='Button3'>";

    echo "</form>";




     };


    mysqli_close($dbcon);


    ?>