How to Insert Multiple Rows in CakePHP 3

How to Insert Multiple Rows in CakePHP 3

CakePHP 3
< 1 Minute Read

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:

[code language=”html”]
$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.

[code language=”html”]
$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.

Jyotibhusan Roul

A Product Specialist, Opensource & SaaS expert for Startups and Enterprise needs. Revenue growth strategist, Market Analyst including Leadership and Mentoring - A proud husband and father!

No Comments

Post A Comment

Exit pop up

Sad to see you leaving early...

From "Aha" to "Oh shit" we are sharing everything on our journey.
Enter your email address to stay up to date with the latest news.
Holler Box