I want to create a table with input fields where student records can be inserted. The name of the students are in the first column of the table fetched from the database with a while loop. The other columns contain fields for inputing the student scores. The challenge I'm facing is how to insert the records of all the students in different row of a table called result_sec into the database. I've search for similar post but couldn't get a suitable answer. Below is the code.

<?php require('header.php'); ?>
<?php 
$query_form = sprintf("SELECT * FROM regform LIMIT 2");
$form = mysqli_query($conn, $query_form) or die(mysqli_error($conn));
$formdata = mysqli_fetch_assoc($form);

if(isset($_POST['submit']))
{
$exes = $_POST['exe'];
$asss = $_POST['ass'];
$ca1s = $_POST['ca1'];
$ca2s = $_POST['ca2'];
$exams = $_POST['exam'];

    foreach($exes as $key => $exe)
    {
            $sql = "INSERT INTO result_sec (exe, ass, ca1, ca2, exam) VALUES ('$exe', '$asss[$key]', '$ca1s[$key]', '$ca2s[$key]', '$exams[$key]')";
    }
    $insert = mysqli_multi_query($conn, $sql);
}
?>
<form method="POST">
    <table>
    <thead>
    <tr>
        <th>Name</th>
        <th>Ass.</th>
        <th>Exe.</th>        
        <th>1st C.A.</th>
        <th>2nd C.A.</th>
        <th>Exam</th>

    </tr>
    </thead>

    <tbody>
     <?php do { ?> 
      <tr>
          <td><?php echo $formdata['surname']." ".$formdata['firstname']; ?></td>
          <td><input name="ass[]" size="1px"/></td>
          <td><input name="exe[]" size="1px" /></td>
          <td><input name="ca1[]" size="1px" /></td>
          <td><input name="ca2[]" size="1px" /></td>
          <td><input name="exam[]" size="1px" /></td>
          <input type="hidden" name="regformid[]" value="<?php echo $formdata['regformid'];?>" />
      </tr>
     <?php } while ($formdata = mysqli_fetch_assoc($form)); ?>
    </tbody>
    </table>
    <button type="submit">Insert Student Record</button>
    </form>
<?php require('footer.php'); ?>

Try to add backticks to the ### column. MySQL for unquoted column names expects:

  1. basic Latin letters, digits 0-9, dollar, underscore
  2. Unicode Extended: U+0080 .. U+FFFF

The character # is U+0023 which falls in the quoted range:

So, write:

$sql = "INSERT INTO result_sec (exe, `###`, ca1, ca2, exam) VALUES ('$exe', '$asss[$key]', '$ca1s[$key]', '$ca2s[$key]', '$exams[$key]')";

Also, the $sql variable is rewritten after each loop, instead you have to append the queries, so make few small changes:

  1. Initialize $sql outsite the loop, otherwise you get a notice for undefined variable
  2. Add a dot in front of the assignment operator =
  3. add a semi-colon at the end of the query

So, write:

$sql = '';
foreach($exes as $key => $exe)
{
    $sql .= "INSERT INTO result_sec (exe, `###`, ca1, ca2, exam) VALUES ('$exe', '$asss[$key]', '$ca1s[$key]', '$ca2s[$key]', '$exams[$key]'); ";

Then it should work.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.