Select mySQL based only on month and year

I have a column in my mySQL DB that has some rows. One of this row is a DATE, like this: 2012-02-01

What I want to achieve is to do a SELECT with PHP based only on the year and month.

The logic of the SELECT will be the following:

$q="SELECT * FROM projects WHERE Date="SELECT HERE THE SPECIFIC YEAR AND MONTH"";

The specific month and year will be be passed from a $_POST variable, like this $_POST['period']="2012-02";

How can I do it?

Solution 1:

SELECT * FROM projects WHERE YEAR(Date) = 2011 AND MONTH(Date) = 5

Solution 2:

If you have

$_POST['period'] = "2012-02";

First, find the first day of the month:

$first_day = $_POST['period'] . "-01"

Then this query will use an index on Date if you have one:

$q = "
    SELECT *  
    FROM projects 
    WHERE Date BETWEEN '$first_day' 
                   AND LAST_DAY( '$first_day' )
     " ;

One could also use inclusive-exclusive intervals, which work pretty good (you don't have to worry if the column is DATE, DATETIME or TIMESTAMP, nor about the precision:

$q = "
    SELECT *  
    FROM projects 
    WHERE Date >= '$first_day' 
      AND Date  < '$first_day' + INTERVAL 1 MONTH 
     " ;

Security warning:

You should properly escape these values or use prepared statements. In short, use whatever method is recommended these days in PHP, to avoid any SQL injection issues.

Solution 3:

Here you go. Leave the computing to PHP and save your DB some work. This way you can make effective use of an index on the Date column.

<?php
    $date = $_POST['period'];

    $start = strtotime($date);
    $end   = strtotime($date . ' 1 month - 1 second');

    $query = sprintf(
        'SELECT *
           FROM projects
          WHERE Date BETWEEN FROM_UNIXTIME(%u) AND FROM_UNIXTIME(%u)',
        $start,
        $end
    );

EDIT
Forgot the Unix timestamp conversion.