Mysqli and binding multiple value sets during inse

Question

Hoping someone can give me some insight here.

When having to insert multiple rows into a table at once, I've used sql that looks something like this:

INSERT INTO some_names (firstName, lastName) VALUES ('Joe', 'Smith'),('Fred','Sampson'),('Lisa','Pearce')

As you can see I'm inserting three rows with one statement. The reason I do this is that I believe it is more efficient than executing three distinct statements to insert the rows.

So my question is this: how do I do this if I want to be able to bind my values to a statement? Unfortunately I've been looking all over the web and I can only find example of single statements in the form of:

$stmt = $mysqli->prepare("INSERT INTO some_names (firstName, lastName) VALUES (?, ?)");
$stmt->bind_param('ss', $firstName, $lastName);
$firstName = "Joe";
$lastName = "Smith";
$stmt->execute();
$firstName = "Fred";
$lastName = "Sampson";
$stmt->execute();

It seems that this would be the equivalent of doing separate INSERT statements and seems to be less efficient.

So my question is: Is there any way to bind in a multi-insert statement? Please educate me here! Thanks

Answer 1

Simple:

$stmt = $mysqli->prepare("INSERT INTO some_names (firstName, lastName) VALUES (?, ?),(?,?),(?,?)")
$stmt->bind_param('ssssss', 'Joe', 'Smith','Fred','Sampson','Lisa','Pearce');

Answer 2

You are looking at this the wrong way.

You would pretty much never do what you have shown above, it would almost always be done in a loop. And even if you dynamically constructed (?, ?), (?, ?), (?, ?) you would still have to loop to build the query string and bind the data (which would get even more complicated because of MySQLi's ridiculous insistance on by-ref bindings) so it wouldn't gain you anything in terms of work that needs to be done by PHP - you just moved the loop - and it would lose you a lot in terms of readability.

It does have a gain in round trips to the database (assuming you are always inserting more than one row) - but this would only make a meaningful difference when inserting hundreds or thousands or rows, in which case you are most likely performing some kind of import operation, in which case the extra couple of seconds probably don't matter (it won't be e.g. slowing a page load).

Answer 3

It seems that this would be the equivalent of doing separate INSERT statements and seems to be less efficient.

No, it’s not less efficient – because a statement is prepared only once (to figure out what it is supposed to do), and after that is done only the data is send to the database afterwards.