Running a Complex MySQL QUERY using PHP

Question

I have tried to fetch the data from a table named 'gainfinal' using a complex query given below :

    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`

I am sure the Query has been running well because it returned right data while running in Xampp.

My PHP code is like this :

    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");

?>

This PHP file had been returning right data for other queries. It only didn't work for this query onl. While running the PHP file, it says : Parse error: syntax error, unexpected T_LNUMBER in C:\xampp\htdocs\climateapp\data\chloroplath\data.php on line 17. How can I run the Query using PHP.

Answer 1

I think your quotes are off. The double quotes that surround:

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

Are affecting the php code. The easiest solution is to replace them with backticks:

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

Or, use names that don't need to be quotes:

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

EDIT:

You also have a problem in the first line. This line:

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

should be:

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

Answer 2

You are using double quotes in your query, which is terminating the string prematurely. Consider using a nowdoc instead:

<?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 are a convenient way of defining multiline "raw" strings, such as a query, when you don't want the contents to be parsed. This allows you to use whatever quotes you want within the query.

Answer 3

It seems to me that you could group by country code and year, you should be able to achieve the same result:

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

Moreover, are you planning to add every year to your query? Seems like a lot of work to me.