Mysqli can't insert multiple rows

Question

$allgames = file_get_contents("https://steamspy.com/api.php?request=all");
$decodeall = json_decode($allgames, true);
foreach($decodeall as $game) {



$sql = "INSERT INTO games (name)
VALUES ('{$game['name']}')";

}

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

$conn->close();

When i do this only the first row will be added. How do i insert multiple rows?

score 1 · Accepted Answer · answered Oct 23 '16 at 20:31

Just get rid of that multi query thing. Use a prepared statement instead

$stmt = $conn->prepare("INSERT INTO games (name) VALUES (?)");
$stmt->bind_param("s", $name);
foreach($decodeall as $game) {
    $name = $game['name'];
    $stmt->execute();
}
echo "New records created successfully";

Note that your current code with multi_query won't work as intended anyway, even with that silly typo fixed. You will have the result of only first query, having no idea what happened to all others.

@bonitzenator nope, you don't. both cases you mention will go without multiple query just perfectly. That's the point. — Your Common Sense, Mar 21 '17 at 08:24

useyourillusiontoo · Answer 2 · 2016-10-23T20:11:56.447

0

You are overwriting the query each time. Try setting sql to blank then appending it each time in the loop.

Try this:

$sql = array();
foreach($decodeall as $game) {

$sql[] = "INSERT INTO games (name) VALUES ('{$game['name']}')";
}

$sqlInserts = implode(';', $sql);

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

edited Oct 23 '16 at 20:11

answered Oct 23 '16 at 19:56

useyourillusiontoo

1,287
1
10
24

Hmm... i tried that but now it gives me the error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO games (name) VALUES ('Team Fortress 2')INSERT INTO games (name) VALU' at line 1 – Matias Næss Oct 23 '16 at 20:03
Sorry, my bad. The sql insert statements need be be concatenated with a semicolon. I've edited the answer, please try again. – useyourillusiontoo Oct 23 '16 at 20:08
Thank you so much for taking your time helping me out, i really appreciate it. I will try this – Matias Næss Oct 23 '16 at 20:15

Qirel · Answer 3 · 2019-04-19T16:43:04.827

You don't need to perform the query multiple times like that, you can do it all in a single query without multi_query(). You can perform many INSERTs with a single query, like this

// Initialize the query-variable
$sql = "INSERT INTO games (name) VALUES";

// Loop through results and add to the query
foreach ($decodeall as $game) {
    $sql .= " ('".$game['name']."'),";
}

// Remove the last comma with rtrim
$sql = rtrim($sql, ',');

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

This will generate a query resembling

INSERT INTO games (name) VALUES ('One'), ('two'), ('Three')

which will insert the values One, Two and Three into separate rows.

This query will break if your $game['name'] variables contain an apostrophy ', so at the very least you should use $mysqli::real_escape_string(), although a prepared statement takes care of that and prevents SQL injection (so I recommend you go for that instead). See How can I prevent SQL injection in PHP?

Using a prepared statement - the better solution
The preferred method of executing a query is by using a prepared statement.

Fetch all the columns using array_column() and loop the array while calling the execute method until finished.

$stmt = $conn->prepare("INSERT INTO games (name) VALUES (?)");
$stmt->bind_param("s", $name);
foreach (array_column($decode, "name") as $name) {
    $stmt->execute();
}

This seems to work, thanks! But the query breaks because of the apostrophy '. I will try using prepared statement and definitely read up on SQL injection. Thanks for pointing that out. — Matias Næss, Oct 23 '16 at 20:32

Mysqli can't insert multiple rows

3 Answers3