CODEWITHSUNDEEP
phpmysql
wobsoriano
wobsoriano

Reputation: 13462

PHP MySQL Get data between dates

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.

enter image description here

Upvotes: 0

Views: 1482

Answers (3)

Matthew Goulart
Matthew Goulart

Reputation: 3065

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

Upvotes: 1

Mihai
Mihai

Reputation: 26804

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.

Upvotes: 1

Chin Leung
Chin Leung

Reputation: 14941

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

Upvotes: 1

Related Questions