In my mySQL database, I have a column with certain rows. One of the rows contains a DATE, such as this: 2012-02-01
I want to use PHP to perform a SELECT based just on the year and month.
The following will be the logic of the SELECT:
$q="SELECT * FROM projects WHERE Date="SELECT HERE THE SPECIFIC YEAR AND MONTH"";
A $_POST variable will be used to pass the precise month and year, such as $_POST[‘period’]=”2012-02″;
I’m not sure how I’m going to accomplish it.
Asked by DiegoP.
SELECT * FROM projects WHERE YEAR(Date) = 2011 AND MONTH(Date) = 5
Answered by Rick Kuipers
If you have
$_POST['period'] = "2012-02";
To begin, locate the month’s first day:
$first_day = $_POST['period'] . "-01"
If you have a Date index, then this query will use it:
$q = " SELECT * FROM projects WHERE Date BETWEEN '$first_day' AND LAST_DAY( '$first_day' ) " ;
You might also use inclusive-exclusive intervals, which work well (you don’t have to care about whether the column is DATE, DATETIME, or TIMESTAMP, and you don’t have to worry about precision:
$q = " SELECT * FROM projects WHERE Date >= '$first_day' AND Date < '$first_day' + INTERVAL 1 MONTH " ;
These values should be appropriately escaped or prepared statements should be used. To summarize, utilize whichever PHP approach is currently suggested to avoid SQL injection concerns.
Answered by ypercubeᵀᴹ
So there you have it. Leave the computation to PHP and free up some space in your database. You may make good use of an index on the Date column this way.
<?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: I completely forgot about the Unix timestamp conversion.
Answered by aefxx
Assume you have a database column called created at that gets its value from a timestamp. From the created at date, you wish to search by Year and Month.
YEAR(date(created_at))=2019 AND MONTH(date(created_at))=2
Answered by Nazmul Haque
$q="SELECT * FROM projects WHERE YEAR(date) = 2012 AND MONTH(date) = 1;
Answered by Arjan
Post is based on https://stackoverflow.com/questions/9104704/select-mysql-based-only-on-month-and-year