PHP/PDO/MySQL:将多个查询转换为单个查询 [英] PHP/PDO/MySQL: Convert Multiple Queries Into Single Query

问题描述

(是的，我已经在问我的问题之前彻底搜索了答案"...但是没有找到我问题的正确"答案.我已经阅读了有关用户变量的MySQL文章.我已经阅读了有关PDO的PHP手册...仍然无法解决该问题.)

我正在尝试从PHP代码中获得从此SQL语句返回的相同结果集，该结果集可在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;

结果集类型的基本原理由来已久，植根于我客户的业务需求.它应该表示存储在给定时间间隔(在这种情况下为2周)中间隔30秒插入的行的数据的统一采样...该特定语句返回的数据正好是120行，然后由PHP打包并发送到用jqplot插件绘制的用户界面...

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...

除PHP中我无法执行多个SQL语句外，其他所有方法都运行良好，我相信这是设置我的用户变量@x所必需的过程，以便在后续的SELECT语句中使用.再次，当使用phpMyAdmin查询我的数据库时，此SQL运行得很好.

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...

我确实意识到，一般来讲，在PHP中，不能在单个查询中执行多个SQL语句...

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

但是，是否可以创建一个单SQL语句(作为字符串)，该语句将使用phpMyAdmin工具和上面列出的SQL来获取与MySQL返回的结果集相同的结果集?一条语句可以传递给PHP内部新创建的PDO并获得正确的行数?

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?

(似乎PDO应该能够处理这种情况，至少这是我从对本评估板的研究中学到的……)

(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...)

我是PHP/MySQL的新手，所以我准备学习.教吧！

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

推荐答案

您可以将初始化移动到子查询中:

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;

这篇关于PHP/PDO/MySQL:将多个查询转换为单个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！