Search code examples
fat-free-framework

Insert multiple VALUES into table

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

Docs

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.

Solution

  • 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'],
   //...
],