在复杂MySQL查询中使用变量

Question

My MySQL Query is like this :

 $myquery= "`SELECT  `year`, 

        sum(case when `countrycode` = '$countryone' then `values` else 0 end) AS `$countryone`,
        sum(case when `countrycode` = '$countrytwo' then `values` else 0 end) AS `$countrytwo`

FROM   `$index`
GROUP BY `year`"; 

This Query has to return all the values of the column values for two unique countrycodes in two different columns.

Before using the variables. my query was like this, which returned right output.

     $myquery = "SELECT  `year`, 

        sum(case when `countrycode` = 'NPL' then `values` else 0 end) AS `NPL`,
        sum(case when `countrycode` = 'USA' then `values` else 0 end) AS `USA`

FROM   `gainfinal`
GROUP BY `year`
";

But after replacing the real values with the variables the Query didn't work. How can I make the first Query run well ?

Answer 1

Problem is this :

AS `$countryone`,

when you pass this in SQL query, php would try to search and replace a value for $countryone...so i would suggest to hardcode it as below :

$myquery= "`SELECT  `year`, 
        sum(case when `countrycode` = '$countryone' then `values` else 0 end) AS $countryone, /* notice "`" is removed */
        sum(case when `countrycode` = '$countrytwo' then `values` else 0 end) AS $countrytwo  /* notice "`" is removed */
FROM   `$index`
GROUP BY `year`";