PHP SQLite PDO 返回空的 json 数组 - PHP SQLite PDO returns empty json array

我是PHP新手，特别是PDO。有人可以帮助我理解为什么这会向JSON_encode返回一个空数组吗？

SQL 查询运行正常并返回结果。当我通过 PDO 传递这个多步骤查询时，我没有得到我所期望的。我看过关于SO的类似问题，并试图与PHP文档协调，但没有太多的见解。

通过PDO向SQLite提交多阶段（有点复杂）查询并将结果传递给json_encode()的正确方法是什么？任何指示都非常感谢。

已更新

：代码示例已更新/清理，其中包含来自下面的@Darren、@Phill@Mike的有用注释。

$dbh = new PDO（'sqlite：livedb2.sqlite'）;

$sth = $dbh->prepare('
CREATE TEMPORARY TABLE TMPnodesA AS
SELECT Source, Location, COUNT(*) AS value
    FROM [emergencydept(sankey)]
    GROUP BY Source, Location 
UNION
SELECT Location, Destination, COUNT(*) AS value
    FROM [emergencydept(sankey)]
    GROUP BY Location, Destination;
CREATE TEMPORARY TABLE TMPnodesB AS
SELECT Source, Location, value 
    FROM TMPnodesA 
    ORDER BY value DESC;
CREATE TEMPORARY TABLE TMPnodesC AS
SELECT Source AS name 
    FROM TMPnodesB 
UNION
SELECT Location 
    FROM TMPnodesB;
CREATE TEMPORARY TABLE TMPnodesD AS
SELECT name
    FROM TMPnodesC;
SELECT name, rowid-1 as id
    FROM TMPnodesD;');
$sth->execute(); print_r($sth);

如果我像这样拆分查询，我仍然只打印$q4查询语句，而不是结果......

$dbh = new PDO('sqlite:livedb2.sqlite');
$q1=('
    CREATE TEMPORARY TABLE TMPnodesA AS
    SELECT Source, Location, COUNT(*) AS value
        FROM [emergencydept(sankey)]
        GROUP BY Source, Location 
    UNION
    SELECT Location, Destination, COUNT(*) AS value
        FROM [emergencydept(sankey)]
        GROUP BY Location, Destination;
    ');
$q2=('
    CREATE TEMPORARY TABLE TMPnodesB AS
    SELECT Source, Location, value 
        FROM TMPnodesA 
        ORDER BY value DESC;
    ');
$q3=('
    CREATE TEMPORARY TABLE TMPnodesC AS
    SELECT Source AS name 
        FROM TMPnodesB 
    UNION
    SELECT Location 
        FROM TMPnodesB;
    ');
$q4=('
    CREATE TEMPORARY TABLE TMPnodesD AS
    SELECT name
        FROM TMPnodesC;
    SELECT name, rowid-1 as id
        FROM TMPnodesD;
    ');
$dbh->exec($q1);
$dbh->exec($q2);
$dbh->exec($q3);
echo json_encode($dbh->query($q4));

使用 PDO 向 SQLite 提交多阶段（复杂）查询并将结果传递给 json_encode 的正确方法是什么？

好吧，似乎有很多关于PDO不支持多个查询的信息（在SQL语句中的第一个分号之后停止）。不幸的是，这种多查询的例子很少（其中大多数不返回任何结果）。我花了一段时间才找到这个优秀的嘟嘟。这最终导致我找到了这个解决方案。

<?php
try {
    $DBH = new PDO("sqlite:livedb2.sqlite");
    $DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $STH = $DBH -> query('
        CREATE TEMPORARY TABLE TMPnodesA AS
        SELECT Source, Location, COUNT(*) AS value
            FROM [emergencydept(sankey)]
            GROUP BY Source, Location 
        UNION
        SELECT Location, Destination, COUNT(*) AS value
            FROM [emergencydept(sankey)]
            GROUP BY Location, Destination;
    ');
    $STH = $DBH -> query('
        CREATE TEMPORARY TABLE TMPnodesB AS
        SELECT Source, Location, value 
            FROM TMPnodesA 
            ORDER BY value DESC;
    ');
    $STH = $DBH -> query('
        CREATE TEMPORARY TABLE TMPnodesC AS
        SELECT Source AS name 
            FROM TMPnodesB 
        UNION
        SELECT Location 
            FROM TMPnodesB;
    ');
    $STH = $DBH -> query('
        CREATE TEMPORARY TABLE TMPnodesD AS
        SELECT name
            FROM TMPnodesC;
    ');
    $STH = $DBH -> query('
        SELECT name, rowid-1 as id
            FROM TMPnodesD;
    ');
$json = array();
while($row = $STH ->fetch(PDO::FETCH_ASSOC)) {
        $json[] = $row;
};
echo json_encode($json);
    $DBH = null;
}
catch(PDOException $e){
    echo $e->getMessage();
}
?>

感谢所有花时间查看此问题并提供评论的人。