Insert multiple VALUES into table

Question

I try to insert multiple VALUES into a table using the fat free frameworks sql mapper.

The problem is it only shows that for one VALUE

$db->exec('INSERT INTO mytable VALUES(?,?)',array(1=>5,2=>'Jim'))

As I have a lot of records and need to speed it up I wanted to add multiple

VALUES, as in VALUES(?,?),(?,?),?,?);

But how has the array to look then?

Background. I try to speed up the import this way because i parse big 100k+ csv files and import them.

The `VALUES` keyword should only appear once. See the correct SQL syntax https://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query — xfra35, Sep 06 '19 at 18:15

crafter · Accepted Answer · 2019-12-07T21:23:41.253

The syntax to do that is:

$db->exec("INSERT INTO `table` (`col1`,`col2`) VALUES ('val1','val2'), ('val1','val2'), ('val1', 'val2')");

Definitely you want to use prepared statements, I recommend first to generate string for placeholders

VALUES (:q1, :q2), (:q3, :q4), (:q5, :q6)

and than generate bindings

[
   ':q1' => $data['val1'],
   ':q2' => $data['val2'],
   ':q3' => $data['val3'],
   ':q4' => $data['val4'],
   //...
],

1 Answers1