How do you combine inserting mass values with prepared statements in php

Question

In the old question asked below the top answer describes how to speed up the MySQL insert process. Since this an old question I'm curious on how to do this with prepared statements. I use MySQL with innoDB. I'll paste in an example from the answer here for people who don't want to check the link.

Here I'll describe 3 methods for inserting data, ranging from slow to fast:

The following is extremely slow if you have many rows to insert:
INSERT INTO mytable (id,name) VALUES (1,'Wouter');
INSERT INTO mytable (id,name) VALUES (2,'Wouter');
INSERT INTO mytable (id,name) VALUES (3,'Wouter');
This is already a lot faster:
INSERT INTO mytable (id, name) VALUES
  (1, 'Wouter'),
  (2, 'Wouter'),
  (3, 'Wouter');
And this is usually the fastest:

Have CSV file that looks like this:
1,Wouter
2,Wouter
3,Wouter
And then run something like
LOAD DATA FROM INFILE 'c:/temp.csv' INTO TABLE mytable

old question

This is obvious an old answer. I want to know how to do this with prepared statements in a way that will insert as fast as possible, but let's go one step at a time. First I want to know how to do this with a prepared statements. Any examples that can be given will be much appreciated. I will reply or edit this question with any extra information that you may need.

I'm using mysqli_ *Edited for Rick James's question.

I'm going to do my best and make an educated guess on how this can be done. Please correct me if I'm wrong.

I think I can convert

INSERT INTO mytable (id, name) VALUES
  (1, 'Wouter'),
  (2, 'Wouter'),
  (3, 'Wouter');

into

$bindParamsToBuild = '($myArray[key], ?,?),'
for (i = 0; i < count($myArray); i++)
{
 if (i === count($myArray))
 {
  $bindParamsToBuild += '($myArray[key], ?,?);';
 }
 else
 {
  $bindParamsToBuild += '($myArray[key], ?,?),';
 }
}
"INSERT INTO mytable (id, name) VALUES".$bindParamsToBuild;

"As fast as possible" would still be the `LOAD DATA FROM` method. — mario, Nov 03 '18 at 06:54
Possible duplicate of [insert multiple rows via a php array into mysql](//stackoverflow.com/q/779986) (3rd answer), [Bulk Parameterized Inserts](//stackoverflow.com/q/4659317), [The most efficient way to insert data into db? Prepared statements, bulk loading or multiple insert statement?](//stackoverflow.com/q/9827651), [PDO Prepared Inserts multiple rows in single query](//stackoverflow.com/q/1176352) — mario, Nov 03 '18 at 07:00
You guys are missing the point as I'm trying to figure out how to put this inside of a prepared statement. Please give me an example of how this would go inside a prepared statement. — Drew, Nov 04 '18 at 01:36

Rick James · Answer 1 · 2018-11-04T02:09:06.290

Are you using PDO or mysqli?

If using mysqli, use real_escape_string() separately on each item in the list. It is a bit tedious but can be made easier by gradually building the insert string. I suggest not putting more than 100 items into a single INSERT.

https://stackoverflow.com/a/780046/1766831 is a pretty good discussion of the details. (But be sure to use mysqli_*, not mysql_*.)

Plan A: Construct the query as discussed in some of those links. Use real_escape_string and PHP string operations instead of "prepare".

Plan B: Build a string with lots of question mark. Simultaneously, build an array with the values to put into those question marks. Then do a single "prepare" to finish the multi-row INSERT statement.

I'm using mysqli_ however you didn't explain how to put this into a prepared statement like I asked above. — Drew, Nov 03 '18 at 23:29

How do you combine inserting mass values with prepared statements in php

1 Answers1