Alex Rivera | Logout

Select mySQL based only on month and year

Asked 2012-02-01T23:02:20.757
119

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?

php mysql
Edit
Report

3 Answers

234
SELECT * FROM projects WHERE Date BETWEEN '2000-01-01' AND '2000-01-31'

for January, 2000

answered 2012-02-01T23:06:43.553
20

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.

answered 2012-02-01T23:42:42.953
1

to get the month and year values from the date column

select year(Date) as "year", month(Date) as "month" from Projects
answered 2012-02-01T23:10:03.897

Your Answer