PHP PDO / MySQLi doesn't return rows while query has mysql user-variables

Question

I want to execute and get the results of the following query:

$query = <<<SQL
    set @num := 0, @priority := '';
    select * from (
        select
        id, status_ts,
        @num := if(@priority = priority, @num + 1, 1) as _row_number,
        @priority := priority as priority
        FROM ($priority_query) as get_priority
        ORDER BY priority DESC, status_ts ASC
    ) as items where items._row_number <= CEIL(priority);
SQL;

The $sql = $PDO->query($query); $sql->rowCount() returns 0, there are no result rows. I've tested the query by executing it directly in the DB and it works.

I agree with @PaulSpiegel. There is no reason to batch multiple statements into one execution, and it makes code more complicated and harder to debug. — Bill Karwin, May 08 '18 at 15:38
Haven't though of that very simple answer. :/ Thanks! I'll try it. — Kangur, May 15 '18 at 22:46

score 0 · Answer 1 · edited May 08 '18 at 14:02

The way to go was to make a multiple query, changing set to select and then properly iterating through the results.

$query = <<<SQL
    select @num := 0, @priority := '';
    select * from (
        select
        id, status_ts,
        @num := if(@priority = priority, @num + 1, 1) as _row_number,
        @priority := priority as priority
        FROM ($priority_query) as get_priority
        ORDER BY priority DESC, status_ts ASC
    ) as items where items._row_number <= CEIL(priority);
SQL;

PDO

    $sql = $pdo->query($query);

    if ($sql && $sql->nextRowset()) {
        $items = [];
        $numRows = $sql->rowCount();
        if (($numRows > 0)) {
            $items = $sql->fetchAll(\PDO::FETCH_OBJ);
        }
    } else {
        $error = $this->DB->pdo->errorInfo();
        throw new \Exception($error[2]);
    }

MySQLi

$mysqli->multi_query($query);
$mysqli->next_result();

if ($result = $mysqli->store_result()) {
   while ($row = $result->fetch_row()) {
      printf("%s\n", $row[0]);
   }
}

PHP PDO / MySQLi doesn't return rows while query has mysql user-variables

1 Answers1

PDO

MySQLi