使用 PHP 和 Mysql 使用一个查询将数据插入到 2 个表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22384391/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert DATA into 2 tables using one Query using PHP and Mysql
提问by user3311898
I have 2 tables that I am trying to insert data into using PHP
我有 2 个表，我正在尝试使用 PHP 将数据插入其中
Table: WINE
-----------------------------------------------------------------
| wine_id | wine_type | country | indicator | color |
-----------------------------------------------------------------
wine_id is auto incremented, then This is my other table
wine_id 是自动递增的，然后这是我的另一个表
Table: stock
--------------------------------------
| stock_id | wine_id | quantity |
--------------------------------------
For the STOCK table I have stock ID as Auto incremented and wine_id is foreign-key so All i need to insert is quantity.
对于 STOCK 表，我的股票 ID 是自动递增的，而 wine_id 是外键，所以我需要插入的只是数量。
I have a syntax like this:
我有这样的语法：
$sql = mysql_query("INSERT INTO TABLE1(field1, field2, field3) VALUES('value1',value2,value3) INSERT INTO STOCK(field) VALUES ('value1')");
If there is another way to do so please suggest and I would like some examples please.
如果有另一种方法可以这样做，请提出建议，我想要一些例子。
回答by Shankar Damodaran
You need to separate your two INSERTstatements with a semicolon.
您需要INSERT用分号分隔两个语句。
This(mysql_*) extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLior PDO_MySQLextension should be used. Switching to PreparedStatementsis even more better to ward off SQL Injection attacks !
This( mysql_*) 扩展从 开始被弃用PHP 5.5.0，将来会被删除。相反，应该使用MySQLiorPDO_MySQL扩展名。切换到PreparedStatements更好地抵御 SQL 注入攻击！
回答by Levi Kovacs
You can run multiple INSERTS once, however you cannot insert into two tables from one single INSERTStatement.
Also make sure to get the last inserted ID with LAST_INSERT_ID()for the foreign key and use a semicolon ;between the two INSERTS
您可以一次运行多个 INSERTS，但是您不能从一个INSERT语句插入到两个表中。还要确保LAST_INSERT_ID()为外键获取最后插入的 ID，并;在两个 INSERTS 之间使用分号
回答by Yoda
From the looks of it, what you're trying to do is:
从它的外观来看，您要做的是：
- Insert data into the
winetable - Get the ID from the
winetable - Insert the ID into the
stocktable
- 向
wine表中插入数据 - 从
wine表中获取 ID - 将 ID 插入
stock表中
mysql_querydoes not support multiple queries. So, my suggestion would be:
mysql_query不支持多个查询。所以，我的建议是：
$result = mysql_query("INSERT INTO `wine` (`wine_type`, `country`, `indicator`, `colour`) VALUES ('Merlot', 'Australia', 'Dry', 'Red')");
$result = mysql_query("INSERT INTO `stock` (`wine_id`, `quantity`) VALUES ('".mysql_insert_id()."', '0');");
Modifying of course to take into account your own variables and value sanitation. As has been mentioned, the mysql_functions are being deprecated, and you're best to move to a PDO structure for your database in the near future.
修改当然要考虑到您自己的变量和价值卫生。如前所述，这些mysql_函数已被弃用，最好在不久的将来为您的数据库迁移到 PDO 结构。
回答by Awlad Liton
you can do it by multiple statement details
您可以通过 多个语句详细信息来完成
like this:
像这样：
$sql = "INSERT INTO TABLE1(field1, field2, field3) VALUES('value1',value2,value3); INSERT INTO STOCK(field) VALUES ('value1');";
multi_query($conn,$sql);
you need to use mysqli_*for this. I assume $connis your mysqliconnection
你需要为此使用mysqli_*。我假设$conn是你的mysqli连接
