使用PHP运行一个复杂的MySQL查询 - Running a Complex MySQL QUERY using PHP

我试图从一个名为'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文件时，它说解析错误:语法错误，意外T_LNUMBER在C:'xampp'htdocs'climateapp'data'chloroplath'data.php第17行。我如何使用PHP运行查询。

在我看来，你可以按国家代码和年分组，你应该能够达到同样的结果:

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

此外，您是否计划在查询中添加每年?对我来说好像工作量很大。

您在查询中使用了双引号，这会过早地终止字符串。考虑使用nowdoc来代替:

<?php
$myquery = <<<'END_OF_QUERY'
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",
        // rest of query
END_OF_QUERY;

Nowdocs是一种定义多行"原始"字符串的方便方式，例如当您不希望解析内容时的查询。这允许你在查询中使用任何你想要的引号。

我觉得你的引号放错了。双引号:

     sum(case when `year` = '2003' then g.values else 0 end) AS "2003",

正在影响php代码。最简单的解决方案是用反号替换它们:

     sum(case when `year` = '2003' then g.values else 0 end) AS `2003`,

或者，使用不需要加引号的名称:

     sum(case when `year` = '2003' then g.values else 0 end) AS value_2003,

编辑:

第一行也有问题。这条线:

SELECT  g.countrycode, sum(case when `year` = '1995' then `g.values` else 0 end) AS "1995",

应:

SELECT g.countrycode, sum(case when `year` = '1995' then g.`values` else 0 end) AS `1995`,