CODEWITHSUNDEEP
phpmysqldate
Sarah Mandana
Sarah Mandana

Reputation: 1513

SQL Query in php not working as required

I have a Database with column name DT, that has values:

  1. 16-05-2015
  2. 16-05-2015
  3. 30-06-2015
  4. 30-06-2015

Sql query in php that I am using is:

    $sql = "Select * from logs WHERE (DT<= '$to') AND (DT>= '$from')  ";

I have also used query:

     $sql = "Select * from logs WHERE (DT BETWEEN '$to' AND '$from')  ";

Both the queries are not functioning in the same way as required.

If $to=31-05-2015 and $ from = any date before 31st, the query displays the 30-06-2015 date as well, but it is not in the range.

When $to=30-05-2015, correct results are displayed but choosing any date after 30-05-2015 does not display the correct range, what could be the reason of it?

Upvotes: 1

Views: 74

Answers (2)

HKumar
HKumar

Reputation: 1624

You have to change your date format to yyyy-mm-dd while saving it to the database and change it to the dd-mm-yyyy while displaying, and also change the datatype of the column to the datetime to make you query work 

$newdate = date("d-m-Y", strtotime($yourdate));

Upvotes: 2

Uri Goren
Uri Goren

Reputation: 13692

apparently, DT is not configured as date in your DB.

And thus the values are ordered lexigoraphically, as strings.

(In lexigoraphical order: 30-... is before 31-..., no matter what ... might be)

You can either change your data to be saved as yyyy-mm-dd strings.

Or configure the column correctly as date

Upvotes: 1

Related Questions