Insert multiple values

MySQL database
#1

Hi I’m new to coding and I want to learn more. It has taken me weeks to understand very simple code. I’ve been able to use what I’ve seen in tutorials, edit it and add things to it.
But I’m stuck and I would like some clue or some material to read to understand more, I want to try to solve it myself.

This is my form.html

<!DOCTYPE html>
<html>
<head>
	<title>Data</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
</head>
    
<body>
  <form action="insert.php" method="post">
         <h1>Form</h1><br>
         <input type="button" id="addbutton" value="Add User"><br><br>    
         <table id="usertable" style="border:1px solid black;">
        <tr>
            <td><b>Manager:</b></td>
            <td> <input type="text" name="who"  required></td>
            <td>&nbsp;</td>
        </tr>
         <tr><td><b>Name : </b></td>                      
            <td><b>Email : </b></td>       
            <td><b>Area : </b></td>
            <td><b>Rol :</b></td></tr>
         <tr><td><input type="text" name="name[]" required></td>
            <td><input type="text" name="email[]" required></td>
            <td><input type="text" name="area[]" required></td>   
            <td><select name="rol[]">
              <option value="userrol1">User rol 1</option>
              <option value="userrol2">User rol 2</option>
              <option value="userrol3">User rol 3</option>
              <option value="userrol4">User rol 4</option>
             </select>        
            </td>
            </tr>
      </table><br>
      <input type="submit" name="submit" value="Submit Data"/>
  </form>
    
<script>
    var i = 1;
    $("#addbutton").click(function () {
        $("#usertable").append('<tr>'+
            '<td><input type="text" name="name[]" required></td>'+
            '<td><input type="text" name="email[]" required></td>'+
             '<td><input type="text" name="area[]" required></td> '+
              '<td><select name="rol[]"> '+
               '<option value="userrol1">User rol 1</option> '+
               '<option value="userrol2">User rol 2</option> '+
               '<option value="userrol3">User rol 3</option> '+
               '<option value="userrol4">User rol 4</option></select></td> '+
            '<td><button type="button" class="removebutton" title="Remove this row">X</button></td></tr>').find("input").each(function () {
        });
        i++;
    });

    $(document).on('click', 'button.removebutton', function () {
        $(this).closest('tr').remove();
        return false;
    });    
    </script>
    
</body>    
</html>

my insert.php

<?php

include 'connect.php';

if(isset($_POST['submit'])){
$who = $_POST['who'];
$name = $_POST['name'];
$email = $_POST['email'];
$area = $_POST['area'];
$rol = $_POST['rol'];
    
 for($i=0;$i<count($name);$i++)
 {
  if($name[$i]!="" && $email[$i]!="" && $area[$i]!="" && $rol[$i]!="")
  { 
    
$sql = "INSERT INTO save (who,name,email,area,rol)
VALUES ('$who','$name[$i]','$email[$i]','$area[$i]','$rol[$i]')";
    
	 if (mysqli_query($conn, $sql)) {
		echo "Data Saved";
	 } else {
		echo "Error: " . $sql . "
" . mysqli_error($conn);
	 }
	 mysqli_close($conn);
  }
 }
}

?>

 for($i=0;$i<count($name);$i++)

I dont understand what this does, i copied it from some example.
Only first data gets stored, if i add more i get and error:

Fatal error : Uncaught Error: mysqli object is already closed in C:\xampp\htdocs\form\insert.php:20 Stack trace: #0 C:\xampp\htdocs\form\insert.php(20): mysqli_query(Object(mysqli), ‘INSERT INTO sav…’) #1 {main} thrown in C:\xampp\htdocs\form\insert.php on line 20

My table is divided into:
Id:
Who: Only store this one once.
Name:
Email:
Area:
Rol:

Thanks.

#2

This is a for(){} loop - https://www.php.net/manual/en/control-structures.for.php It is looping from 0 to one less then the count of number of elements in the name array, to access each corresponding value in the name, email, area, and rol arrays of submitted data.

The reason you are getting an error about the mysqli connection being closed after the first pass through the loop is because your code is closing the connection inside the loop. In general, there’s no need to close database connections in your code since php will automatically close them when your script ends.

Next, do NOT put external, unknown, dynamic values directly into an sql query statement. This will allow any sql special characters in a value to break the sql query syntax, which is how sql injection is accomplished. Instead, use a prepared query and supply the values when the query gets executed. This would also be a good time to switch to the much simpler and more consistent PDO database extension. A prepare query will also provide a small performance gain (~5%) when executing the same query with different data values. You would prepare the query once before the start of any looping, then provide each set of data values when you execute the prepared query inside the loop.

1 Like
#3

Thanks a lot for your answer, it finally works.
Im going to check what you said about inserting dynamic values and PDO. Thanks agains for your answer, very kind.

Sponsor our Newsletter | Privacy Policy | Terms of Service