user3258571 user3258571 - 1 month ago 7
MySQL Question

Using between 2 dates to query data

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?

Answer

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)

Comments