运行sql查询以插入到表中并更新另一个表

How do i run a sql query in php to insert into a table and update another table. for example, when an inventory is entered, and submitted, sql query should run an insert statement to update the batch file and run update statement to update the totalquantityinstock (new quantity + existing quantity) in the product table. Hope my explanation could be understood.

如何在php中运行sql查询以插入到表中并更新另一个表。例如，当输入并提交清单时，sql查询应该运行一条insert语句来更新批处理文件，并运行update语句来更新product表中的totalquantiinstock(新数量+现有数量)。希望我的解释能被理解。

Tables: Product (ProdId, Name, Cat, TtlQtyInStock,) and ProdInventory (InvID, ProdID, Qty)

表:产品(ProdId, Name, Cat, TtlQtyInStock，)和产品目录(InvID, ProdId, Qty)

Upon entering a new ProdInventory record in php form, the query should insert into ProdInventory and also update the TtlQtyInStock column in Product table).. What i want is currently the inventory of the product cld be 20, so when new inventory of 10 of same prod is added it shld sum 20 + 10.

当以php形式输入新的ProdInventory记录时，查询应该插入到ProdInventory中，并更新Product表中的TtlQtyInStock列)。我现在想要的是产品cld的库存是20，所以当增加10个相同的prod的新库存时，应该是20 + 10。

My current query is:

我现在查询的方法是:

$query = "INSERT INTO `ProdInventory (`ProdId`, `Quantity`) VALUES ('$ProdID', '$Quantity')";
"UPDATE product
SET QuantityInStock = (QuantityInStock + $Quantity)
WHERE ProductCode = $ProductCode";

I did browse thru some site but cldnt find something that suits my design. if someone cld help... :)

我确实浏览了一些网站，但是cldnt找到了一些适合我设计的东西。如果有人cld帮助……:)

TIA.

TIA。

3 个解决方案

#1

the probable solution for that though not the best is by creating an Insert Trigger. so basically what it does is everytime an insert is made in the table the trigger fires and updates the value of the stock (it may be on the different table)

可能的解决方案是创建一个插入触发器。基本上每次在表中插入时触发器都会触发并更新股票的值(它可能在不同的表上)

#2

You need to run two update statments, instead of an insert and update.

您需要运行两个更新语句，而不是插入和更新。

This can be achieved in one mysql trip using stored procedures or multi_query (MYSQLi).

这可以通过使用存储过程或multi_query (MYSQLi)的一次mysql访问实现。

Here is a link for reference :

这里有一个链接以供参考:

http://php.net/manual/en/mysqli.multi-query.php

http://php.net/manual/en/mysqli.quickstart.stored-procedures.php

#3

I suggest you create a view. In that view you can make a column in which you can calculate anything you need. that way, your database structure will be very clean, and the chances of errors will reduce imho

我建议您创建一个视图。在这个视图中，您可以创建一个列，在其中您可以计算任何您需要的东西。这样，您的数据库结构将非常清晰，出错的可能性将减少imho

#1