Mysqli multiple row insert, simple multi insert query

Question

How do I insert this query with mysqli?...

INSERT INTO table (field1, field2, field3) VALUES ('value', 'value', 'value'), ('value', 'value', 'value'), ('value', 'value', 'value');

Normally in mysql this query is straight forward and will insert 3 rows, how do I do this in mysqli without using a prepared statement or maybe using a prepared statement but without getting too complicated?. I just wish to know if there is a way to execute such query without doing extra funky stuff in PHP.

In essence, I have some extracted data that has around 10 rows per insert (and also needs multiple inserts in addition to having multiple rows), and this is what I need it for. I only wish to do this with a query as I have normally done it with mysql, and not add multiple insert as one per each row.

mysqli is just a set of functions to send your query in mysql. — Your Common Sense, Oct 22 '13 at 08:14
Store the SQL statement in a variable and pass that to the mysqli_query as a parameter; — Sajuna Fernando, Oct 22 '13 at 08:15
Sajuna unfortunately it can't be done that way. Mysqli is not the same as mysql. — user1675155, Oct 22 '13 at 08:42
Tried using mysql while there is a mysqli connection open, didn't work!. — user1675155, Oct 22 '13 at 17:43

score 23 · Answer 1 · edited Jan 02 '23 at 14:25

The mysqli class provides a number of different ways of accomplishing your inserts, each with its own benefits. Certainly, one of them should fit your needs.

The following examples assume that your unspecified "extracted data" is stored in an array of arrays: $bigArray[0...datasetsize][0...2].

The mysqli database is assumed to be $db.

Method 1 - As Simple As Possible

If you want to use a prepared statement and parameter binding, a first effort might look like the following. While not optimal, the statement is only prepared once. However, the variables are bound for each insert, which is wasteful (but simple). Since inserts are not bundled, the example loops over 10.

$statement = $db->prepare("INSERT INTO testTable (fieldA, fieldB, fieldC) VALUES (?,?,?)");
for ($i = 0; $i < 10; ++$i)
{
    $statement->bind_param("iii",$bigArray[$i][0],$bigArray[$i][1],$bigArray[$i][2]);
    $statement->execute();
}

Method 2 - Optimized

Prepared statements and multiple inserts combined enable performance which is nearly identical to the raw insert queries of Method 1. Actual results will vary depending on your setup, but a quick test on my system with both a local and a remote database showed performance a few percentage points faster with the optimized method, increasing a few points more if data in Method 1 needs to be escaped.

The following uses call_user_func_array, but you could avoid that if you know how many inserts you want to bundle each time and build call bind_param directly. That would further increase performance slightly.

$sql = "INSERT INTO testTable (fieldA,fieldB,fieldC) VALUES (?,?,?)".str_repeat(",(?,?,?)",count($bigArray)-1);
$statement = $db->prepare($sql);

// This is the type string used by statement::bind_param. 
// s will be good for any type.
$types = str_repeat("s", count($bigArray) * count($bigArray[0]));

$values = array_merge(...$bigArray);
$statement->bind_param($types, ...$values);
$statement->execute();

This is exactly what I was trying to AVOID, like i said, I wanted to just pass the query, WITHOUT doing extra funky stuff in PHP, and is is a extreme example of funky stuff, in other words, I wanted to pass query as much as possible on its raw form. — user1675155, Oct 04 '16 at 09:20
@fyo but how would you deal with Method1 or Method2, if Data is _not_ stored in an _array_, but in an _JSON_ object? How to slice the hundreds or thousands JSON datasets into pieces of 10 to insert them at once? — Peter, Sep 14 '17 at 19:48

score -1 · Answer 2 · answered Sep 16 '21 at 06:30

$sql = "INSERT INTO `aspnetuserroles` (`UserId`, `RoleId`)
         VALUES ('1', '8811404'), ('1', '1429d')";
$stmt = mysqli_prepare($conn, $sql); 

  if (mysqli_stmt_execute($stmt)) {
       echo '<script language="javascript">';
       echo 'alert("Registeration Sucessfull!")';
       echo '</script>';
     
     } else {
             echo '<script language="javascript">';
             echo 'alert("User Role Registeration Failed!")';
             echo '</script>';
             echo $sql;
                            }

score -3 · Answer 3 · answered Oct 22 '13 at 08:18

-3

Mysqli is not a database of it's own, but just a set of functions to send your query in old mysql.

So, using mysqli you can run any mysql query.

However, in case of dynamically supplied values you cannot avoid "extra funky stuff in PHP" as you are supposed to use prepared statements for that. And, unfortunately, raw mysqli is not that easy with them.

So, to perform such insert you will need to create a query with placeholders first

INSERT INTO table (field1,field2,field3) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?);

then bind all the values using call_user_func_array()
and finally execute;

answered Oct 22 '13 at 08:18

Your Common Sense

156,878
40
214
345

Okay, I guess I can do that. the whole query was actually prepared with php so it would be a long single query, but I guess I have to modify that to fit mysqli. Didn't want to do extra stuff for just an insert but I guess I have to adjust to mysqli. Thanks!, great answer. – user1675155 Oct 22 '13 at 08:40
Found a similar question: http://stackoverflow.com/questions/15575405/mysqli-and-binding-multiple-value-sets-during-insert but still no solution!!!! – user1675155 Oct 22 '13 at 17:45

Mysqli multiple row insert, simple multi insert query

3 Answers3

Linked

Related