php - MySql/PHP 简单报表查询

Question

我正在运行一个 MySQL/PHP，我正在尝试显示一个简单的报告来跟踪销售代表何时联系客户。我不知道我做错了什么，因为我是这方面的新手。对我来说最简单的解决方案似乎非常复杂(为每个数字制作一个单独的记录集)。我认为必须有一种更简单的方法。

我希望在一个简单的表格中显示当前周/月/年内建立的联系人数量。见下文。任何帮助将不胜感激。

      |Current|Current|       |
      | Week  | Month | YTD   |
------|-------|-------|-------|
Brian |  7    |   14  |  37   |
------|-------|-------|-------|
Chad  |  0    |   15  |  27   |
------|-------|-------|-------|
David |  11   |   26  |  52   |
------|-------|-------|-------|

当前记录集

mysql_select_db($database_Sales, $Sales);
$query_rsCurWeek = "SELECT Sales.rep, COUNT(*) FROM Sales WHERE YEARWEEK(`date`, 1) = YEARWEEK(CURDATE(), 1) GROUP BY Sales.rep";
$rsCurWeek = mysql_query($query_rsCurWeek, $Sales) or die(mysql_error());
$row_rsCurWeek = mysql_fetch_assoc($rsCurWeek);
$totalRows_rsCurWeek = mysql_num_rows($rsCurWeek);

mysql_select_db($database_Sales, $Sales);
$query_rsCurMonth = "SELECT Sales.rep, COUNT(*) FROM Sales WHERE MONTH(date) = MONTH(CURDATE()) GROUP BY Sales.rep";
$rsCurMonth = mysql_query($query_rsCurMonth, $Sales) or die(mysql_error());
$row_rsCurMonth = mysql_fetch_assoc($rsCurMonth);
$totalRows_rsCurMonth = mysql_num_rows($rsCurMonth);

mysql_select_db($database_Sales, $Sales);
$query_rsCurYear = "SELECT Sales.rep, COUNT(*) FROM Sales WHERE YEAR(date) =         
YEAR(CURDATE()) GROUP BY Sales.rep";
$rsCurYear = mysql_query($query_rsCurYear, $Sales) or die(mysql_error());
$row_rsCurYear = mysql_fetch_assoc($rsCurYear);
$totalRows_rsCurYear = mysql_num_rows($rsCurYear);

当前输出表

<table width="400" border="1" cellspacing="0" cellpadding="0">
<tr>
<th width="175" align="center"></th>
<th width="75" align="center">Current<br />Week</th>
<th width="75" align="center">Current<br />Month</th>
<th width="75" align="center">YTD</th>
</tr>
<?php do { ?>
<tr>
<th align="left"><?php echo $row_rsCurYear['rep']; ?></th>
<td align="center"><?php echo $row_rsCurWeek['COUNT(*)']; ?></td>
<td align="center"><?php echo $row_rsCurMonth['COUNT(*)']; ?></td>
<td align="center"><?php echo $row_rsCurYear['COUNT(*)']; ?></td>
</tr>
<?php } while ($row_rsCurWeek = mysql_fetch_assoc($rsCurWeek)); ?>

Answer 1

try{

  $sql = "SELECT * FROM contacts";   

  $result = $pdo->query($sql);

  if($result->rowCount() > 0){

    echo "<table>";

        echo "<tr>";

            echo "<th>username</th>";

            echo "<th>current_week</th>";

            echo "<th>current_month</th>";

            echo "<th>ytd</th>";

        echo "</tr>";

    while($row = $result->fetch()){

        echo "<tr>";

            echo "<td>" . $row['username'] . "</td>";

            echo "<td>" . $row['current_week'] . "</td>";

            echo "<td>" . $row['current_month'] . "</td>";

            echo "<td>" . $row['ytd'] . "</td>";

        echo "</tr>";

    }
    echo "</table>";
    // Free result set
    unset($result);
  } else{
    echo "No records matching your query were found.";
  }
} catch(PDOException $e){
 die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}