Insert array values into mysql through loop

Question

I'm reading through xml files with simplexml_load_file. I'm trying to loop through 3 different xml files and then insert the value into mysql database. It's only inserting the last array. Should I be using a while loop?

Here is my code:

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 


// Load/read xml files
$id1 = simplexml_load_file('1.xml');
// echo $id1->video[0];

$id2 = simplexml_load_file('2.xml');
// echo $id2->video[0];

$id3 = simplexml_load_file('2.xml');
// echo $id3->video[0];



// make into an array list
$arr = array($id1->video[0], $id2->video[0], $id3->video[0]);

// loop through array and insert into sql database
foreach ($arr as $value) {
    $value = $value;
    $sql = "INSERT INTO videos (username, src, type, position)
    VALUES ('admin', '".$value."', 'vide', '0')";
}

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

You should perform the query inside the loop, otherwise you're taking only the last index of the array because the content of $sql changes in every iteration — Osakr
– Osakr, Commented Jan 4, 2019 at 17:02
You overwrite $sql on every iteration. You also are open to SQL injections. Parameterize. — user3783243
– user3783243, Commented Jan 4, 2019 at 17:02
@user3783243 thank you for your feedback! I will look into that. — code
– code, Commented Jan 4, 2019 at 17:10
Using a prepared statement will also improve the performance of the multiple inserts. Prepare once before the loop, then bind the value and execute inside the loop. — Don't Panic
– Don't Panic, Commented Jan 4, 2019 at 17:35

Osakr · Accepted Answer · 2019-01-04 17:41:18Z

Every time you iterate in the foreach you're overwritting the $sql variable. Since you're performing the query after the for loop, only the last item of the array it's being inserted into the DB.

To solve it you should put the query inside the for loop:

foreach ($arr as $value) {
    $sql = "INSERT INTO videos (username, src, type, position)
    VALUES ('admin', '".$value."', 'vide', '0')";

    if ($conn->query($sql)) {
       echo "New record created successfully";
    } else {
       echo "Error: " . $sql . "<br>" . $conn->error;
    }
 }

However you must take care with SQL Injection, I recommend you to take a look at PDO. http://php.net/manual/en/ref.pdo-mysql.php

Let me transform this into a PDO example:

$sql = "INSERT INTO videos (username, src, type, position ) VALUES ('admin', :value, 'vide', '0')";
if ($stmt = $PDO->prepare($sql)) {
    foreach($arr as $value) {
        $binds['value'] = $value;
        if ($stmt->execute($binds)) {
            echo "Row inserted successfully"
        } 
    }
}

$PDO would be the variable where the conexion is stored.

Collectives™ on Stack Overflow

Insert array values into mysql through loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related