PHP MySQLi Multiple Inserts

Viewed 84 times

I'm wondering if prepared statements work the same as a normal mysql_query with multiple VALUES.

INSERT INTO table (a,b) VALUES ('a','b'), ('c','d');

$sql = $db->prepare('INSERT INTO table (a,b) VALUES (?, ?);

If I use the prepared statement in a loop, is MySQL optimizing the insert in the background to work like it would in the first piece of code there, or is it just like running the first piece of code inside a loop with one value each time ?

Answers

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

Sunday, November 6, 2022

shuhamluvuns-e7688432bcec

$stmt = $mysqli->stmt_init();

if($stmt->prepare("INSERT INTO `activity` (`id`, `name`, `type`) VALUES (?, ?, ?)"))
{
   $stmt->bind_param('iss', $_id, $_name, $_type);
   for($i=0;$i<$limit;$i++)
   {
      $_id = $id[$i];
      $_name = $name[$i];
      $_type = $type[$i];
      $stmt->execute();
   }

}

should do it for you!

Sunday, November 20, 2022

devster101

The answer is I was forgetting to call mysqlistmt::store_result after mysqlistmt::execute().

Once I added $dupSelectStmt->store_result(); I was able to call $dupSelectStmt->num_rows and $portableDB->affected_rows and they both showed the 24 I knew I should be seeing.

Wednesday, September 7, 2022

andrew_homeyer

Looking Back on this question now a year later I can see exactly what i did wrong. in the form i didn't give it any action, i left it at <form action="" method="post">. It should have been <form action="#" method="post">. basic stuff up by a beginner.

Monday, December 19, 2022

franck_dernoncourt

I would do this to minimize chances of error

INSERT INTO production VALUES (120,103,10,0,0), 
(120,107,5,1,0), (120,106,7,2,0), (120, 103,20,0,1);

Wednesday, November 9, 2022

royka

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysqli

PHP MySQLi Multiple Inserts

Answers

php,mysqli