php - 从表单字段在 php mysql 中每周报告

Question

我正在尝试通过选择年份和周来生成每周报告。下面是我的代码。我一直工作，直到添加 WHERE 条件。

<form action="" method="POST">
 Select Year:<select name="sortyear" id="sortyear">
                 <option value="2012">2012</option>
                 <option value="2013">2013</option>
                 <option value="2014">2014</option>
                 <option value="2015">2015</option>
             </select>
 Select Week:<select name="week" id="week">
          ...</select>

<?php $sql = mysql_query("SELECT SUM(credit) AS credit, depot, Date_Format(`timestamp`,'%U%Y') AS period FROM transactions where Date_Format(`timestamp`,'%U%Y')='102013' group by Date_Format(`timestamp`,'%U%Y') DESC"); while($row11 = mysql_fetch_array( $sql )) 
   {
       $credit = $row11['credit'].'<br>';
       $depot = $row11['depot'].'<br>';
       $period = $row11['period'].'<br>';
   }

$sortyear = $_REQUEST['sortyear'];
$week = $_REQUEST['week'];?>     

请帮忙。

Answer 1

您的查询效率将极其低下。您正在获取 mysql 日期/时间字段，将其转换为字符串，然后使用该字符串进行比较。这排除了索引的任何使用。

为什么不简单

SELECT ...
...
WHERE (YEAR(timestamp) = 2013) AND (MONTH(timestamp) = 10)
GROUP BY YEAR(timestamp), MONTH(timestamp)

？