php - 日期范围搜索

Question

嗨，我在日期范围搜索中遇到问题，因为它获取了错误的结果

例如:我搜索 04/01/2013 到 04/02/2013 据说结果将显示从 04/01/2013 到 04/02/2013 这些日期的数据，但它获取了错误的数据

这是我的表格

<form  method="post"id="myform" action="index.php" >
        <label for="from">Sales Date from :</label>
        <input name="startfrom" type="text" id="startfrom" class="datepicker form-control"/>
        <label for="to">Sales Date to :</label>
        <input name="to" type="text" id="end" class="datepicker form-control"/>
        <label>Outlet Name:</label>
        <select name="OutletName" class="input-sm form-control">
            <option value="">--</option>
            <?php
            error_reporting(0);
            include 'config.php';
                $sql = "SELECT * FROM ".$SETTINGS["data_table"]." GROUP BY OutletName ORDER BY OutletName";
                $sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
                while ($row = mysql_fetch_assoc($sql_result)) {
                    echo "<option value='".$row["OutletName"]."'".($row["OutletName"]==$_REQUEST["OutletName"] ? " selected" : "").">".$row["OutletName"]."</option>";
                }
            ?>
        </select>
        <label>Category:</label>
        <select name="Category" class="input-sm form-control ">
            <option value="">--</option>
            <?php
            error_reporting(0);
            $sql = "SELECT * FROM ".$SETTINGS["data_table"]." GROUP BY Category ORDER BY Category";
                $sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
                while ($row = mysql_fetch_assoc($sql_result)) {
                    echo "<option value='".$row["Category"]."'".($row["Category"]==$_REQUEST["Category"] ? " selected" : "").">".$row["Category"]."</option>";
                }
            ?>
        </select></br>
        <label>Product Code:</label>
        <select name="ProductCode" class="input-sm form-control ">
            <option value="">--</option>
            <?php
            error_reporting(0);
            $sql = "SELECT * FROM ".$SETTINGS["data_table"]." GROUP BY ProductCode ORDER BY ProductCode";
                $sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
                while ($row = mysql_fetch_assoc($sql_result)) {
                    echo "<option value='".$row["ProductCode"]."'".($row["ProductCode"]==$_REQUEST["ProductCode"] ? " selected" : "").">".$row["ProductCode"]."</option>";
                }
            ?>
        </select></br>
        <button type="submit" class="btn  btn-primary btn-block" name="submit" id="submit"  />FIND&nbsp;<span class="glyphicon glyphicon-search"></span></button>

这是我的搜索查询

if ($_REQUEST["Category"]<>'') {
                                    $search_Category = " AND Category='".mysql_real_escape_string($_REQUEST["Category"])."'";   
                                }
                                if ($_REQUEST["ProductCode"]<>'') {
                                    $search_ProductCode = " AND ProductCode='".mysql_real_escape_string($_REQUEST["ProductCode"])."'";  
                                }
                                if ($_REQUEST["OutletName"]<>'') {
                                    $search_OutletName = " AND OutletName='".mysql_real_escape_string($_REQUEST["OutletName"])."'"; 
                                }

                                $search_groupby = "GROUP BY CategoryID,OracleCategory,ProductCode ORDER BY CategoryID,OracleCategory,ProductCode ";

                                if ($_REQUEST["startfrom"]<>'' and $_REQUEST["end"]<>'') {
                                    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE SalesDate >= '".mysql_real_escape_string($_REQUEST["startfrom"])."'AND SalesDate <='".mysql_real_escape_string($_REQUEST["end"])."'".$search_OutletName.$search_Category.$search_ProductCode;
                                } else if ($_REQUEST["startfrom"]<>'') {
                                    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE SalesDate >= '".mysql_real_escape_string($_REQUEST["startfrom"])."'".$search_OutletName.$search_Category.$search_ProductCode;
                                } else if ($_REQUEST["end"]<>'') {
                                    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE SalesDate <= '".mysql_real_escape_string($_REQUEST["end"])."'".$search_OutletName.$search_Category.$search_ProductCode;
                                }else {
                                    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE ECRNo>0 ".$search_OutletName.$search_Category.$search_ProductCode;
                                }

谢谢

Answer 1

改变

<input name="to" type="text" id="end" class="datepicker form-control"/>

至

<input name="end" type="text" id="end" class="datepicker form-control"/>

日期选择器的名称应为 end，因为您在日期接收端使用了 $_REQUEST["end"]。

编辑

使用此查询

$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE UNIX_TIMESTAMP(str_to_date(SalesDate, '%d/%m/%Y')) >= '".strtotime(mysql_real_escape_string($_REQUEST["startfrom"]))."' AND UNIX_TIMESTAMP(str_to_date(SalesDate, '%d/%m/%Y'))<='".strtotime(mysql_real_escape_string($_REQUEST["startfrom"]))."'".$search_OutletName.$search_Category.$search_ProductCode;

如果您在 salesDate 字段上使用 GROUP BY，请按以下方式使用 GROUP BY UNIX_TIMESTAMP(str_to_date(SalesDate, '%d/%m/%Y'))

编辑2

替换所有出现的

SalesDate 到 UNIX_TIMESTAMP(str_to_date(SalesDate, '%d/%m/%Y'))
和
$_REQUEST["startfrom"] 到
strtotime(mysql_real_escape_string($_REQUEST["startfrom"]))
和
$_REQUEST["end"] 至
strtotime(mysql_real_escape_string($_REQUEST["startfrom"]))