We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

How to multi row insert in Phalcon ?

I have got data in as key => value in large array . (20K items). Now I am trying to achieve this : say in $employees

INSERT INTO employees (id, First, Last)
VALUES
    ('Jayesh', 'Singh'),
    ('John', 'Gardner'),
    ('Mohd', 'Tarreq'),
    ('Robert', 'Smith');

How can I do this in phalcon more natively ?

I tried raw sql , build sql query from array giving above but on executing using : 

$success = $this->di->getShared('db')->execute($query);

Now it is seeing first value as column Name, showing error : 

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Jayesh' in 'field list'
#0 [internal function]: PDO->exec('INSERT INTO `em...')

Also, what is the best way to achieve this wehre I can also sanitize each element properly ?



32.2k

Hi @iRickyj you have to use prepared statements. Check this

Good luck



8.8k

Thanks, PDO prepared statement looks completely logical here however, how to do that in terms of Phalcon ? I am seeing https://docs.phalcon.io/en/3.3/db-phql and other related docs, but can't get it right. Any example with native phalcon way ?



145.0k

Best will be still to use PDO. I mean you can just access $di->get('db') and use proper methods from there. Doing it using PHQL will be very unsufficient because it will create model object for each row etc it will be just slow.



8.8k

Alright, that make sense, what about this thought : https://docs.phalcon.io/en/3.3/db-layer#crud ie. raw sql still phalcon way ?



145.0k
edited Jul '18

Yes, it's the exact thing i wrote. Just try to avoid PHQL/models for big insterts.



8.8k

Any thought about using transaction statement provided in Phalcon itself ?

I did this (taken from https://docs.phalcon.io/en/3.3/db-layer#transactions ), I made commit only after all statements.

 try {

        $connection->begin();

        // Loop through $employees array for each statement
        foreach($employees as $key => $value)  {
        $connection->execute('INSERT into `table` VALUES (?, ?'
            [
                $key,
                $value
            ]
            );
   }

        // Create a save point
        $connection->commit();
    } catch (Exception $e) {
        // An error has occurred, release the nested transaction
        $connection->rollback();
    }

I got desired result as well it was almost instant with 20K records. Have not done any benchmarking yet. I am not sure if transaction is better than pdo with multiple insert in single prepared object.



145.0k

Well transaction really doesn't change here a much, do you exepect any error here?



8.8k

Error in the sense , ie. data inconsistency ?

For transaction, as I read around, it says each query is tested but not executed , so I guess could be little more overhead as compared to Single PDO prepared statement for multiple inserts. But for now, with limited knowledge, it seems better than executing each row separately .

So, in nutshell, I feel (On left being better).

Single PDO Prepared for Multiple Inserte is > Transaction with single commit > Regular Multiple Single Insert

Any views ?