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

问题描述：

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;
?>

我遇到从临时表中获取json数据的问题。正如您所看到的，我尝试在$ query2 code>生成临时表 charts_econo code>，在第二个文件中我尝试将表解析为JSON，但是当我运行时，我什么都没得到第一个PHP（createTemp.php），最后是第二个（testTemp.php）。 p>

这是我的createTemp.php p>


 ＆lt  ;？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＆gt; query（$ query）; 
 $ return = array（）; 
if（$ results）{
while（$  row = $ results-＆gt; fetch_assoc（））{
 $ return [] = array（（float）$ row ['x']，（float）$ row ['y']）; 
} 
}  
 $ query2 =“CREATE TEMPORARY TABLE IF NOT NOT EXISTS`chart_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，
`econcon_sum_cost`十进制（12,7）NOT NULL 
）ENGINE = MyISAM DEFAULT CH  ARSET = utf8 AS（SELECT COUNT（项目），
 SUM（powerline_length），
 SUM（road_length），
 SUM（cost_per_year）
 FROM econo WHERE $ collm = 1;“; 
 $ con＆gt;  query（$ query2）; 
 $ con＆gt; close（）; 
echo json_encode（$ return）; 
？＆gt; 
  code>  pre> 
 
 这是 testTemp.php  p> 
 
 
 ＆lt;？PHP 
include'conconfig.php'; 
 $ con = new mysqli（DB_HOST，DB_USER，DB_PASS，DB_NAME）; 
 $ query  =“SELECT * FROM`chart_econo`”; 
 $ results = $ con＆gt; query（$ query）; 
 
if（$ results）{
while（$ row = $ results-＆gt; fetch_assoc（））  {
 $ json = json_encode（$ row）; 
} 
} 
 $ con＆gt; close（）; 
echo $ json; 
？＆gt; 
  code>  pre> \  n  div>

答

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]

decoded_json['results1']

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

相关推荐