MySQL SET with PDO Connection

Question

I'm attempting to execute a very long SQL statement that selects the "Top N" rows using a number of user options. I do not wish to post the whole statement here as it is very long but I am using MySQL variables as described in this post : MySql Query: Select top 3 rows from table for each category

The statement works fine when run on the MySQL command line BUT the issue I am having is that the whole query has to be run through a PDO connection and I wondered if someboody could confirm that I cannot set values 'on-the-fly' as illustrated below. Is this a PDO limitation? Have I just wasted a few hours? Many thanks for your help ...

SELECT outerseasons.* FROM (SELECT seasons.CountryName, seasons.idCountry, '' as     idCountryGroup, '' as CGName, seasons.DIValue_WorkingEntered, 
      seasons.SNName, seasons.idSeason, seasons.idParameter, seasons.PMName,
      CASE 
        WHEN @idSeason != seasons.idSeason OR @idParameter != seasons.idParameter
        THEN @rownum := 1
        ELSE @rownum := @rownum + 1
      END AS rank,
      @idSeason := seasons.idSeason as var_season,
      @idParameter := seasons.idParameter as var_parameter
      FROM ( SELECT ...

No, it's written in native MySQL. Not my fault and no way around it... :( — Charles Jackson, Apr 17 '13 at 11:28

score 0 · Answer 1 · edited May 23 '17 at 12:04

0

PDO has noting to do with SQL syntax. It can execute whatever SQL query you wrote.
The only trouble can be caused by set of queries. It can be easily solved by splitting these sets into separate queries and running them one by one.

Whatever else trouble can be caused by SQL syntax error only.
Just verify your SQL syntax and watch PDO errors

edited May 23 '17 at 12:04

Community

1
1

answered Apr 17 '13 at 11:52

Your Common Sense

156,878
40
214
345

Thanks for your feedback, greatly appreciated. Can you please elaborate on " It can be easily solved by splitting these sets into separate queries and running them one by one" ? Do you mean running each SELECT as a separate query? And how do I combine them again (if that makes sense). Forgive my ignorance. – Charles Jackson Apr 17 '13 at 12:06
I mean unning each query as a separate query. if you have no `;` symbol in your SQL, don't bother then - it's not the case. – Your Common Sense Apr 17 '13 at 12:08

MySQL SET with PDO Connection

1 Answers1