php inserts only last array record into sql

Question

I have working code, which inserts data into csv file. Here is a part of it:

if (isset($_POST['array'])) {
        foreach ($_POST['array'] as $loop) {
            $txt = $txt . $loop['name'] . ";" . $loop['email'];
            $txt .="\n";
        }
    }

Instead of csv, i would like it to insert data into mysql database, so i have simply changed the code to:

    if (isset($_POST['array'])) {
        foreach ($_POST['array'] as $loop) {

            $sql = "insert into persons (Name, Email)
                    values ('" . $loop['name'] . "', '" . $loop['email'] . "')";     
        }
    }

Only the last record is saved into the persons table. There are no errors, but all the previous records except last are not inserted. Why?

Also, you do want to [use prepared statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). — GMB, Dec 27 '19 at 10:55
You're overwriting `$sql` (instead of concatenating) on every iteration of the loop. Last one wins — brombeer, Dec 27 '19 at 10:56
Use small case for table column name `Name`, `Email` to `name`,`email` — Niklesh Raut, Dec 27 '19 at 11:04

Mike Foxtech · Answer 1 · 2019-12-27T22:33:25.590

3

Better way is only one time create insert

if (isset($_POST['array'])) {
    $values = [];
    $sql = "insert into persons (Name, Email) values ";

    foreach ($_POST['array'] as $loop) {
        $values[] = "('" . $conn->real_escape_string($loop['name']) . "', '" . $conn->real_escape_string($loop['email']) . "')";     
    }

    if (!empty($values)) {
        $conn->query($sql . implode(', ', $values));
    }
}

edited Dec 27 '19 at 22:33

answered Dec 27 '19 at 11:06

Mike Foxtech

1,633
1
6
7

1

Note that this is still very much prone to SQL injection attacks. – Jeto Dec 27 '19 at 12:13
**Warning:** Your code could still be open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and you should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Dec 28 '19 at 14:04

score 2 · Accepted Answer · answered Dec 27 '19 at 12:33

The reason why it doesn't work is because you never execute your SQL query anywhere.

To execute the query you should first prepare the statement and then bind the params and execute.

// Your connection to DB
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'username', 'password', 'dbname');
$mysqli->set_charset('utf8mb4'); // always set the charset


if (isset($_POST['array'])) {
    $stmt = $mysqli->prepare('INSERT INTO persons (Name, Email) VALUES (?,?)');
    foreach ($_POST['array'] as $loop) {
        $stmt->bind_param('ss', $loop['name'], $loop['email']);
        $stmt->execute();
    }
}

score 1 · Answer 3 · answered Dec 27 '19 at 10:57

Please execute query inside the loop as given below

...
$conn = mysqli_connect($servername, $username, $password, $dbname);
...

if (isset($_POST['array'])) {
    foreach ($_POST['array'] as $loop) {

        $sql = "insert into persons (Name, Email)
                values ('" . $loop['name'] . "', '" . $loop['email'] . "')";    
        $conn->query($sql);// Query should execute inside loop       

    }
}

score 0 · Answer 4 · edited Aug 04 '21 at 11:19

I had the same issue inserting JSON arrays into MySQL and fixed it by putting the mysqli_stmt->execute() function inside the foreach

// loop through the array
foreach ($data as $row) {
    echo "Data has been Uploaded successfully <br>";
    // get the project details
    $id = $row['id'];
    $name = $row['name'];
    $togglCid = $row['cid'];
    // execute insert query
    mysqli_stmt_execute($sql);
}

This means it executes the code after each loop

score -1 · Answer 5 · answered Dec 27 '19 at 11:00

If you want to execute the query outside the loop Try using as following

 if (isset($_POST['array'])) {
            $sql="";
            foreach ($_POST['array'] as $loop) {

                $sql .= "insert into persons (Name, Email)
                        values ('" . $loop['name'] . "', '" . $loop['email'] . "');\n";     
        }
    }

php inserts only last array record into sql

5 Answers5