I have a MySQL Query which executes correctly in SequelPro but doesnt execute in PHP Mysqli. The message which is shown:
Execution stopped.
Message: An error occured when executing sql-statement: SET @csum := 0; select kunde, sales, (@csum := @csum + cr.sales) as cumulative_sales FROM (and the whole query (too long for printing it here)
I tried it a few times in Sequel PRo and it works.
and the query is stored in the variable $toBeExecuted
also printed it out to check if its correct and it is.
return mysqli_query($this->connectionTargetDB, $toBoExecuted);
I guess theres a problem with SET @csum := 0
/edit
The whole Query:
SET @csum := 0;
select kunde, sales, (@csum := @csum + cr.sales) as cumulative_sales
FROM (
SELECT j.kunde as kunde,
ROUND(SUM(m.ausgangsrechnungen - m.eingangsrechnungen), 2) as sales
FROM jobs_per_month m,
jobs j,
temporal_dates t
WHERE day(t._date) = 1
AND (t._date BETWEEN date_add(now(), INTERVAL -12 MONTH) and now())
AND m.monat = month(t._date)
AND m.jahr = year(t._date)
AND j.internal_jobnr = m.internal_jobnr
GROUP BY j.kunde
HAVING sales >= 10000
UNION ALL
SELECT concat(COUNT(r.sales), ' Kunde < 10k') as kunde,
ROUND(SUM(r.sales), 2) as sales
FROM (SELECT j.kunde as kunde,
ROUND(SUM(m.ausgangsrechnungen - m.eingangsrechnungen), 2) as sales
FROM jobs_per_month m,
jobs j,
temporal_dates t
WHERE day(t._date) = 1
AND (t._date BETWEEN date_add(now(), INTERVAL -12 MONTH) and now())
AND m.monat = month(t._date)
AND m.jahr = year(t._date)
AND j.internal_jobnr = m.internal_jobnr
GROUP BY j.kunde
HAVING sales < 10000 AND sales > 0) r
ORDER by sales desc) cr
解决方案
Figured the answer out myself.
The problem was as I said in SET @csum := 0;
I dont know why but it works when I do a CROSS JOIN (SELECT @csum:=1) c
after the last FROM in the statement.