在用户输入值sql的日期之间

Question

I have a table that has a column called hdate. I have set this withe the date property.

I have a form :

 <H2>Search By Date</H2></div>
  <form name="attdate" action="datesearch.php" method="post">
      From :<input id="datepicker" name = "startd">   To: <input id="datepicker1" name = "endd">
    <input type="submit" value="Search Records">

Now my intention is to allow the user to enter a start date and end date and obtain all the rows with these date.

<?php


include 'config.php';

$startd = ($_POST['startd']);
$endd = ($_POST['endd']);


$startd = mysqli_real_escape_string($con,$startd);
$endd = mysqli_real_escape_string($con,$endd);

$sql = "SELECT * FROM handover WHERE hdate  AND  >= '" . $startd . "' AND  <'"     

.$endd.  "' + ‘ 23:59:59’"
;
$result = mysqli_query($con,$sql);

$count=mysqli_num_rows($result);

if($count==0 ){

 echo "</br></br></br></br></br></br></br><p> No Matching results found</p>";
}
else{
while($row = mysqli_fetch_array($result)) {
 echo 'here are your results';

My dates are going in to the database for hdate and in the same format set by the datepicker.

I am successfully getting results querying other columns. Any ideas? note time added after reading on a couple of sites. I have read other questions on stacked but they do not relate to user input.

When testing with the current set up I am not getting errors just the "No matches so I guess the query is valid but not set up right to query my hdate column.

I can change column easily so if the simplest solution is to use timestamp or something then it okay to do so.

<?php


include 'config.php';

$startd = ($_POST['startd']);
$endd = ($_POST['endd']);


$startd = mysqli_real_escape_string($con,$startd);
$endd = mysqli_real_escape_string($con,$endd);

$startd = '2014-11-18';
$endd = '2014-11-20';
$sql = "SELECT * FROM handover WHERE hdate >= date('" . $startd . "') AND hdate < 

ADDDATE(date('" . $endd . "'), INTERVAL 1 DAY)";
echo $sql; // run in mys

$result = mysqli_query($con,$sql);

$count=mysqli_num_rows($result);

if($count==0 ){

 echo "</br></br></br></br></br></br></br><p> No Matching results found</p>";
}
else{
while($row = mysqli_fetch_array($result)) {

Im still just getting a "no matches " echo

Answer 1

Assuming that $startd and $endd are in your basic MySQL date format of YYYY-MM-DD, then:

$sql = "SELECT * FROM handover WHERE hdate >= date('" . $startd . "') AND hdate < ADDDATE(date('" . $endd . "'), INTERVAL 1 DAY)";

Otherwise, you'll probably have to perform some string operations to reformat $startd and $endd into YYYY-MM-DD before using the above.

My tip for how to debug this stuff in general would be hardcode some values in the variables and print the SQL out, then run it in the console and mess with it until you can get it to work as expected:

$startd = '2014-11-18';
$endd = '2014-11-20';
$sql = "SELECT * FROM handover WHERE hdate >= date('" . $startd . "') AND hdate < ADDDATE(date('" . $endd . "'), INTERVAL 1 DAY)";
echo $sql; // run in mysql console and see how it works before moving forward in php

Then after that works, upgrade to using the POST parameters and printing the SQL out, and you'll know whether it looks right or not based on whether it matches what was working before.

Please note your SQL syntax has been corrected above, as you had AND twice where its only needed once, and you were missing the reference to hdate after your second AND.