5,937 total views, 4 views today

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 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 binded 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 above tips useful? Feel free to drop in your suggestion…

Please visit Andolasoft’s CakePHP Service to know more.