PHP MySQL Get data between dates

Question

I have a simple code that I can't get to work. I'm trying to get all the data between selected dates but it isn't working. Here are my codes:

form

 <form action="selectedInvoices.php" method="POST">
  <div class="row">
    <div class="large-4 columns">
      <label>From
        <input type="text" class="datepicker" name="from" />
      </label>
    </div>
    <div class="large-4 columns">
      <label>To
        <input type="text" class="datepicker" name="to" />
      </label>
    </div>
    <div class="large-4 columns">
      <button class="button" type="submit">Show Invoice</button>
    </div>
  </div>
  </form>

selectedInvoices.php

$fromOrig = strtotime($_POST['from']);
$toOrig = strtotime($_POST['to']);

$from = date('Y-m-d', $fromOrig);
$to = date('Y-m-d', $toOrig);

$sql = mysql_query("SELECT * FROM allinvoices WHERE acc_date BETWEEN '".$from."' AND '".$to."'");
while($r = mysql_fetch_assoc($sql)) {
$drno = $r['drno'];
$name = $r['name'];
$amount = $r['amountdue'];

The data type of my acc_date field is varchar which I guess is wrong here. The format of the date when I insert is m-d-Y.

What should I do to make the code work? Thank you in advance.

"The data type of my acc_date field is varchar which I guess is wrong here." Well thats itself is wrong there. how then to compare date ? — rahul
– rahul, Commented Mar 4, 2016 at 13:45
@Plum is there a way that I can keep the data type and do the conversion in my code? — wobsoriano
– wobsoriano, Commented Mar 4, 2016 at 13:48
WHERE STR_TO_DATE(acc_date, 'Y-m-D') Change the format to what you need. — Mihai
– Mihai, Commented Mar 4, 2016 at 13:51
@Mihai so I do not need to do the strtotime function anymore? — wobsoriano
– wobsoriano, Commented Mar 4, 2016 at 13:52

Mihai · Accepted Answer · 2016-03-04 16:01:52Z

1

WHERE STR_TO_DATE(acc_date, '%Y/%m/%d')  BETWEEN '".$from."' AND '".$to."'

Fiddle

Date in mysql is Y-m-d,you were comparing with d-m-Y for BETWEEN.Also make sure $from is < than $to.

edited Mar 4, 2016 at 16:01

answered Mar 4, 2016 at 15:29

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

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

4 Comments

wobsoriano Over a year ago

With my code above, should I just change my query with your code?

Mihai Over a year ago

@FewFlyBy If this doesnt work vardump $from and $to and edit your question with the results.

wobsoriano Over a year ago

Hey btw, the dates in my database is Y/m/d

wobsoriano Over a year ago

This is the exact code I need. Thank you very much, sir!

Chin Leung · Accepted Answer · 2016-03-04 14:33:28Z

1

Since you said your date format in the database is m-d-Y, you could simply change your $toand $from to the appropriate format.

Replace

$from = date('Y-m-d', $fromOrig);
$to = date('Y-m-d', $toOrig);

With

$from = date('m-d-Y', $fromOrig);
$to = date('m-d-Y', $toOrig);

And for your query, you can replace the BETWEEN with

acc_date >= $from && acc_date <= $to

answered Mar 4, 2016 at 14:33

Chin Leung

15k4 gold badges38 silver badges64 bronze badges

1 Comment

wobsoriano Over a year ago

I still get empty data

Matthew Goulart · Accepted Answer · 2016-03-04 16:04:30Z

1

I recently worked on something similar.

I used the less than and more than operators, so

... WHERE startDate > $date AND endDate < $date

Also ensure that your date fields in the DB are actually proper date type fields. Make sure that when you insert a date, it's formatted correctly( IE. date type may be yyyy-mm-dd.) Otherwise the DB won't read it correctly.

And just a side note, you can do away with the concatenation in your query by changing the surrounding quotes to single (the quotes around the whole query)

 BETWEEN '".$from."' AND '".$to."'");

can become

BETWEEN $from AND $to');

answered Mar 4, 2016 at 16:04

Matthew Goulart

3,0845 gold badges34 silver badges74 bronze badges

1 Comment

wobsoriano Over a year ago

Thanks for the effort!

Collectives™ on Stack Overflow

PHP MySQL Get data between dates

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related