PDO使用FK数据插入两个表中

Question

I have two tables

CREATE TABLE angos 
    (
        id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
        angos varchar(255) not null, 
        grammar varchar(3) not null, 
        updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        CONSTRAINT unique_input UNIQUE (angos)
    ) engine=InnoDB;

CREATE TABLE english 
    (
        id int primary key,
        angos_id int,
        english varchar(255),
        grammar_note varchar(500),
        CONSTRAINT fk_angos_source FOREIGN KEY (angos_id) REFERENCES angos(id)
    ) engine=InnoDB;

...and some csv data that I have stored in a php array. I'm trying to insert the array data into two mysql tables, one of which has a fk of the other. I'm using php PDO for the insert. This is what I have...

$sql = <<<'EOD'

BEGIN;

INSERT INTO angos
(angos, grammar)
VALUES
(:angos, :grammar);

INSERT INTO english
(angos_id, english)
VALUES
(:angos_id, :english);

COMMIT;

EOD;

try 
{
    $q = $handler->prepare($sql);

    foreach($csv as $value)
    {
        $q->execute(
            array(
                ':angos' => $value[0], 
                ':grammar' => 'con', 
                ':english' => $value[1], 
                ':angos_id' => 'LAST_INSERT_ID()'
            )
        );
    }
} 
catch(PDOException $e) 
{ 
    die( "Error: 
" . $e->getMessage() ); 
}

...the data fails to insert and the error message I'm currently getting is

Error: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). 
Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

What's happening?

Answer 1

By default you can only execute one query at once with PDOStatement::execute() method. If you like to use transactions have a look at the PDO::beginTransaction(), PDO::commit() and PDO::rollBack() methods of PDO. And create two separated queries.

But as you're told by PHP you can set the attribute PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to run multiple queries in one prepared statement. But you could run intro trouble if you change the PDO driver e.g. from MySQL to PostgreSQL. Because PostgreSQL does not support the attribute mentioned above. So I suggest that you only use one statement per prepared statement. That makes the prepared statements also more reusable than a specfic construct of statements.