3

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?

Adam Friedman
  • 520
  • 6
  • 20
  • Are you sure you've got your syntax correct? Looking at the CakePHP API, when the _execute function is run, it explodes on a ; and runs each as separate queries: http://api.cakephp.org/2.4/source-class-DboSource.html#__execute. Perhaps you should simplify you example first to determine if it is actually a Cake issue, or something else. – Erebus Nov 26 '13 at 01:20
  • Yep, I'm sure I have my syntax correct. I see that the this two-statement SQL is being executed but the CakePHP query debug output reports that it returns 0 rows. When I run the same two-statement SQL directly in the MySQL console, it returns hundreds of rows. – Adam Friedman Feb 02 '14 at 21:01
  • 1
    I just did a test with two queries run 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. You might be able to work your queries into one, but I'm not sure how. Perhaps you could ask a separate question specific to that. – Erebus Feb 03 '14 at 20:52
  • Just looking through the tutorial you linked, are you sure you can't implement what you need with a UNION query, or perhaps running a broader query, and then filtering out the results you want from within Cake? – ahjohnston25 Mar 02 '14 at 20:57
  • You [can't do that with php by default](http://stackoverflow.com/questions/19666488/is-an-sql-injection-actually-possible-by-adding-a-second-query/19678610#19678610). You're better off using a stored proceedure. – AD7six Mar 03 '14 at 08:46
  • @Erebus: Can you transfer your comments into an answer and I'll accept? You correctly point out that my question is a red herring -- Cake does call them both and returns the results of the first. – Adam Friedman Jul 18 '14 at 16:51

2 Answers2

1

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.

Erebus
  • 1,998
  • 2
  • 19
  • 32
0

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

Community
  • 1
  • 1
Aziz Saleh
  • 2,687
  • 1
  • 17
  • 27