CODEWITHSUNDEEP
phpmysql
Codezzz
Codezzz

Reputation: 245

Fail to select the FROM date correctly in MYSQL statement

  $from = "2017-7-1";
  $to = "2017-8-25";

I don't know why i cant seems to select the from date in the HTML5 date picker correctly, for instance when i pick 2017-7-1, the result page wont show the result data on 2017-7-1 , but when i pick 2017-6-30 which is ONE day BEFORE, it can show. Any idea why? I have tried both the BETWEEN CLause and the >= AND <=, They both dont work Any idea why? Thanks

  $result = mysql_query("SELECT * FROM treatmentdetail WHERE nextdate BETWEEN 
  '$from' AND '$to'");

  $result = mysql_query("SELECT * FROM treatmentdetail WHERE nextdate >= 
  '$from' AND nextdate <= '$to'");

Upvotes: 0

Views: 36

Answers (3)

Michael
Michael

Reputation: 566

Change your field to DATE and save your date as YYYY-MM-DD like in you example

  $from = "2017-07-01";
  $to = "2017-08-25";

You can do then your search correctly with mysql(i)

SELECT * FROM treatmentdetail 
    WHERE 
        nextdate >= '$from' AND nextdate <= '$to';

Have a look on the docs

MySQL DATATYPES DATE

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

You should convert properly to date 

   SELECT * 
   FROM treatmentdetail 
   WHERE str_to_date(nextdate, '%Y-%m-%d') 
      BETWEEN str_to_date('$from','%Y-%m-%d' ) AND  str_to_date('$to','%Y-%m-%d' )

and you should use most recent mysql driver .. with param binding too

Upvotes: 1

Farsay
Farsay

Reputation: 312

You are missing connection variable I think you will have to use connection variable.

$result = mysqli_query($conn,"SELECT * FROM treatmentdetail WHERE nextdate BETWEEN 
  '$from' AND '$to'") or die(mysqli_error($conn));

Upvotes: 0

Related Questions