php - 同时插入和更新，或在SQL查询中使用事务处理

我有三个问题：

$sql1 = "INSERT INTO bag(bag_id, chara_id, item_id, item_qty)VALUES(NULL,:id,:item_id,'1')";
$sql2 = "UPDATE chara SET chara_gold = chara_gold - :gold WHERE chara_id = :id";
$sql3 = "UPDATE shop SET item_qty = :qty WHERE item_id = :item_id";

有三张桌子：
查拉

+----------+------------+----------------+-------------+------------+----------+----------+-----------+-----------+
| chara_id | chara_name | chara_class_id | chara_level | chara_gold | chara_hp | chara_mp | chara_atk | chara_def |
+----------+------------+----------------+-------------+------------+----------+----------+-----------+-----------+
|        1 | LawrenceX  |              1 |           1 |       1000 |     1000 |     1000 |         7 |         3 |
|        3 | Viscocent  |              2 |           1 |       1000 |      900 |     1100 |         5 |         5 |
|        4 | Piatos     |              1 |           1 |       1000 |     1000 |     1000 |         7 |         3 |
|        5 | Hello      |              1 |           1 |       1000 |     1000 |     1000 |         2 |         8 |
|        6 | Sample     |              3 |           1 |       1000 |     1100 |      900 |         9 |         1 |
|        8 | Sampuro    |              2 |           1 |       1000 |     1500 |      100 |         5 |         5 |
|       12 | fail       |              2 |           1 |       1000 |      900 |      100 |         5 |         5 |
+----------+------------+----------------+-------------+------------+----------+----------+-----------+-----------+

袋

+---------+-----------------+------------+
| item_id | item_name       | item_price |
+---------+-----------------+------------+
|       0 | Halberd         |        400 |
|       1 | Axe             |        200 |
|       2 | Wooden Sword    |        225 |
|       3 | Dagger          |         55 |
|       4 | Bow             |        120 |
|       5 | Helmet          |        155 |
|       6 | Tunic           |         50 |
|       7 | Armour          |        150 |
|       8 | Necklace        |        199 |
|       9 | Studded Leather |        240 |
+---------+-----------------+------------+

商店

+---------+---------+------------+----------+
| shop_id | item_id | item_price | item_qty |
+---------+---------+------------+----------+
|       1 |       1 |        200 |       99 |
|       2 |       2 |        225 |       99 |
|       3 |       3 |         55 |       99 |
|       4 |       4 |        120 |       99 |
|       5 |       5 |        155 |       99 |
|       6 |       6 |         50 |       99 |
|       7 |       7 |        150 |       99 |
|       8 |       8 |        199 |       99 |
+---------+---------+------------+----------+

场景：
我想在包item_id栏中添加一个项目
然后，根据他购买的商品数量（根据商店）扣除chara's id。
然后在charas gold表中将购买项目的item_price减去1。
能在一个查询中完成吗。
我还想用transact。

最佳答案

不能在一个查询中更新多个表。但是，您可以使用事务使其立即发生：

$db = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
$db->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

try {
    $db->beginTransaction();

    $q1 = $db->prepare("INSERT INTO bag(bag_id, chara_id, item_id, item_qty)VALUES(NULL,:id,:item_id,'1')");
    $q1->bindValue(':id', $yourId);
    $q1->bindValue(':item_id', $yourItemId);
    $q1->execute();

    $q2 = $db->prepare("UPDATE chara SET chara_gold = chara_gold - :gold WHERE chara_id = :id");
    $q2->bindValue(':gold', $yourItemPrice);
    $q2->bindValue(':id', $yourCharaId);
    $q2->execute();

    $q3 = $db->prepare("UPDATE shop SET item_qty = :qty WHERE item_id = :item_id");
    $q3->bindValue(':qty', $yourQty);
    $q3->bindValue(':item_id', $yourItemId);
    $q3->execute();

    $db->commit();
} catch (Exception $e) {
    $db->rollback();
}

关于php - 同时插入和更新，或在SQL查询中使用事务处理，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16334257/