SQL Query in php not working as required

Question

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?

HKumar · Accepted Answer · 2015-05-23 05:55:46Z

2

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

answered May 23, 2015 at 5:55

HKumar

1,6743 gold badges14 silver badges29 bronze badges

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

Comments

Uri Goren · Accepted Answer · 2015-05-23 05:43:07Z

1

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

answered May 23, 2015 at 5:43

Uri Goren

13.8k8 gold badges62 silver badges113 bronze badges

5 Comments

Sarah Mandana Over a year ago

But Format of date is yyyy-mm-dd which I don't want as further I have to write the date's values in the excel file and excel writes this format as ####, excel only writes dd-mm-yyyy correctly

Uri Goren Over a year ago

You need to separate the two concepts, the way you store you're data should not be the same as the way you display your data

Sarah Mandana Over a year ago

My php code is taking values from database in a loop and then storing in excel file one by one, I don't know how to change one specific column's data format there

Uri Goren Over a year ago

You can change the display format with date("d-m-Y", strtotime($r['DT'])); as @Susain suggested, or other string-manipulation techniques.

Sarah Mandana Over a year ago

Thanks for figuring out the problem.

Collectives™ on Stack Overflow

SQL Query in php not working as required

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related