Using between 2 dates to query data

Question

I have 2 input boxes in a form that I will input beginning date under selDate and end date under selDate2. My table stores the date field in a text field.

<form id="formDate" name="formDate" method="post" action="">
      <p><strong>Date Filter:</strong></br>
        </p>
      <p>
        <label for="selDate"></label>
        <input type="text" name="selDate" id="selDate" 
value="<?php echo $_POST['selDate']; ?>" />
      </p>
      <p>
        <label for="selDate2"></label>
        <input type="text" name="selDate2" id="selDate2" 
value="<?php echo $_POST['selDate2']; ?>"/>
      </p>
      <p>
        <input type="submit" name="go" id="go" value="Submit" />

    </form>

I am using the following statement to list the corresponding rows.

$varDate_Recordset1 = $row_RecordsetLastDate['date'];
if (isset($_POST['selDate'])) {
  $varDate_Recordset1 = $_POST['selDate'];
}
$varDate2_Recordset1 = $row_RecordsetLastDate['date'];
if (isset($_POST['selDate2'])) {
  $varDate2_Recordset1 = $_POST['selDate2'];
}
mysql_select_db($database_port, $port);
$query_Recordset1 = sprintf("SELECT * FROM checkout, `transaction`, school_store 
WHERE `transaction`.activity_id=school_store.Tag 
AND `transaction`.transaction_id=checkout.transaction_id AND (checkout.`date` 
BETWEEN %s AND %s) AND `transaction`.status='1' 
ORDER BY checkout.`date` DESC, `transaction`.id DESC", 
GetSQLValueString($varDate_Recordset1, "text"), 
GetSQLValueString($varDate2_Recordset1, "text"));
$Recordset1 = mysql_query($query_Recordset1, $port) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

I am using BETWEEN both variables in the query. When input the dates I echo blank rows. I have also tried checkout.date BETWEEN CAST(%s AS DATE) AND CAST(%s AS DATE) to no avail. I even tried checkout.date >=%s AND checkout.date <=%s with no luck. Why is it not displaying the data?

does the query return rows when your leave the BETWEEN date part out? — Peter M
– Peter M, Commented Nov 2, 2016 at 18:05
Echo your $query_Recordset1 to see if the query looks like you think it should, and try it on the mysql command line. You should also switch to PDO or mysqli, as the mysql_* functions are removed in PHP7, and deprecated in previous versions. It could possibly help your query. — aynber
– aynber, Commented Nov 2, 2016 at 18:05
I use RecorsetDateLast to display by default the latest date if no date is selected. That echoes the data fine. — user3258571
– user3258571, Commented Nov 2, 2016 at 18:08
In what format do you input your dates? By default they should by formatted as 'Y-m-d' — Peter M
– Peter M, Commented Nov 2, 2016 at 18:14

user3258571 · Accepted Answer · 2016-11-02 19:08:29Z

1

Just for when others search for this information.

the field checkout.date was not of the type DATE or DATETIME and therefor was not showing any results with the current query.

Changing field type resolved the problem. (this however could result in data los so be careful).

End query should be (checkout.date BETWEEN %s AND %s)

edited Nov 2, 2016 at 19:08

user3258571

3844 silver badges18 bronze badges

answered Nov 2, 2016 at 18:58

Peter M

1,0598 silver badges19 bronze badges

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

Comments

aynber · Accepted Answer · 2016-11-02 18:08:32Z

0

Since your date is in a text field, it can't be used as comparison. You have to cast that column as a date first.

(STR_TO_DATE(checkout.`date`, '%Y-%m-%d') BETWEEN %s and %s)

Make sure that all three are in a Y-m-d format, or you may have to convert it.

answered Nov 2, 2016 at 18:08

aynber

23.2k9 gold badges57 silver badges69 bronze badges

7 Comments

user3258571 Over a year ago

Now says query is empty using SELECT * FROM checkout, transaction, school_store WHERE transaction.activity_id=school_store.Tag AND transaction.transaction_id=checkout.transaction_id AND (STR_TO_DATE(checkout.date, '%Y-%m-%d')) BETWEEN %s AND %s

aynber Over a year ago

Between needs to go between the two )) in your query. After the STR_TO_TIME but before closing out the AND.

user3258571 Over a year ago

I am now using (STR_TO_DATE(checkout.date, '%Y-%m-%d') BETWEEN %s AND %s). I still am getting Query was empty.

user3258571 Over a year ago

Query was empty is also showing for (STR_TO_DATE(checkout.date, '%Y-%m-%d') BETWEEN CAST(%s AS DATE) AND CAST(%s AS DATE))

aynber Over a year ago

Query was empty means it's not passing the query into mysql_query. Did you change anything with that line or change $query_Recordset1 other than my suggestion?

|

Collectives™ on Stack Overflow

Using between 2 dates to query data

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related