Member Avatar for Oluflourish

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'); ?>
  • 2 Contributors
  • 1 Reply
  • 8K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by cereal

Dani AI

Generated

A safer and faster approach is to skip mysqli_multi_query and use a transaction with a prepared statement. That way you avoid SQL injection risks from concatenating user input, you do not have to manage multiple result sets, and you ensure either all rows are saved or none are if something fails. Also, tie each score row to the student via regformid[] (you already have this hidden field) rather than relying on display order.

Example workflow you can drop in:

  • Validate that all posted arrays have the same length and contain numeric values in an expected range (e.g., 0-100).
  • Begin a transaction.
  • Prepare a single INSERT and execute it once per student.
  • Commit; on any error, rollback and report.

Code sketch:

if (!empty($_POST['regformid'])) {
    $ids  = $_POST['regformid'];
    $###  = $_POST['###'];
    $exe  = $_POST['exe'];
    $ca1  = $_POST['ca1'];
    $ca2  = $_POST['ca2'];
    $exam = $_POST['exam'];

    $n = count($ids);
    if ($n !== count($###) || $n !== count($exe) || $n !== count($ca1) || $n !== count($ca2) || $n !== count($exam)) {
        die('Mismatched input arrays.');
    }

    $conn->begin_transaction();
    $stmt = $conn->prepare('INSERT INTO result_sec (regformid, ###, exe, ca1, ca2, exam) VALUES (?, ?, ?, ?, ?, ?)');
    $stmt->bind_param('iiiiii', $id, $a, $e, $c1, $c2, $ex);

    for ($i = 0; $i < $n; $i++) {
        $id = (int)$ids[$i];
        $a  = (int)$###[$i];
        $e  = (int)$exe[$i];
        $c1 = (int)$ca1[$i];
        $c2 = (int)$ca2[$i];
        $ex = (int)$exam[$i];

        if ($a < 0 || $a > 100 || $e < 0 || $e > 100 /* ...repeat checks... */) {
            $conn->rollback();
            die('Invalid score range.');
        }
        if (!$stmt->execute()) {
            $conn->rollback();
            die('Insert failed: '.$stmt->error);
        }
    }
    $conn->commit();
}

If you want a single SQL statement, you can build a multi-row INSERT ... VALUES (...),(...),... string, but prepared statements + transaction are simpler and robust. See MySQL prepared statements and START TRANSACTION/COMMIT. Also consider renaming any columns with non-alphanumeric characters rather than relying on backticks for identifiers.

Member Avatar for cereal

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.

Edited by cereal
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.