mysql查询中的php循环

Question

我有一个像这样的大mysql查询:

SELECT u.userid AS `User`
  , SUM(CASE WHEN activitydate='2011-07-01' THEN round(time/60) ELSE 0 END) AS `2011-07-01`
  , SUM(CASE WHEN activitydate='2011-07-02' THEN round(time/60) ELSE 0 END) AS `2011-07-02`
.......
  , SUM(CASE WHEN activitydate='2011-07-30' THEN round(time/60) ELSE 0 END) AS `2011-07-30`
FROM hoursbase h
  JOIN person u
    ON h.userid = u.id
WHERE h.activitydate BETWEEN '2011-07-01' AND '2011-07-30'
GROUP BY h.userid
ORDER BY h.userid

有什么方法可以使用 php 将上述查询放入循环中。

此外，我尝试添加一个下拉菜单，选择后，相应的月份将在查询中更新。

问候，
钱德鲁。

Answer 1

如果您不介意查询具有不同的输出格式，您可以像这样重写它:

SELECT u.userid AS `User`
   ,activitydate
   ,sum(round(ifnull(time,0)/60)) as timetaken
FROM hoursbase h
JOIN person u ON h.userid = u.id 
WHERE h.activitydate BETWEEN :startdate AND :enddate  /*note the : params*/ 
GROUP BY h.userid, h.activitydate 
ORDER BY h.userid, h.activitydate

这将首先返回按 userid 分组的数据，然后按 activitydate 分组。
它还会运行得快得多。
最后，在 php 中获取每个用户每个日期的结果会更容易。
当您更改月份时，您不必更改列数。

以下是如何在 php 中使用循环来循环它:
我复制了这个答案的代码:How do I loop through a MySQL query via PDO in PHP?

// $attrs is optional, this demonstrates using persistent connections, 
// the equivalent of mysql_pconnect 
$attrs = array(PDO::ATTR_PERSISTENT => true);  

// connect to PDO 
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "password", $attrs);
// the following tells PDO we want it to throw Exceptions for every error. 
// this is far more useful than the default mode of throwing php errors 
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
// prepare the statement. the place holders allow PDO to handle substituting 
// the values, which also prevents SQL injection 
$stmt = $pdo->prepare("SELECT u.userid AS `User`.....  ");  
// bind the parameters 
$stmt->bindValue(":startdate", "2011-07-01"); 
$stmt->bindValue(":enddate", "2011-07-31");  
// initialise an array for the results  
$products = array(); 
if ($stmt->execute()) {   
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {      
    //do more useful stuff here
    //escape all fields that can be entered by users using htmlspecialchars
    //to prevent XSS exploits.
    echo htmlspecialchars($row['User']);
    echo htmlspecialchars($row['activitydate']);
    echo $row['timetaken'];
  } 
}  
// set PDO to null in order to close the connection 
$pdo = null; 

关于htmlspecialchars()
您需要转义用户可以输入并输出到屏幕的所有字符串字段。
在这种情况下，我转义了 userid 和 activitydate 因为我只有 95% 确定这些是整数和日期字段，如果我 100% 确定，我会跳过转义，但是如果我不是，我就必须逃跑。

链接:
How to escape output in PHP
How do I loop through a MySQL query via PDO in PHP?