mysql:如何用准备好的语句插入多个表 - MySQLi: How to insert into multiple tables with prepared statements

MySQLi: How to insert into multiple tables with prepared statements

我处于需要在查询中插入2个表的情况。我在网上搜索过，找不到解决方案。我要做的是在user表中插入值&同时在profile中插入值。我可以做一个接一个的的方式，但我读到，这是不有效的，被认为是糟糕的编码技术。

当前代码:

$statement = $db->prepare("
    BEGIN;
    INSERT INTO `user`(`username`, `email`, `password_hashed`, `fname`, `lname`, `dob`, `agreement`, `gender`, `access_token`)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
    INSERT INTO `profile_picture`(`owner`) VALUES (LAST_INSERT_ID());
    COMMIT;
");
if($statement) {
    $statement->bind_param("ssssssiss", $username, $email, $hashedPassword, $fname, $lname, $dob, $agreement, $gender, $access_token);
    $statement->execute();
    $statement->close();
    echo "DONE";
    exit();
}
else printf("Error: %s.'n", $db->error);

我在试图复制Frank的答案时遇到了问题。正确的做法是:

try {
    $db->begin_transaction();
  
    $stmt = $db->prepare("INSERT INTO `user`(`username`, `email`, `password_hashed`, `fname`, `lname`, `dob`, `agreement`, `gender`, `access_token`)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param("ssssssiss", $username, $email, $hashedPassword, $fname, $lname, $dob, $agreement, $gender, $access_token);
    $stmt->execute();
    $stmt = $db->prepare("ANOTHER QUERY");
    $stmt->bind_param(...)
    $stmt->execute();
    $db->commit();
} catch(mysqli_sql_exception $ex) {
    //Something went wrong rollback!
    $db->rollback();
    throw $ex->getMessage();
}

第一个语句执行后，您可以使用以下命令从PHP获得对insertID的访问权限:$last_id = $db->lastInsertId();