PHP mysqli query error but query is correct

Question

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

Your Common Sense · Accepted Answer · 2017-08-09 11:12:44Z

1

The error is pretty obvious.

What you have here is not an SQL query, but a set of multiple queries.
Whereas mysqli_query is intended to run only one query.

Therefore you should make two calls,

$this->connectionTargetDB->query("SET @csum := 0");
return $this->connectionTargetDB->query($toBoExecuted);

answered Aug 9, 2017 at 11:12

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

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

Comments

Markus G. · Accepted Answer · 2017-08-09 10:38:03Z

0

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.

answered Aug 9, 2017 at 10:38

Markus G.

1,7283 gold badges28 silver badges53 bronze badges

Collectives™ on Stack Overflow

PHP mysqli query error but query is correct

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related