user3258571 user3258571 - 1 year ago 73
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>
<label for="selDate"></label>
<input type="text" name="selDate" id="selDate"
value="<?php echo $_POST['selDate']; ?>" />
<label for="selDate2"></label>
<input type="text" name="selDate2" id="selDate2"
value="<?php echo $_POST['selDate2']; ?>"/>
<input type="submit" name="go" id="go" value="Submit" />


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.
BETWEEN CAST(%s AS DATE) AND CAST(%s AS DATE) to no avail. I even tried checkout.
>=%s AND checkout.
<=%s with no luck. Why is it not displaying the data?

Answer Source

Just for when others search for this information.

the field 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 ( BETWEEN %s AND %s)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download