JSON SQL Query gives error on SET

Question

$query = mysql_query(" SET @c1=0; SELECT @c1 := @c1+1 as Week,
AVG(Temp) AS Average_Temperature FROM (   SELECT t1.*, COUNT(*) cnt
FROM test2 t1   LEFT JOIN test2 t2
ON t2.Date <= t1.Date
AND YEAR(FROM_UNIXTIME(t2.Date)) = YEAR(FROM_UNIXTIME(t1.Date))
AND MONTH(FROM_UNIXTIME(t2.Date)) = MONTH(FROM_UNIXTIME(t1.Date))   GROUP 
BY Date ) t GROUP BY   YEAR(FROM_UNIXTIME(Date)), MONTH(FROM_UNIXTIME(Date)), CEIL(cnt/7);" );

If I dont use SET @c1=0; I got no error... So whats the use of this on json php? Above code successfully queried on PHPMyAdmin.

edit: solved

I just want to get row count on the current table but others fail.. Only this one is good for me.. what can I do? — Atik
– Atik, Commented Dec 28, 2013 at 15:07
Why don't you do it in PHP? Run the select query, iterate the result set and count... — Leonardo
– Leonardo, Commented Dec 28, 2013 at 15:11

Shankar Narayana Damodaran · Accepted Answer · 2013-12-28 14:35:36Z

1

Just enclose them in quotes .. See here $query = mysql_query(" your query ");

Like this..

$query = mysql_query("
    SELECT AVG(Temp) AS Average_Temperature FROM (
    SELECT t1.*, COUNT(*) cnt FROM test2 t1
  LEFT JOIN test2 t2
    ON t2.Date <= t1.Date
    AND YEAR(FROM_UNIXTIME(t2.Date)) = YEAR(FROM_UNIXTIME(t1.Date))
        AND MONTH(FROM_UNIXTIME(t2.Date)) = MONTH(FROM_UNIXTIME(t1.Date))
  GROUP 
    BY Date
) t
GROUP BY
  YEAR(FROM_UNIXTIME(Date)), MONTH(FROM_UNIXTIME(Date)), CEIL(cnt/7);
");

^{Disclaimer : Stop using mysql_* functions as they are deprecated. Switch to MySQLi or PDO instead.}

edited Dec 28, 2013 at 14:35

answered Dec 28, 2013 at 14:30

Shankar Narayana Damodaran

68.6k43 gold badges102 silver badges129 bronze badges

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

1 Comment

Atik Over a year ago

oh! I forgot it! Thanks it works now.. Its easy to forget this things in a mess like that :)

Gordon Linoff · Accepted Answer · 2013-12-28 16:03:20Z

0

Clearly, the set and select are incompatible as a single query. There is an easy work-around. Set the variable in the query:

SELECT @c1 := @c1+1 as Week, AVG(Temp) AS Average_Temperature
FROM (SELECT t1.*, COUNT(*) as cnt
      FROM test2 t1 LEFT JOIN
           test2 t2
           ON t2.Date <= t1.Date AND
              YEAR(FROM_UNIXTIME(t2.Date)) = YEAR(FROM_UNIXTIME(t1.Date)) AND
              MONTH(FROM_UNIXTIME(t2.Date)) = MONTH(FROM_UNIXTIME(t1.Date))
      GROUP BY Date
     ) t cross join
     (select @c1 := 0) const
GROUP BY YEAR(FROM_UNIXTIME(Date)), MONTH(FROM_UNIXTIME(Date)), CEIL(cnt/7);

answered Dec 28, 2013 at 16:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

JSON SQL Query gives error on SET

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related