Searching MySQL table for date range ...

[bsamson]

I have a MySQL table structured like so:

 

id

entrydate

location

morningTraffic

afternoonTraffic

eveningTraffic

 

The entrydate field is stored in MySQL date format

Like so: 2008/03/08

 

I have a form requesting this info:

MONTH / LOCATION

 

I used the following php to list the current and 2 previous months:

<?php
  // Get last 3 months for search criteria
  $mnth1 = date("F");
  $mnth2 = date("F",strtotime("-1 month"));
  $mnth3 = date("F",strtotime("-2 months"));
?>

 

Then I have this HTML/PHP displaying the info:

<select size="1" name="month">
<option value="<? echo $mnth1; ?>"><? echo $mnth1; ?></option>
<option value="<? echo $mnth2; ?>"><? echo $mnth2; ?></option>
<option value="<? echo $mnth3; ?>"><? echo $mnth3; ?></option>
</select>

 

How do I create a query to search the date field for the month only?? Any ideas would be greatly appreciated! Thanks.

 

Best Regards,

bsamson

[paul2463]

try this

$query = "SELECT DATE_FORMAT(entrydate, '%c')";

 

would pull the numeric month of the datefield from the database

[PFMaBiSmAd]

To query your database for entrydate's with a month value that matches what was entered (once you have more than a year of data, you need to include the current year as well) use the mysql MONTH() function -

 

$month = 3; // get the desired numeric month value - actual code up to you
$query = "SELECT * FROM your_table WHERE MONTH(entrydate) = $month";

 

Ref - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_month