如何使用 PHP/MYSQL 在单个查询中执行 SELECT 和 INSERT？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1118095/
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
How to execute SELECT and INSERT in single query with PHP/MYSQL?
提问by Prashant
I have a table user_namewith 3 fields, id, Name, Email(idis auto_incrementfield). I want to execute the following query in PHP, but its not returning any result.
我有一个user_name包含 3 个字段的表，id, Name, Email（id是auto_increment字段）。我想在 PHP 中执行以下查询，但它不返回任何结果。
INSERT INTO user_name (Name, Email) VALUES ('Example', '[email protected]');
SELECT LAST_INSERT_ID() AS 'userid';
When I am executing the above query in PHP as below then its not returning anything.
当我在 PHP 中执行上述查询时，如下所示，它不返回任何内容。
$_SQL="INSERT INTO user_name (Name,Email) VALUES ('Example', '[email protected]');
SELECT LAST_INSERT_ID() AS 'userid';";
$result_last_id = @mysql_query($_SQL);
$rs_insert = mysql_fetch_array($result_last_id);
$new_userid = $rs_insert['userid'];
Can anyone please tell me how to execute both queries into one.
谁能告诉我如何将两个查询合二为一。
回答by CMS
Give a look to the mysql_insert_id()function.
看看mysql_insert_id()功能。
mysql_query($insertStatementOnly);
$new_userid = mysql_insert_id();
回答by Blixt
It appears you don't need to execute multiple queries, but I included how to do it below. What you want is the last inserted id, which you get from mysql_insert_id.
看起来您不需要执行多个查询，但我在下面介绍了如何执行。你想要的是最后插入的 id，你从mysql_insert_id.
To execute multiple queries
执行多个查询
From comments on documentation of mysql_query:
来自对文档的评论mysql_query：
The documentation claims that "multiple queries are not supported".
However, multiple queries seem to be supported. You just have to pass flag 65536 as mysql_connect's 5 parameter (client_flags). This value is defined in /usr/include/mysql/mysql_com.h:
#define CLIENT_MULTI_STATEMENTS (1UL << 16) /* Enable/disable multi-stmt support */Executed with multiple queries at once, the mysql_query function will return a result only for the first query. The other queries will be executed as well, but you won't have a result for them.
该文档声称“不支持多个查询”。
但是，似乎支持多个查询。您只需将标志 65536 作为 mysql_connect 的 5 参数（client_flags）传递。这个值在 /usr/include/mysql/mysql_com.h 中定义：
#define CLIENT_MULTI_STATEMENTS (1UL << 16) /* Enable/disable multi-stmt support */一次执行多个查询，mysql_query 函数将只返回第一个查询的结果。其他查询也将被执行，但您不会得到它们的结果。
Alternatively, have a look at the mysqlilibrary, which has a multi_querymethod.
或者，看看mysqli图书馆，它有一个multi_query方法。
回答by Fanis Hatzidakis
May I also suggest you avoid the error-suppression operator '@' in mysql_query as you may not be made aware of any mysql errors. At the very least do
我还建议您避免在 mysql_query 中使用错误抑制运算符“@”，因为您可能不知道任何 mysql 错误。至少做
mysql_query($sql) or die("error: " . mysql_error()) ;
回答by Richy B.
If you are using the Zend Framework with a PDO defined MySQL database, you would just use:
如果您将 Zend 框架与 PDO 定义的 MySQL 数据库一起使用，您只需使用：
$database=Zend_Db::factory('PDO_MySQL',Array('hostname'=>'localhost','username'=>'x','password'=>'y','dbname'=>'z');
$connectionHandle=$database->getConnection();
$rowsInserted=$connectionHandle->insert('database_name','INSERT INTO x (a,b) VALUES (c,d)');
if ($rowsInserted>0) {
$autoIncrementValue=$connectionHandle->lastInsertId();
}
回答by PHP Developer
Yes You can using Shell command <BR>mysql -user -p -h database -e '
SQL STATEMENT 1;
SQL STATEMENT 2;
SQL STATEMENT 3;
.......;
'
是的，您可以使用 Shell 命令<BR>mysql -user -p -h database -e ' SQL STATEMENT 1; SQL 语句 2；SQL语句3；......; '
回答by jezmck
Simple answer really: You just can't do it.
答案很简单：你就是做不到。
回答by Azfar Niaz
this might do what u want:
这可能会做你想要的：
insert into table1 (Name,Emails) values ('qqq','www');
select max(id) as lastinserted from table1;
