我要执行并获取以下查询的结果:
$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;
$sql = $PDO->query($query); $sql->rowCount()
返回0,没有结果行。我已经通过直接在数据库中执行查询来测试查询,并且它可以正常工作。 最佳答案
解决方法是进行多次查询,将set
更改为select
,然后正确地遍历结果。
$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]);
}
}