My query doesn't work if the last query has a comment on it.

user_9864672 0
Viewed 79

we've been struggling to some situations in PHP wherein some of the queries don't work. We've found out that the non-working-queries occurs only if the last query executed has a comment in it.

In php, we have something like this:

$getUsersQuery = "SELECT * from `users` where active = 1; ##DEVELOPER: JOE";
$getUsersStmt = $con->prepare($getUsersQuery);
$getUsersStmt->execute();
$users = $getUsersStmt->fetchAll();

$getProductsQuery = "SELECT * from `products` where active = 1; ##DEVELOPER: JOE";
$getProductsStmt = $con->prepare($getProductsQuery);
$getProductsStmt->execute();
$products = $getProductsStmt->fetchAll();

For the $users variable, it contains accurate data, but in $products variable, it contains nothing. After moments of configuring, we got a theory that it's because the last query executed has comment ##DEVELOPER: JOE on it. We removed the comment of the first query, and the second query worked. We've tested it, and the theory is proven. Btw, we are using MySql and PHP 5.

We now know the reason how it occurs. But our question is that, why is it occurring?

If you have ideas, please do comment it out. That will be a big help for us. Thanks.

pdo mysql php
1 Answers

The culprit is not the comment itself. It's the combination of having a ; statement delimiter, a comment and two different statements. Here's a simpler reproduce code:

$stmt = $con->prepare('SELECT CURRENT_DATE; #');
$stmt->execute();
var_dump($stmt->fetchAll());

$stmt2 = $con->prepare('SELECT CURRENT_TIME');
$stmt2->execute();
var_dump($stmt2->fetchAll());

array(1) {
  [0]=>
  array(2) {
    ["CURRENT_DATE"]=>
    string(10) "2018-11-13"
    [0]=>
    string(10) "2018-11-13"
  }
}
array(0) {
}

What's probably happening is that PDO believes you're issuing multiple statements thus expects multiple result-sets. If you overwrite the statement it seems to clear stuff automatically but if you create a new variable it seems it's still expecting the first statement to complete.

If you call PDOStatement::nextRowset() everything works again:

$stmt = $con->prepare('SELECT CURRENT_DATE; #');
$stmt->execute();
var_dump($stmt->fetchAll());
$stmt->nextRowset(); // <--- Retrieve the "phantom" result-set

$stmt2 = $con->prepare('SELECT CURRENT_TIME');
$stmt2->execute();
var_dump($stmt2->fetchAll());

Alternatively, you can set PDO::ATTR_EMULATE_PREPARES => false so PDO will not parse SQL and let the job to MySQL server.

user_135080
Related