CODEWITHSUNDEEP
phpmysqlsql
Shiva Bhusal
Shiva Bhusal

Reputation: 57

Using variables in Complex MySQL Query

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 ?

Upvotes: 0

Views: 110

Answers (2)

NoobEditor
NoobEditor

Reputation: 15891

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

Upvotes: 1

Sougata Bose
Sougata Bose

Reputation: 31749

use - '".$countryone."' instead of '$countryone' in the query as it will be treated as string.

Upvotes: 1

Related Questions