1

(Yes, I have "thoroughly searched for an answer before asking my question"... but haven't found the "right" answer to my issue. I have read the MySQL man about user variables... I have read the PHP man regarding PDO... still can't grok the solution.)

I'm trying to get from PHP code the same result set returned from this SQL statement that executes correctly and successfully within PhpMyAdmin:

SET @x := 0;

SELECT * 
FROM (

   SELECT 
   `o`.`timestamp`
   `o`.`v_phase_a` ,
   `o`.`v_phase_b` ,
   `o`.`v_phase_c` ,
   (@x := @x+1) AS x
   FROM `operational_parameters` AS o
   WHERE `o`.`timestamp` >= '2014-01-21 05:00:00'
   AND `o`.`timestamp` <= '2014-02-04 04:59:30'
   ORDER BY `o`.`timestamp`

) t
WHERE x MOD 336 = 0;

The rationale for the type of result set is a long story and rooted in my client's business requirements. It's supposed to represent a uniform sampling of data stored as rows inserted 30 seconds apart over a given time span, in this case 2 weeks... this particular statement returns exactly 120 rows of data, which are then packaged by PHP and sent to the UI to be charted with the jqplot plug-in...

Everything else works excellently well, except that within PHP I'm unable to execute multiple SQL statements, a process which I believe would be required to SET my user variable @x, for use in the subsequent SELECT statement. Again, this SQL runs just fine when queried against my database using phpMyAdmin...

I do realize that generally speaking, within PHP, one cannot execute multiple SQL statements in a single query...

Is it possible, however, to create a SINGLE SQL statement - as a string - that will fetch the same result set as that returned by MySQL using the phpMyAdmin tool and the SQL I've listed above? A single statement that I can pass to my newly created PDO inside my PHP and get the right number of rows?

(Seems like PDOs are supposed to be able to handle such a scenario, at least that's what I've gleaned from my study of this board...)

I'm sort of new to PHP/MySQL, so I'm ready to learn. Teach on!

Kei
  • 771
  • 6
  • 17
PHPAcolyte
  • 11
  • 2

1 Answers1

2

You can move the initialization into a subquery:

SELECT *
FROM (SELECT o.timestamp , o.v_phase_a , o.v_phase_b , o.v_phase_c , (@x := @x+1) AS x
      FROM operational_parameters AS o cross join
           (select @x := 0) const
      WHERE o.timestamp >= '2014-01-21 05:00:00' AND o.timestamp <= '2014-02-04 04:59:30'
      ORDER BY o.timestamp
     ) t
WHERE x MOD 336 = 0;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Perfect! I've learned something today. After implementing in PHP, this SQL returns the perfect result set every time. Many, many thanks, to you Mr. Linoff, and to stackoverflow. Frickin' awesome! – PHPAcolyte Feb 05 '14 at 19:05