php - $mysqli->准备 SQL 事务

Question

我对 SQL 事务还很陌生，并尝试执行以下语句，但遗憾的是它不起作用...

$stmt = $mysqli->prepare("

BEGIN;

INSERT INTO groups (group_name, group_desc, user_id_fk) VALUES ("'.$groupName.'","'.$groupDesc.'","'.$user_id.'");

INSERT INTO group_users (group_id_fk, user_id_fk) VALUES (LAST_INSERT_ID(), "'.$username.'");

COMMIT;

") or trigger_error($mysqli->error, E_USER_ERROR);

$stmt->execute();

$stmt->close();

这是否可能是我在这里尝试的，还是完全错误的？

感谢您的每一个回复，谢谢!

Answer 1

您使用prepare()的方式是错误的。如果直接在查询中添加变量，那么使用prepare() 绝对没有意义。

这是执行查询的方式:

$mysqli->query("BEGIN");

$sql = "INSERT INTO groups (group_name, group_desc, user_id_fk) VALUES (?,?,?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("ssi",$groupName,$groupDesc,$user_id);
$stmt->execute();

$sql = "INSERT INTO group_users (group_id_fk, user_id_fk) VALUES (LAST_INSERT_ID(), ?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("s",$username);
$stmt->execute();

$mysqli->query("COMMIT");