PHP SQLite PDO returns empty json array -

i'm new(ish) php , in particular pdo. can me understand why returning empty array json_encode?

the sql query runs fine , returns results. when pass multi-step query via pdo, i'm not getting i'd expect. i've looked @ similar questions on , tried reconcile php documentation without insight.

what correct way submit multi-stage (somewhat complex) query via pdo sqlite , pass results json_encode()? pointers appreciated.

updated: code example updated/cleaned helpful comments @darren, @phill, @mike below.

$dbh = new pdo('sqlite:livedb2.sqlite');

$sth = $dbh->prepare(' create temporary table tmpnodesa select source, location, count(*) value     [emergencydept(sankey)]     group source, location  union select location, destination, count(*) value     [emergencydept(sankey)]     group location, destination; create temporary table tmpnodesb select source, location, value      tmpnodesa      order value desc; create temporary table tmpnodesc select source name      tmpnodesb  union select location      tmpnodesb; create temporary table tmpnodesd select name     tmpnodesc; select name, rowid-1 id     tmpnodesd;');  $sth->execute(); print_r($sth); 

if split query i'm still printing $q4 query statement, not results...

$dbh = new pdo('sqlite:livedb2.sqlite');  $q1=('     create temporary table tmpnodesa     select source, location, count(*) value         [emergencydept(sankey)]         group source, location      union     select location, destination, count(*) value         [emergencydept(sankey)]         group location, destination;     ');  $q2=('     create temporary table tmpnodesb     select source, location, value          tmpnodesa          order value desc;     ');  $q3=('     create temporary table tmpnodesc     select source name          tmpnodesb      union     select location          tmpnodesb;     ');  $q4=('     create temporary table tmpnodesd     select name         tmpnodesc;     select name, rowid-1 id         tmpnodesd;     ');  $dbh->exec($q1); $dbh->exec($q2); $dbh->exec($q3); echo json_encode($dbh->query($q4)); 

what correct way use pdo submit multi-stage (complex) query sqlite , passing results json_encode?

well, seems there's plenty of info available pdo not supporting multiple queries (halting after first semi-colon in sql statement). unfortunately there few examples of these sorts of multiple queries (most of return no results). took me while find this excellent tut. led me solution.

<?php  try {      $dbh = new pdo("sqlite:livedb2.sqlite");      $dbh->setattribute( pdo::attr_errmode, pdo::errmode_exception );       $sth = $dbh -> query('          create temporary table tmpnodesa         select source, location, count(*) value             [emergencydept(sankey)]             group source, location          union         select location, destination, count(*) value             [emergencydept(sankey)]             group location, destination;     ');      $sth = $dbh -> query('          create temporary table tmpnodesb         select source, location, value              tmpnodesa              order value desc;     ');      $sth = $dbh -> query('          create temporary table tmpnodesc         select source name              tmpnodesb          union         select location              tmpnodesb;     ');      $sth = $dbh -> query('          create temporary table tmpnodesd         select name             tmpnodesc;     ');      $sth = $dbh -> query('          select name, rowid-1 id             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 took time view question , offer comments.