PHP SQLite PDO返回空json数组

问题描述：

我是PHP（特别是PDO）的新成员（ish）。有人可以帮助我理解为什么这是返回一个空数组到JSON_encode？PHP SQLite PDO返回空json数组

SQL查询运行良好并返回结果。当我通过PDO传递这个多步查询时，我得不到我期望的结果。我已经看过类似的问题，并试图在没有太多洞察力的情况下与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？

你不应该像这样循环访问'fetchAll（）'。什么'print_r（$ sth-> fetchAll（））;'show？ – Darren 2014-12-01 23:29:57

'新的PDO（...）或死（...）'是**永远不会**将达到您的'die'语句，所以请停止这样做 – Phil 2014-12-01 23:36:53

'print_r（$ sth-> fetchAll（））;''产生'Array（）' – Colin 2014-12-02 01:27:20

答

好吧，似乎有很多关于PDO的信息不支持多个查询（在SQL语句中的第一个分号后停止）。不幸的是，这种多重查询的例子很少（其中大部分都没有返回结果）。我花了一段时间才发现this excellent tut.这最终导致我找到了这个解决方案。

<?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(); 
} 

?>

感谢大家花时间查看此问题并提供意见。

PHP SQLite PDO返回空json数组

相关推荐