从MySQL临时表获取数据的问题

Question

I am having issue on getting json Data from a Temporary table. As you can see I tried to generate a Temporary Table charts_econo at $query2 and in second file I tried to parse the table into JSON but I am getting nothing when I run the First PHP (createTemp.php) and eventually the second one(testTemp.php).

Here is my createTemp.php

<?PHP
include 'conconfig.php';
$con = new mysqli(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$collm = $_POST['column'];
$query = "SELECT x, y  FROM  econo WHERE ".$collm."=1";
$results = $con->query($query);
$return = array();
if($results) {
while($row = $results->fetch_assoc()) {
    $return[] = array((float)$row['x'],(float)$row['y']);
}
}
$query2 = "CREATE TEMPORARY TABLE IF NOT EXISTS `charts_econo` (
          `econo_sum_projects` decimal(12,7) NOT NULL,
          `econo_sum_powerline` decimal(12,7) NOT NULL,
          `econo_sum_roads` decimal(12,7) NOT NULL,
          `econo_sum_cost` decimal(12,7) NOT NULL
          ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AS(SELECT COUNT(project),
                                                   SUM(powerline_length),
                                                   SUM(road_length),
                                                   SUM(cost_per_year)
                                                   FROM econo WHERE $collm=1;";
$con->query($query2);
$con->close();
echo json_encode($return);
?>

and this is testTemp.php

<?PHP
include 'conconfig.php';
$con = new mysqli(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$query = "SELECT *  FROM `charts_econo`";
$results = $con->query($query);

if($results) {
while($row = $results->fetch_assoc()) {
$json= json_encode($row);
   }
}
$con->close();
echo $json;
?>

Answer 1

From the mysql documentation:

A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed.

So, the second script (in most cases) can't access the table created by the first one! You'll have to query it and output the JSON in the first script, or output the result of the SELECT from the CREATE TEMPORARY TABLE in the first script directly in either script (i.e. not use any temporary table).

To output both in the same JSON, you can create an array of both:

echo json_encode([$results1, $results2]);

Or an associative array/object:

echo json_encode({'results1':$results1, 'results2':$results2});

This changes the way the client will get access to each part once decoded:

decoded_json[0]

or

decoded_json['results1']