php - 使用 PHP 运行复杂的 MySQL QUERY

Question

我尝试使用下面给出的复杂查询从名为“gainfinal”的表中获取数据:

    SELECT  g.countrycode,
        sum(case when `year` = '1995' then g.values else 0 end) AS "1995",
        sum(case when `year` = '1996' then g.values else 0 end) AS "1996",
        sum(case when `year` = '1997' then g.values else 0 end) AS "1997",
        sum(case when `year` = '1998' then g.values else 0 end) AS "1998", 
        sum(case when `year` = '1999' then g.values else 0 end) AS "1999",
        sum(case when `year` = '2000' then g.values else 0 end) AS "2000",
        sum(case when `year` = '2001' then g.values else 0 end) AS "2001",
        sum(case when `year` = '2002' then g.values else 0 end) AS "2002",
         sum(case when `year` = '2003' then g.values else 0 end) AS "2003", 
         sum(case when `year` = '2004' then g.values else 0 end) AS "2004", 
         sum(case when `year` = '2005' then g.values else 0 end) AS "2005",
        sum(case when `year` = '2006' then g.values else 0 end) AS "2006",
        sum(case when `year` = '2007' then g.values else 0 end) AS "2007",
        sum(case when `year` = '2008' then g.values else 0 end) AS "2008",
        sum(case when `year` = '2009' then g.values else 0 end) AS "2009", 
         sum(case when `year` = '2010' then g.values else 0 end) AS "2010", 
         sum(case when `year` = '2011' then g.values else 0 end) AS "2011",
        sum(case when `year` = '2012' then g.values else 0 end) AS "2012"


FROM    `gainfinal` g
WHERE   `year` between '1995' and '2012'
GROUP BY `countrycode`

我确信查询一直运行良好，因为它在 Xampp 中运行时返回了正确的数据。

我的 PHP 代码是这样的:

    ini_set('display_errors', 1); 
    $username = "root"; 
    $password = "";   
    $host = "localhost";
    $database="climate";
     //$country = 'NPL';
    // $indices = 'foodfinal';
   // $country=$_GET["country"];

    // $indices=$_GET["indices"];


    $server = mysql_connect($host, $username, $password);
    $connection = mysql_select_db($database, $server);

    $myquery = "SELECT  g.countrycode,sum(case when `year` = '1995' then `g.values` else 0 end) AS "1995",
        sum(case when `year` = '1996' then g.values else 0 end) AS "1996",
        sum(case when `year` = '1997' then g.values else 0 end) AS "1997",
        sum(case when `year` = '1998' then g.values else 0 end) AS "1998", 
        sum(case when `year` = '1999' then g.values else 0 end) AS "1999",
        sum(case when `year` = '2000' then g.values else 0 end) AS "2000",
        sum(case when `year` = '2001' then g.values else 0 end) AS "2001",
        sum(case when `year` = '2002' then g.values else 0 end) AS "2002",
         sum(case when `year` = '2003' then g.values else 0 end) AS "2003", 

         sum(case when `year` = '2004' then g.values else 0 end) AS "2004", 
         sum(case when `year` = '2005' then g.values else 0 end) AS "2005",
        sum(case when `year` = '2006' then g.values else 0 end) AS "2006",
        sum(case when `year` = '2007' then g.values else 0 end) AS "2007",
        sum(case when `year` = '2008' then g.values else 0 end) AS "2008",
        sum(case when `year` = '2009' then g.values else 0 end) AS "2009", 
         sum(case when `year` = '2010' then g.values else 0 end) AS "2010", 
         sum(case when `year` = '2011' then g.values else 0 end) AS "2011",
        sum(case when `year` = '2012' then g.values else 0 end) AS "2012"


FROM    `gainfinal` g
WHERE   `year` between '1995' and '2012'
GROUP BY `countrycode`";
    $query = mysql_query($myquery);

    if ( ! $query ) {
        echo mysql_error();
        die;
    }

    $data = array();

    for ($x = 0; $x < mysql_num_rows($query); $x++) {
        $data[] = mysql_fetch_assoc($query);
    }

    echo json_encode($data);  
     mysql_close($server);
      //header('Location: linegraph.html');
     // include( "linegraph.html");

?>

此 PHP 文件一直在为其他查询返回正确的数据。它仅不适用于此查询。在运行 PHP 文件时，它说: 解析错误:语法错误，第 17 行 C:\xampp\htdocs\climateapp\data\chloroplath\data.php 中的意外 T_LNUMBER。如何使用 PHP 运行查询。

Answer 1

在我看来，您可以按国家/地区代码和 年份进行分组，您应该能够获得相同的结果:

SELECT  
  g.countrycode,
  g.year,
  SUM(g.values) as summed
FROM    `gainfinal` g
WHERE   g.year between '1995' and '2012'
GROUP BY g.countrycode, g.year

此外，您打算每年都添加到您的查询中吗？对我来说似乎工作量很大。