如何在php MySQL中从两个不同的表更新表？

Question

我有三张桌子，预订，订单和库存。在预订中，我有订单，例如(两个苹果和一个orange).(reservation.confirmation=orders.confirmation).我想更新苹果和橙色inventory.sold时，reservation.status更改为“已交付”

reservation
---------------------------
confirmation | status

EX-10          pending


orders
---------------------------
confirmation  | product    |  qty

EX-10           apple         2
EX-10           orange        1


inventory
---------------------------
id            | nameofproduct | sold

1               apple           -->update to 2
2               orange          -->update to 1

到目前为止，这是我的代码。

$confirmation = $_POST['confirmation'];
    $status=$_POST['status'];
    mysql_query("UPDATE reservation SET status='$status' WHERE reservation_id='$roomid'");
    if ($status=='Delivered')
{
    mysql_query("UPDATE inventory SET sold = (SELECT qty FROM orders where confirmation='$confirmation') where nameofproduct=(SELECT prduct FROM orders where confirmation='$confirmatioin')");

        }

请帮助进行正确的查询。谢谢

Answer 1

你听说过TRIGGERS吗？请参阅：http://www.sitepoint.com/how-to-create-mysql-triggers/

我已经为你做了你想要的东西，我没有检查它，所以可能有错误，如果你有问题请告诉我。

DELIMITER $$
CREATE TRIGGER inventory
AFTER UPDATE ON reservation FOR EACH ROW
BEGIN
        DECLARE finished INT DEFAULT 0;
        DECLARE product VARCHAR(200); -- SHOULD BE THE SAME TYPE AND LENGTH AS order.product
        DECLARE qty INT;  -- SHOULD BE THE SAME TYPE AND LENGTH AS order.qty
        DECLARE orders_cursor CURSOR FOR SELECT o.product, o.qty FROM orders o WHERE o.confirmation = NEW.confirmation;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;



        IF (NEW.status = 'delivered') THEN
            open orders_cursor;
            get_orders: LOOP

                FETCH orders_cursor INTO product, qty;

                IF finished = 1 THEN
                    LEAVE get_orders;
                END IF;

                -- UPDATE INVENTORY
                UPDATE inventory i SET i.sold = qty WHERE i.nameofproduct = product;
            END LOOP get_orders;
            close orders_cursor;
        END IF;


END
$$