php - 通过日期过滤获取出现次数

Question

你好，我有这张 table

table name : addresses


|  id  |branch |      datetime      |
____________________________________

|16875 |north  | 2015-07-13 02:11:34|
|16345 |north  | 2015-07-11 08:04:42|
|16000 |north  |2015-07-10 08:16:07 |
|16960 |north  |2015-07-13 05:16:04 |
|15909 |north  |2015-07-10 05:16:05 |
|16669 |south  |2015-07-12 07:16:03 |
|16513 |south  |2015-07-12 00:20:43 |
|16699 |south  |2015-07-12 08:16:02 |
|15939 |east   |2015-07-10 06:16:05 |
|16449 |east   |2015-07-11 11:16:04 |
|16517 |east   |2015-07-12 01:16:01 |
|16729 |east   |2015-07-12 09:16:02 |
|16418 |west   |2015-07-11 10:18:16 |
|15971 |west   |2015-07-10 07:16:04 |
|16785 |west   |2015-07-12 11:16:01 |
|16757 |west   |2015-07-12 10:16:02 |
|16353 |west   |2015-07-11 08:16:04 |
|16877 |west   |2015-07-13 02:16:03 |

在我的 php 页面上，我想查询此表，但只显示重复值的计数，因为我有日期时间列，我想选择日期并显示该日期的重复值。

例如我选择日期

2015-07-13

所以我会查询

|Branch|count|
______________
|north |  2  |
|west  |  1  |
______________

另一个例子是我想选择日期是

2015-07-10它将查询

|Branch|count|
______________
|north |  2  |
|east  |  1  |
|west  |  1  |
______________

在我的 php 页面上我使用此代码

   <table cellpadding="0" cellspacing="0" border="0" id="table">
        <thead>
            <tr>
                <th><h3>Branch</h3></th>
                <th><h3>Count</h3></th>



            </tr>
        </thead>
        <tbody>
            <?php
// Connect to database server
mysql_connect("localhost", "user", "user") or die (mysql_error ());

// Select database
mysql_select_db("data") or die(mysql_error());

// SQL query
$strSQL  = "SELECT branch,count(branch) as occurence FROM `addresses` WHERE datetime >= \"2015-07-10\" AND datetime < \"2015-07-11\" group by branch";

// Execute the query (the recordset $rs contains the result)
$rs = mysql_query($strSQL);

// Loop the recordset $rs
while($row = mysql_fetch_array($rs)) {

   // W
    echo"<tr>";

    echo"<td>". $row['branch'];
    echo"<td>". $row['occurence'];





  }

// Close the database connection
mysql_close();
                    ?>

我的输出是

|Branch|count|
______________
|north |  2  |
|east  |  1  |
|west  |  1  |
______________

那么如何在我的 php 页面上集成日期过滤呢？计算重复值。

谢谢

Answer 1

您可以将开始和结束日期作为参数传递给查询:

// Connect to database
$dbh = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');

// Prepare statement
$stmt = $dbh->prepare("SELECT branch, count(branch) AS occurence
                        FROM `addresses`
                        WHERE datetime >= :from AND datetime < :to
                        GROUP BY branch");

// Bind parameters
$stmt->bindParam(':from', $from);
$stmt->bindParam(':to',   $to);

// Execute
$from = '2015-07-10';
$to   = '2015-07-11';
$stmt->execute();

// Fetch result
$result = $stmt->fetchAll();

请参阅PHP documentation on Prepared Statements了解更多信息。