php mysqli_multi_query() stops inserting after first query

Question

I'm trying to insert multiple rows into the same table using a mysqli_multi_query function, but it only executes the first query. I have tried adding the values to the end of the first query separated by a comma as well, but nothing seems to work. Any suggestions?

I've switched to prepared statements but still only the first result is inserted. Am I missing something?

$DBConnect = mysqli_connect("localhost", "root", "", "getpressed");

if ($DBConnect->connect_error) {
die("Connection failed: " . $DBConnect->connect_error);

}

$stmt = $DBConnect->prepare("INSERT INTO orderdetails (orderID, productID, quantity) VALUES (?, ?, ?)");
$stmt->bind_param("iii", $orderID, $productID, $quantity);

$orderID = $orderID;
$productID = 1;
$quantity = $sportShirtQuantity;
$stmt->execute();

$orderID = $orderID;
$productID = 2;
$quantity = $sportCoatQuantity;
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$DBConnect->close();

Please, **DON'T** use this function. It's inherently hazardous. Use [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) with placeholder values and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. You can do this as a singular `INSERT` with two sets of values, or one prepared `INSERT` that's executed twice with different bindings. Either way is significantly better than what you have here. — tadman, Jul 22 '16 at 17:57
Out of all dumb answers that are provided so far, the only and I mean ONLY proper way is what @tadman wrote. It's faster, it's trivial, it's resistant to `max_allowed_packet`, it's safe. Any other solution, whatever someone might tell you, is horse manure compared to prepared statements. — N.B., Jul 22 '16 at 18:07

score 1 · Accepted Answer · answered Jul 22 '16 at 19:09

1

I had a primary key index on orderID that wouldn't allow me to insert multiple rows with the same orderID. I'm an idiot. Thank you all for your help. It does work much better with prepared statements as suggested by tadman.

answered Jul 22 '16 at 19:09

kurt

17
5

Aleksandar Đokić · Answer 2 · 2016-07-22T18:43:51.510

I changed your code a bit

$mysqli = new mysqli("localhost", "root", "", "getpressed");

if ($mysqli->connect_errno) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "INSERT INTO orderdetails (orderID, productID, quantity) VALUES ('".$orderID."', 1, '".$sportShirtQuantity."');";
$sql .= "INSERT INTO orderdetails (orderID, productID, quantity) VALUES ('".$orderID."', 2, '".$sportCoatQuantity."');";

if ($mysqli->multi_query($sql))) {
    echo "New records created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($mysqli);
}
do {
    if ($res = $mysqli->store_result()) {
        var_dump($res->fetch_all(MYSQLI_ASSOC));
        $res->free();
    }
} while ($mysqli->more_results() && $mysqli->next_result());

I also highly recommend you to use PDO prepared statements in future.

Why should the OP "try this"? A ***good answer*** will always have an explanation of what was done and why it was done in such a manner, not only for the OP but for future visitors to SO. — Jay Blanchard, Jul 22 '16 at 18:14

score -3 · Answer 3 · answered Jul 22 '16 at 17:49

-3

Remove the semicolon off of the last statement. The documentation notes that the semicolon for this method is used to concatenate statements, not end them.

Read the documentation here: Link

$sql = "INSERT INTO orderdetails (orderID, productID, quantity) VALUES ('".$orderID."', 1, '".$sportShirtQuantity."');";
$sql .= "INSERT INTO orderdetails (orderID, productID, quantity) VALUES ('".$orderID."', 2, '".$sportCoatQuantity."')";

answered Jul 22 '16 at 17:49

Groovey

1
1

tried that, still no dice. Only inserts the first line. – kurt Jul 22 '16 at 17:57
Also, look at your code, you are mixing procedural PHP with O-O PHP, try to stick with just one convention, as they are not compatible. – Groovey Jul 22 '16 at 18:06

php mysqli_multi_query() stops inserting after first query

3 Answers3