Using variables in Complex MySQL Query

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 ?

Use prepared statements. php.net/manual/en/pdo.prepared-statements.php — Justinas
– Justinas, Commented Aug 26, 2014 at 7:35

Sougata Bose · Accepted Answer · 2014-08-26 07:42:37Z

1

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

answered Aug 26, 2014 at 7:42

Sougata Bose

31.8k8 gold badges52 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shiva Bhusal Over a year ago

@ SGT, Still, It didn't work for me. Should I change AS $countryone to `".$countryone."' as well ? Or only change it and keep everything as it is.

Shiva Bhusal Over a year ago

I'm trying to convert the result into a JsonTable. I changed the code as $temp[] = array('v' => $r['".$countryone."']); $temp[] = array('v' => $r['".$countrytwo."']); Did I use the correct format or I made a mess out of it ?

Sougata Bose Over a year ago

if u r getting it as an array then json_encode would do the all work to convert it to json.or else just built an array and pass it to json_encode.

Shiva Bhusal Over a year ago

@ SGT, Isn't it possible to pass a variable inside $temp[] = array('v' => $r['".$countryone."']); $temp[] = array('v' => $r['".$countrytwo."']);where $countryone and $countrytwo are variables. I need the values of $counntryone and $countryone but it prints $countrycode and $countryone.

Shiva Bhusal Over a year ago

My problem is actually with the variables inside above two lines.

NoobEditor · Accepted Answer · 2014-08-26 12:59:42Z

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

edited Aug 26, 2014 at 12:59

answered Aug 26, 2014 at 7:36

NoobEditor

16k20 gold badges85 silver badges117 bronze badges

4 Comments

Shiva Bhusal Over a year ago

Thank you for the answer. It gave the right output but I nned to display the new column name as the value that has been passed in the variable.

NoobEditor Over a year ago

@CivaBhusal : so $countryone has a value?

Shiva Bhusal Over a year ago

Yaa. $countryone has a value.

NoobEditor Over a year ago

@CivaBhusal : try the updated query...just remove backtics from alias :)

Collectives™ on Stack Overflow

Using variables in Complex MySQL Query

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related