Multiple data combined

[premento]

Is it possible to combine multiple arrays and insert in one INSERT statement? If so, how to do the combining of arrays?

[ricwein]

It's currently not implemented, but you can use transactions to improve overall insert-performance for multiple statements. Also see Issue #483 and #208.

[premento]

Any plans to implement? Highly useful :)

[ghost]

why not simply using array_merge($array1, $array2) right before calling insert?

[ricwein]

I guess, @premento means inserting several datasets at once into a database like:

$data = [
  ['username1', $id1, 'password_hash1'],
  ['username2', $id2, 'password_hash2'],
];
$db->insert('users', $data);

which is currently not supported. An array_merge() doesn't help with that at all.

[premento]

yes, I was looking into this. Is there a sample to see how transactions could be used for the same?

[ricwein]

You can use transaction like described in the Readme. It should work with something like:

$success = true;
$db->startTransaction();

foreach($data as $dataSet) {
   if (!$db->insert ('tableName', $dataSet)) {

     // something went wrong

      $db->rollback();
      $success = false;
      break;
   }
}

if ($success) {
    $db->commit();
}

[ricwein]

If I'll find the time, I'll take a look at implementing a multi-dataSet insertion feature later today, if @avbdr has no problem with that.

[avbdr]

sure, go ahead. Ill glad to merge. We can call that method multiInsert or insertMulti(). We can optimize that later, but initial performance is not that bad when using transactions

[ricwein]

See merge request #504

[avbdr]

fixed in master

[premento]

Just a doubt, instead of using 10 separate inserts, will there be any advantage in using this new multiInsert? Will the mysql write transactions reduce?

[avbdr]

if you are not doing all inserts in 1 transaction then most likely Richards work will be faster