CODEWITHSUNDEEP
phpsqljson
Atik
Atik

Reputation: 99

JSON SQL Query gives error on SET

$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

Upvotes: 0

Views: 82

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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

Upvotes: 0

Shankar Narayana Damodaran
Shankar Narayana Damodaran

Reputation: 68526

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.

Upvotes: 1

Related Questions