CODEWITHSUNDEEP
phpmysqlcakephpcakephp-2.0
Adam Friedman
Adam Friedman

Reputation: 549

Multiple SQL statements in a single CakePHP query() call?

I'm trying to do some complex SQL that requires me to set a couple of variables in MySQL in one statement and then executing a query that relies on them in the next statement. For this I need to send these commands to the database in a single $Model->query() call.

The two-statement call looks similar to this, pasted from this tutorial:

set @num := 0, @type := '';

select type, variety, price,
  @num := if(@type = type, @num + 1, 1) as row_number,
  @type := type as dummy
from fruits force index(type)
group by type, price, variety
having row_number <= 2;

Interestingly, I cannot find a way to execute multiple semi-colon-separated SQL statements through any CakePHP model method (something like mysqli::multi_query). It appears that the query() method only executes the first statement in this multi-statement call.

How do I execute both in the same call?

Upvotes: 3

Views: 2541

Answers (2)

Erebus
Erebus

Reputation: 2038

Looking at the CakePHP API, when the _execute function is run, it explodes on a ; and runs each as separate queries:

api.cakephp.org/2.4/source-class-DboSource.html#__execute

I just did a test with two queries, ran through the query function, and while it does run both it only returns the data from the first query. This is why you are getting a return of 0 rows (as the first query is variable creation). I'm sure this is a Cake default, as it wouldn't know what to return in the case of multiple queries.

Upvotes: 1

Aziz Saleh
Aziz Saleh

Reputation: 2707

Using PDO->Query that won't be possible. There are some solutions if you are using PDP Version >=5.3 listed here (using exec()):

https://stackoverflow.com/a/6461110/1935500

Upvotes: 0

Related Questions