PHP MYSQL Date Range Search with Jquery Datepicker

Question

I've tried a few different solutions to my problem but I can't seem to find one that works. I am trying to search a date range from a datetime field in my mysql database. Here's my jquery before the form:

  <script type="text/javascript" language="javascript">
            jQuery(function() {
                jQuery( "#from" ).datepicker({
                  defaultDate: "+1w",
                  changeMonth: true,
                  numberOfMonths: 1,
                  dateFormat: "yy-mm-dd",
                  onClose: function( selectedDate ) {
                    $( "#to" ).datepicker( "option", "minDate", selectedDate );
                  }
                });
                jQuery( "#to" ).datepicker({
                  defaultDate: "+1w",
                  changeMonth: true,
                  numberOfMonths: 1,
                  dateFormat: "yy-mm-dd",
                  onClose: function( selectedDate ) {
                    jQuery( "#from" ).datepicker( "option", "maxDate", selectedDate );
                  }
                });
            });
</script>

Heres my simple form:

<form method="post" action="search.php">
<p>Select a date range: </p><label style="color:#FFF;" for="from">From</label>
From:<input type="text" id="from" name="from" />
<label style="color:#FFF;" for="to" >to</label>
To:<input type="text" id="to" name="to" />
<input name="export" type="submit" value="Search" />
</form>

And here's my sql statement:

        $hostname = ""; //SET SERVER/HOSTNAME
    $dbusername = ""; //SET DATABASE USERNAME
    $dbname = ""; //SET DATABASE NAME
    $dbpassword = ""; //SET DATABASE USERNAME
    $link = mysqli_connect($hostname, $dbusername, $dbpassword, $dbname); 
    if (!$link) { 
    die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error()); 
    } 
$sql = "SELECT * FROM log WHERE call_date >= DATE_FORMAT('" . $from . "', '%Y%m%d') AND call_date <=  DATE_FORMAT('" . $to . "', '%Y%m%d')";
//$sql = "SELECT * FROM log WHERE call_date >= '".date("Y-m-d", strtotime($from))."' AND call_date <=  '".date("Y-m-d", strtotime($to))."'";  

//$sql = "SELECT * FROM log WHERE call_date >= '$from' AND call_date <=  '$to'"; 
    $result = mysqli_query($link, $sql, MYSQLI_STORE_RESULT);
while($row = $result->fetch_assoc()){
        $name= $row['name'];
        $disposition = $row['did_id'];
        $date = $row['call_date'];
}

...then outputting my results. You can see I tried a few different sql statements that i've commented out. I know I can connect to my database, I know I am getting the dates in the format yyyy-mm-dd, and the datetime field has the format yyyy-mm-dd hh:mm:ss. Any help is greatly appreciated. Thank you.

Maher.z · Accepted Answer · 2013-10-06 16:28:17Z

1

Change

   $sql = "SELECT * FROM log WHERE call_date >= DATE_FORMAT('" . $from . "', '%Y%m%d') AND call_date <=  DATE_FORMAT('" . $to . "', '%Y%m%d')";

To

   $sql = "SELECT * FROM log WHERE call_date >= DATE_FORMAT('" . $from . "', '%Y/%m/%d') AND call_date <=  DATE_FORMAT('" . $to . "', '%Y/%m/%d')";

answered Oct 6, 2013 at 16:28

Maher.z

261 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

dkeeper09 Over a year ago

beautiful. You are awesome.

zoranc · Accepted Answer · 2014-03-11 07:57:24Z

0

jQuery return format dateFormat: "yy-mm-dd"

DATE_FORMAT('" . $to . "', '%Y%m%d')";
Change to
DATE_FORMAT('" . $to . "', '%Y-%m-%d')";

If you define without time, MySQL will automatic define as 00:00:00.

So, If you select date 2000-01-02, MySQL will select 2000-01-02 00:00:00

This is the example of selecting date:
$sql = "SELECT * FROM log WHERE call_date >= DATE_FORMAT('{$from}', '%Y-%m-%d') AND call_date <= DATE_FORMAT('{$to} 23:59:59', '%Y-%m-%d %H:%i:%s')";

edited Mar 11, 2014 at 7:57

zoranc

2,4541 gold badge22 silver badges34 bronze badges

answered Mar 11, 2014 at 7:38

Clement

171 silver badge4 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL Date Range Search with Jquery Datepicker

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related