Date Range Search

Question

hi im having trouble in my date range search cause it fetch wrong result

ex: im searching 04/01/2013 to 04/02/2013 supposedly that the result will show the data from 04/01/2013 to 04/02/2013 these dates but it fetch it wrong data

this is my form

<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>

and here is my search query

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;
                                }

thanks

Answer 1

Change

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

To

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

The name of the datepicker should be end as you have used $_REQUEST["end"] in the date receiving end.

EDIT

Use this query

$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;

If you are using GROUP BY on salesDate field use it this way GROUP BY UNIX_TIMESTAMP(str_to_date(SalesDate, '%d/%m/%Y'))

EDIT 2

Substitute all occurance of

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