Only one data from array is inserting in the database mysql php

Question

I'm inserting data from multiple inputs and only last row from array is inserting to the database.

if(isset($_POST["submit"]))  

    {

        $fk_app_id = mysqli_insert_id($conn);
        $app_children_name = $_POST['app_children_name'];

        for( $i = 0 ; $i < count($app_children_name); $i++){
            $sql2 = "INSERT INTO applicant_children(fk_app_id, app_children_name, app_children_bday) VALUES ('$fk_app_id', '$app_children_name[$i]', '')";
        }
    }

You would need to execute the INSERT for each iteration of the `for` loop. BUT you should look at using [prepared statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Nigel Ren, Oct 17 '19 at 08:08
That is because you keep overwriting the same variable `$sql2` in the loop, so in the end you're only left with the last `INSERT` statement. — jibsteroos, Oct 17 '19 at 08:11
Your code is vulnerable to SQL injection. You should use prepared statements. — Dharman, Oct 17 '19 at 11:22

score 1 · Answer 1 · answered Oct 17 '19 at 09:45

You have a for loop and in each step of this loop you generate a new String which has the value of a query that you would like to execute. However, inside the for loop you never execute your query, only when the loop finishes. For your reference:

for ($i = 0; $i < 100; $i++) {
    //This happens 100 times
}
//This happens once

Since your insert happens only once and your insert is defined for a single row, the behavior you experience is what one should expect seeing your code. To fix the behavior, you have several possibilities. You could define a composite insert to insert all your records in a single command, as Kris Roofe suggests, or you can actually execute your query inside your for loop in each step. What you do currently is analogous of writing text in a line in a notebook, erasing it, writing in the same line something else and so on and then expecting that all the previously erased texts in that line would be remembered.

score 0 · Answer 2 · answered Oct 17 '19 at 08:16

0

You only process the last element in your foreach loop.

Change it to,

$sql2 = "INSERT INTO applicant_children(fk_app_id, app_children_name, app_children_bday) VALUES ";
for( $i = 0 ; $i < count($app_children_name); $i++){
    $sql2 .= "('$fk_app_id', '$app_children_name[$i]', ''),";
}
trim($sql2,",");

answered Oct 17 '19 at 08:16

LF00

27,015
29
156
295

its shows error and now the arrays doesn't add to the database – Vincent Dabucol Oct 17 '19 at 08:50
What error it shows? – LF00 Oct 17 '19 at 09:17
1

@KrisRoofe the query probably contains some bugs that we cannot determine, not knowing what these values are and what the schema is. Upvoted. – Lajos Arpad Oct 17 '19 at 09:47
Can you show the value of `$sql2` here. – LF00 Oct 17 '19 at 09:49
It's unfair to downvote without reason. – LF00 Oct 17 '19 at 11:51

score -1 · Answer 3 · answered Oct 17 '19 at 09:52

Mysql query should be executed inside the loop. Query executuon is missing in loop.

Here is the solution :-

<?php

if(isset($_POST["submit"]))  

    {
        $link = mysqli_connect("localhost", "root", "", "demo");

        $fk_app_id = mysqli_insert_id($conn);
        $app_children_name = $_POST['app_children_name'];

        for( $i = 0 ; $i < count($app_children_name); $i++){
            $sql2 = "INSERT INTO applicant_children(fk_app_id, app_children_name, app_children_bday) VALUES ('$fk_app_id', '$app_children_name[$i]', '')";
            mysqli_query($link, $sql2);
        }
    }
?>

Only one data from array is inserting in the database mysql php

3 Answers3