How To Insert Multiple Rows In CakePHP 3

Social sharing

One of my many issues with CakePHP 2 was lack of multi-row inserts. Cake2 allows saving multiple rows through a single call via saveMany but the issue is with a bunch of single row inserts which can be really slow down when inserting large data; unlike CakePHP 3. Here is what I did to fix this:

$sql = "INSERT INTO `people` (`name`, `title`) VALUES ";
foreach ($people as $person){
       list ($name, $title) = $person;
       $sql.= "('$name','$title'),";
}
$this->query (substr ($sql, 0,-1));

This sucks since I’m not using any framework and I’m also not using PDO bound parameters.

Cake3 has the fixes with the new Query Builder ORM.

$peopleTable = TableRegistry::get ('People');
$oQuery = $peopleTable->query ();
foreach ($people as $person) {
    $oQuery->insert (['name','title'])
        ->values ($person); // person array contains name and title
}
$oQuery->execute ();

The key here is not to issue execute () until after you’ve added all your data via insert (). This will return an instance of the PDO Statement object which inherits all sorts of valuable methods including count () which would give you the total number of rows inserted and errorInfo () for query errors.

Did you find the above tips useful? Feel free to drop in your suggestion…

Please visit Andolasoft’s CakePHP Service to know more.