如何用一个查询php更新多个表

Question

if($_SERVER['REQUEST_METHOD'] == 'POST'){
$content = file_get_contents('php://input');
$user = json_decode($content, true);
$id_a = $user['id_a'];
$id_b = $user['id_b'];
$aName = $user['aName'];
$bName = $user['bName'];

$sql = "
BEGIN TRANSACTION;
UPDATE `tb1` 
SET `aName` = '$aName' 
WHERE `id_a` = '$id_a';

UPDATE `tb2` 
SET `bName` = '$nName' 
WHERE `id_b` = '$id_b';

COMMIT
";  
$result = $conn->query($sql);
if($result){
echo json_encode(['status'=>'success','message'=>'Edited successfully']);
}
else{
echo json_encode(['status'=>'error','message'=>'An error occurred editing the information.']);
}
}
else{
echo json_encode(['status'=>'error','message'=>'REQUEST_METHOD Error']);
}
$conn->close();

我需要更新数据多表，但当我使用上面的代码时，它会响应"；编辑成功"；但数据库中的数据并没有改变任何

但当更新单个表时，它可以

Answer 1

不需要事务，因为您的PDO似乎不接受事务。

像这样的东西应该在你举的例子中起作用：

UPDATE tb1, tb2
SET tb1.aName = $aName,
tb2.bName = $bName
WHERE
tb1.id_a = $id_a
AND tb2.id_b = $id_b;