Webeng Webeng - 4 months ago 22
Ajax Question

MySQL Update and Select in one statement

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 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:


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


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.

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

Answer

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

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)