insert multiple same name input to database

Question

I know that this question has been already asked here, but I went through 20+ and couldn't get below code to work

<form action="" method="post" >
 <td><input name="field1[]" value="" ></td>
 <td><input name="field2[]" value="" ></td>
 <td><input name="field3[]" value="" ></td>
 <td><input name="field1[]" value=""></td> 
 <td><input name="field2[]" value=""></td>
 <td><input name="field3[]" value=""></td>
 <td><input name="field1[]" value=""></td>
 <td><input name="field2[]" value=""></td>
 <td><input name="field3[]" value=""></td>
 <button type="submit" id="submit" name="submit" > Go </button>
</form>





<?php
if(isset($_POST["submit"])){

    foreach($_POST['field1'] as $key => $value) {

        /* Attempt MySQL server connection. Assuming you are running MySQL
        server with default setting (user 'root' with no password) */
        $link = mysqli_connect("localhost", "dbuser", "dbpass", "dbname");

        // Check connection
        if($link === false){
            die("ERROR: Could not connect. " . mysqli_connect_error());
        }

        // Prepare an insert statement
        $sql = "INSERT INTO test (field1, field2, field3) VALUES (?, ?, ?)";

        if($stmt = mysqli_prepare($link, $sql)){
            // Bind variables to the prepared statement as parameters
            mysqli_stmt_bind_param($stmt, "sss", $field1, $field2, $field3);

            // Set parameters
            $field1 = $_REQUEST['field1'];
            $field2 = $_REQUEST['field2'];
            $field3 = $_REQUEST['field3'];

            // Attempt to execute the prepared statement
            if(mysqli_stmt_execute($stmt)){
                echo "Records inserted successfully.";
            } else{
                echo "ERROR: Could not execute query: $sql. " . mysqli_error($link);
            }
        } else{
            echo "ERROR: Could not prepare query: $sql. " . mysqli_error($link);
        }

        // Close statement
        mysqli_stmt_close($stmt);

        // Close connection
        mysqli_close($link);    
    }       
}
?>

My problem is clear - how to store this array to database. I am doing something wrong because my DB stores Array word instead of value. Any help would be massively appreciated

Answer 1

You are referencing $_POST['field1'] which is an array, hence your results.

You need to reference the items of the array i.e. $_POST['field1'][0] and so on.

You also dont need to prepare the query inside the foreach. Do it once outside the loop and save n* the round trips to the server and n* the query compilations.

<?php
if(isset($_POST["submit"])){

    /* Attempt MySQL server connection. Assuming you are running MySQL
        server with default setting (user 'root' with no password) */
    $link = mysqli_connect("localhost", "dbuser", "dbpass", "dbname");

    // Check connection
    if($link === false){
        die("ERROR: Could not connect. " . mysqli_connect_error());
    }

    // Prepare an insert statement
    $sql = "INSERT INTO test (field1, field2, field3) VALUES (?, ?, ?)";
    $stmt = mysqli_prepare($link, $sql);

    // Bind variables to the prepared statement as parameters
    mysqli_stmt_bind_param($stmt, "sss", $field1, $field2, $field3);

    foreach($_POST['field1'] as $key => $value) {

        // Set parameters
        $field1 = $_POST['field1'][$key];
        $field2 = $_POST['field2'][$key];
        $field3 = $_POST['field3'][$key];

        // Attempt to execute the prepared statement
        if(mysqli_stmt_execute($stmt)){
            echo "Records inserted successfully.";
        } else{
            echo "ERROR: Could not execute query: $sql. " . mysqli_error($link);
        }
    }        
}
?>