带有插入和获取输出错误的 PDO 事务语句 [英] PDO Transaction statement with insert and fetch output error

问题描述

$tr = $connection->prepare("
        START TRANSACTION;
            INSERT INTO data(name, address, dated) VALUES('Umesh', 'Kathmandu', NOW());
            SET @DataID:= LAST_INSERT_ID();
            INSERT INTO activity(data_id, activity, dated) VALUES(@DataID, 'Test Implemented', NOW() );
           SET @ActID:= LAST_INSERT_ID();
           SELECT @DataID as data_id, @ActID as activity_id;
        COMMIT;
");

$tr->execute();

$os = $tr->fetch(PDO::FETCH_ASSOC);

print_r($os);

我只是想同时输出 last_insert_id() 作为输出，查询运行良好，并且我成功地将数据插入到两个表中.

I am just trying to output both last_insert_id() as output, the query runs well, and I got data inserted into both tables, successfully.

但它在行 $os = $tr->fetch(PDO::FETCH_ASSOC);

推荐答案

永远不要使用单个调用执行多个语句.如果您使用本机准备，它将无法正常工作，并且很难做到正确.

You should never execute multiple statements using a single call. It won't work if you use native prepares and it is difficult to get it right.

将其拆分为多个语句并使用 PDO 的函数获取最后插入的 ID.

Split it up into multiple statements and use PDO's functions to get the last inserted ID.

$connection->beginTransaction();

$tr = $connection->prepare("INSERT INTO data(name, address, dated) VALUES('Umesh', 'Kathmandu', NOW())");
$tr->execute();
$DataID = $connection->lastInsertId();

$tr = $connection->prepare("INSERT INTO activity(data_id, activity, dated) VALUES(:DataID, 'Test Implemented', NOW() )");
$tr->execute(['DataID' => $DataID]);
$ActID = $connection->lastInsertId();

$connection->commit();


print_r($DataID, $ActID);

这篇关于带有插入和获取输出错误的 PDO 事务语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！