CODEWITHSUNDEEP
phpmysqldate
Bill Teale
Bill Teale

Reputation: 169

query between range of dates not showing results

I have a mysql database with a field called DATE, storing data as a date. I am trying to get the php date to select records for the next 31 days from the current date.

This is what I have...

$start_THISMONTH = "-1";
if (isset($to_date)) {
$start_THISMONTH = $to_date;
}
$finish_THISMONTH = "-1";
if (isset($from_date)) {
$finish_THISMONTH = $from_date;
}
mysql_select_db($database_WHTSON, $WHTSON);
$query_THISMONTH = sprintf("SELECT * FROM CALENDAR WHERE DATE BETWEEN %s AND %s AND       APPROVED = 1 ORDER BY DATE ASC", GetSQLValueString($start_THISMONTH, "date"),GetSQLValueString($finish_THISMONTH, "date"));
$THISMONTH = mysql_query($query_THISMONTH, $WHTSON) or die(mysql_error());
$row_THISMONTH = mysql_fetch_assoc($THISMONTH);
$totalRows_THISMONTH = mysql_num_rows($THISMONTH);

-

The code to set up the two variables is 

$from_date = date("Y-m-j");
$to_date = date('Y-m-j', strtotime("+31 days"));

And my php code in the body is

  <h4><strong><font color="#FF0000"><?php echo $row_THISMONTH['EVENT_NAME']; ?></font></strong></h4>
  <?php $date = date_format($row_THISMONTH['DATE'], 'jS F'); ?>
  <h5><em><?php echo $date; ?>,  <?php echo $row_THISMONTH['TIMES']; ?><br />
    <?php echo $row_THISMONTH['LOCATION_ADDRESS']; ?>, <?php echo $row_THISMONTH['LOCATION_TOWN']; ?> <?php echo $row_THISMONTH['LOCATION']; ?></em><br />
  </h5>
  <p><?php echo $row_THISMONTH['EVENT_DETAILS']; ?><br />
  </p>

No results are showing up. This is a new build database, and only one record is in there, with a date of Valentines Day. If I change the code to a simple "find all records" query it shows up great (though the date_format doesn't display a date.

This is my nemesis, please help me understand what I've done wrong?

Upvotes: 0

Views: 89

Answers (2)

Bill Teale
Bill Teale

Reputation: 169

I have been staring at this for so long, but I think I have found one answer. I tried to be logical and use $to_date and $from_date, and then put them in the wrong order in the query. So although I haven;t actually solved the issue, I thinnk the issue is my untidyness on this occasion. I learnt a lot from the discussion too, thank you very much - time for a cleen sheet and a slower pace :)

Upvotes: 0

Nouphal.M
Nouphal.M

Reputation: 6344

If you have a date field column in mysql(YYYY-MM-DD) then try date('Y-m-d') instead of date('Y-m-j')

Upvotes: 1

Related Questions