仅根据月份和年份选择mySQL

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?

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

The logic will be:

SELECT * FROM objects WHERE Date LIKE '$_POST[period]-%';

The LIKE operator will select all rows that start with $_POST['period'] followed by dash and the day of the mont

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html - Some additional information

$q="SELECT * FROM projects WHERE YEAR(date) = 2012 AND MONTH(date) = 1;

You can do like this:

$q="SELECT * FROM projects WHERE Year(Date) = '$year' and Month(Date) = '$month'";

to get the month and year values from the date column

select year(Date) as "year", month(Date) as "month" from Projects

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.

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.

Here, FIND record by MONTH and DATE in mySQL

Here is your POST value $_POST['period']="2012-02";

Just, explode value by dash $Period = explode('-',$_POST['period']);

Get array from explode value :

Array ( [0] => 2012 [1] => 02 )

Put value in SQL Query:

SELECT * FROM projects WHERE YEAR(Date) = '".$Period[0]."' AND Month(Date) = '".$Period[0]."';

Get Result by MONTH and YEAR.

Yeah this is working, but it returns one row only while there is several rows to retrieve using selected columns only. Like SELECT Title,Date FROM mytable WHERE YEAR(Date)=2017 AND MONTH(Date)=09 returns only one row.

Here is a query that I use and it will return each record within a period as a sum.

Here is the code:

$result = mysqli_query($conn,"SELECT emp_nr, SUM(az) 
FROM az_konto 
WHERE date BETWEEN '2018-01-01 00:00:00' AND '2018-01-31 23:59:59' 
GROUP BY emp_nr ASC");

echo "<table border='1'>
<tr>
<th>Mitarbeiter NR</th>
<th>Stunden im Monat</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
$emp_nr=$row['emp_nr'];
$az=$row['SUM(az)'];
echo "<tr>";
echo "<td>" . $emp_nr . "</td>";
echo "<td>" . $az . "</td>";
echo "</tr>";
}
echo "</table>";
$conn->close();
?>

This lists each emp_nr and the sum of the monthly hours that they have accumulated.

Suppose you have a database field created_at Where you take value from timestamp. You want to search by Year & Month from created_at date.

YEAR(date(created_at))=2019 AND MONTH(date(created_at))=2