MySQL Update和Select在一个语句中

I am attempting to do an UPDATE and a SELECT in the same sql statement. For some reason, the below code is failing.

我试图在同一个sql语句中执行UPDATE和SELECT。由于某种原因，下面的代码失败了。

$sql = "UPDATE mytable SET last_activity=CURRENT_TIMESTAMP, 
info1=:info1, info2=:info2 WHERE id = {$id};";

$sql .= "SELECT id, info1, info2 FROM myTable 
WHERE info1 >=:valueA AND info2>:valueB;"

$stmt = $conn->prepare($sql);
$stmt->bindParam(":info1", $info1);
$stmt->bindParam(":info2", $info2);

$stmt->bindParam(":valueA", $valueA);
$stmt->bindParam(":valueB", $valueB);

$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo json_encode($result);

QUESTION: what might I be doing wrong? I have been spending hours on this issue knowing that it's probably a small error right under my nose.

问题：我可能做错了什么？我已经在这个问题上花了好几个小时知道这可能是我的鼻子下面的一个小错误。

Edited:

编辑：

I obtained this error message when loading the page that contains the php code:

我在加载包含php代码的页面时收到此错误消息：

Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error' in ajaxCall.php:89 Stack trace: #0 ajaxCall.php(89): PDOStatement->fetchAll(2) #1 {main} thrown in ajaxCall.php on line 89

ajaxCall.php中带有消息'SQLSTATE [HY000]：常规错误'的未捕获异常'PDOException'：89堆栈跟踪：＃0 ajaxCall.php（89）：PDOStatement-> fetchAll（2）#a {main}在ajaxCall中抛出。第89行的PHP

I am using ajax to call the php page that contains the above code, and when I load the php page from the browser, I get the above error message.

我使用ajax来调用包含上面代码的php页面，当我从浏览器加载php页面时，我收到上面的错误消息。

Line 89 is: $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

第89行是：$ result = $ stmt-> fetchAll（PDO :: FETCH_ASSOC）;

2 个解决方案

#1

Since you are running two queries, you need to call nextRowset to access the results from the second one.

由于您正在运行两个查询，因此需要调用nextRowset来访问第二个查询的结果。

So, do it like this:

所以，这样做：

// code
$stmt->execute();
$stmt->nextRowset();
// code

When you run two or more queries, you get a multi-rowset result. That means that you get something like this (representation only, not really this):

当您运行两个或多个查询时，您将获得多行集结果。这意味着你得到这样的东西（仅代表，不是真的这样）：

Array(
    [0] => rowset1,
    [1] => rowset2,
    ...
)

Since you want the second set -the result from the SELECT-, you can consume the first one by calling nextRowset. That way, you'll be able to fetch the results from the 'important' set.
(Even though 'consume' might not be the right word for this, it fits for understanding purposes)

由于您需要第二个设置 - SELECT-的结果，您可以通过调用nextRowset来使用第一个设置。这样，您就可以从“重要”集中获取结果。（即使'消费'可能不是正确的词，它适合理解目的）

#2

Executing two queries with one call is only allowed when you are using mysqlnd. Even then, you must have PDO::ATTR_EMULATE_PREPARES set to 1 when using prepared statements. You can set this using:

只有在使用mysqlnd时，才允许使用一次调用执行两个查询。即便如此，在使用预准备语句时，必须将PDO :: ATTR_EMULATE_PREPARES设置为1。您可以使用以下方式设置：

$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, 1);

Alternatively, you can use $conn->exec($sql), which works regardless. However, it will not allow you to bind any data to the executed SQL.

或者，您可以使用$ conn-> exec（$ sql），无论如何都可以。但是，它不允许您将任何数据绑定到已执行的SQL。

All in all, don't execute multiple queries with one call.

总而言之，不要通过一次调用执行多个查询。

#1