PHP SQLite PDO返回空的json数组

Question

I'm new(ish) to PHP and in particular PDO. Can someone help me understand why this is returning an empty array to JSON_encode?

The SQL query runs fine and returns results. When I pass this multi-step query via PDO, I'm not getting what I'd expect. I've looked at similar questions on SO and tried to reconcile with the PHP documentation without much insight.

What is the correct way to submit a multi-stage (somewhat complex) query via PDO to SQLite and pass the results to json_encode()? Any pointers are much appreciated.

UPDATED: Code example updated/cleaned with helpful comments from @Darren, @Phill, @Mike below.

$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);

If I split the query up like this I'm still only printing the $q4 query statement, not the results...

$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));

What is the correct way to use PDO to submit a multi-stage (complex) query to SQLite and passing results to json_encode?

Answer 1

Well, it seems there's plenty of info available about PDO not supporting multiple queries (halting after the first semi-colon in an SQL statement). Unfortunately there are very few examples of these sorts of multiple queries (most of which return no results). It took me a while to find this excellent tut. Which eventually led me to this solution.

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

?>

Thanks to everyone that took the time to view this question and offer comments.